Query With 2 <> Conditions On Different Fields Not Working

Jan 23, 2008

I'm trying to get this query to return all records except those with category: 4 AND issue: 12

Instead I'm always getting ZERO category: 4 and ZERO issue: 12

Should not the parenthesis force this to be taken as a single "WHERE"?


SELECT ASSIGNED_TO, COMPLAINT_NUM, DATE_RESOLVED, CATEGORY, ISSUE
FROM tmptbl_tm_YTD
WHERE (CATEGORY<>4 AND ISSUE<>12);

View Replies


ADVERTISEMENT

Unbound Fields In Query Not Working

Sep 30, 2005

Hi

I have a from with unbound fields on and i have it set up so when you type in the field click the button - it runs a query and finds the records.

I have tried to add a combo box that gets the actual value from the field (avoids people typing wrongly) which it finds the values fine, but the query does not seem to be able to returns to results for that value selected via the combo box.

Any advice would be good?

Thanks

Scott

View 2 Replies View Related

Tables :: Multiple Conditions In Calculated Fields

Jan 9, 2013

is there any way to put into the calculated field (in expression builder) conditions? What I need is something like

Sum If (Table1.Field1="Y" And CurrentTable.Field2=Table1.Field3)

I means sum how many times there is "S" value in the field1 Table1, but only for records where the field3 in Table1 is equal to the value in the actual table in Field2 (in the actual row).

View 13 Replies View Related

Queries :: Multi-field Query Not Working When Some Fields Are Null Value

May 22, 2013

Access 2010. I've had to learn it at work because our DBA was let go and I was the only one willing to give it a go. Read a book or two and picked up some stuff on the internet.

Here's my problem:
I have a simple table -Employee with 4 fields. FirstName, LastName, Office and JobTitle. I have form called Form1 that has 3 control fields cboJobTitle (a combo box that is populated by a query that finds all the unique values of that field in the Employee table), cboOffice (same as above) and txtName (a text box to allow user input) that are used as the criteria for a multi-field query triggered by a button at the bottom of the form. The idea being that you could do a search using this form to find all the employees in one office or all the accountants in one office, or any other combination.

The main search query has the following criteria for each field -
Like [forms]![Form1]![cboOffice] & "*"
Like [forms]![Form1]![cboJobTitle] & "*"
Like [forms]![Form1]![txtName] & "*"

It works great...until I enter a record where one of those fields may be null, such as if I leave the JobTitle blank.
If I have two employees in an office in one city and then do a search for all the employees in that office, it only returns one record and ignores the one that has the null value in the JobTitle field.

View 14 Replies View Related

Select Query With Conditions

Aug 9, 2005

Hi friends,

I had two tables parent(f1) , child(f1,f2,f3).
I had to display f3 value for parent(f1) when f1 has only one f2,
if f2 values are more than one for single f1 and if all f3 values are 0 then
i should display f3 as 0, else if some values for f3 are nonZeros and some are Zeros then
is should display f3 value as '-'.

How to do this?
please any suggestions?

Thanks.

View 1 Replies View Related

Query With Conditions By Date

Aug 9, 2007

I am so frustrated right now that I could scream. I have a database which tracks attendance and referrals for a networking organization - it meets weekly on Thursdays. It has a report which displays totals of both attendance and referrals for each weekly meeting for an entire month. The problem I am having is related to the fact that some months have 4 Thursdays and some have 5. If I get my queries to display the meeting dates correctly for the 4-Thursday months, then it displays incorrectly for the 5-Thursday months, and vice versa.

Is there any way to put a criteria on a missing record? If I could have it just populate the 5th week with spaces or null when it doesn't exist for that month, it would make my life a lot easier.

I can send a copy of the database to anyone who thinks they can help. It's too big to post.

View 8 Replies View Related

How To Check More Than 3 Conditions In A Query?

Mar 23, 2005

I have a filed and I want to find out its length then in the next column i have to add the data like if filed 1's length is 1 then it should be 000+field1.value, if it is 2 then 00+field1.value, if it is 3 then 0+field1.value and so on...any help is appreciated , thanks

View 2 Replies View Related

Query Based On Multiple Conditions

Jan 11, 2005

For some reason, this one is driving me nuts so i would really appreciate some help.
This is kind of complicated to explain but here goes.
I am trying to create a query so that, based on certain conditions certain rows from the Form/Table are either visible or not visible on the report.
I have four fields involved:
Antic Dep$ (Anticipated Deposit Amount)
Antic Loan$ (Anticipated Loan Amount)
Date Dep Booked
Date Loan Booked

Basically i am trying to hide any records that in which the Date Dep Booked and/or Date Loan Booked are older than the current month...

I can think it through logically but cannot get my head around the query.

If Date Dep Booked or Date Loan Booked is not older than current month then show the row;
If Date Dep Booked is older than current month and Date Loan Booked is older than current month then hide the row; unless

If Date Dep Booked is older than current month but Date Loan Booked is not older than current month then show the row; except
If Date Dep Booked is older than current month and Antic Loan$ is zero then hide the row; but
If Date Dep Booked is older than current month and Antic Loan$ is not zero then show the row;

Then i also need to do the reverse for the Date Loan Booked, i.e.

If Date Loan Booked is older than current month but Date Dep Booked is not older than current month then show the row; except
If Date Loan Booked is older than current month and Antic Dep$ is zero then hide the row; but
If Date Loan Booked is older than current month and Antic Dep$ is not zero then show the row;

Phew! That probably makes no sense but i had to try.

Once again, i appreciate any help so that i can keep the remainder of my hair.

Switters

View 1 Replies View Related

Doing A Date Query With Conditions In Excel?

Aug 8, 2015

I am trying to do a date query with conditions in excel but it don't seem to work.

I am trying to do a query on a table on the birthdate column and I need the records of all athletes that were born on or after 1/1/2008. Below is what I did but it doesn't seem to work.

Created a design view
chose the birth date field
and the criteria row I typed
>=#1/1/2008#

View 2 Replies View Related

Please Help-Conditions On Query Results (Booking Problem)

Nov 23, 2004

Ok i'm new so don't be too hard.

I'm making the most simple DB where there are bookings consisting of a StartDate and EndDate. The Scenario is a car hire firm. Obviously i don't want to be able to double book a car and want to be able to list all cars available within the two dates (input).

I have set the defult input to 12/07/04(StartDate) and 12/17/04(EndDate) for the purpose of testing.

I have created a query to find all cars which are not available in relation to the input dates, but cannot get it to list the available cars!

this is the query i have:

-----------------------------------------------------------

SELECT CarDetails.CarReg
FROM CarDetails INNER JOIN BookingDetails ON CarDetails.CarReg = BookingDetails.CarReg
WHERE (((((([BookingDetails].[StartDate])<#12/7/2004#) And (([BookingDetails].[ReturnDate])<#12/7/2004#)) Or ((([BookingDetails].[StartDate])>#12/17/2004#) And (([BookingDetails].[ReturnDate])>#12/17/2004#)))=False));


-----------------------------------------------------------

All i want to do is take all of the cars and deduct the one's which are produced by this query.

Thanks for any help!

View 3 Replies View Related

Queries :: Apply Conditions To Totals Row In Query

May 15, 2013

I have a parameter query with a totals row that displays averages. Is there a way to have the average row use only specific records in its calculation based on one of the field's values WHILE still displaying all the records returned by the query.I want only data that has a "YES" value used in the average while still displaying the records marked as "NO"

View 1 Replies View Related

Queries :: Create Calculation Query That Uses Different Equations Under Certain Conditions

May 12, 2013

I want to create a calculation query that uses different equations under certain conditions. Here's specifically what I need:

If the "Cost_Category" field is "Full Price" then the query uses the following calculation:
Total_Cost: Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost]))

If the "Cost_Category" field is "BOCES" then the query uses the following calculation:
Total_Cost: Sum(nz([BOCES_Number_of_Participants])*nz([Cost_Per_Person]))

I have successfully created these two queries individually, but combining them doesn't seem to work. Here's what I wrote:

Total_Cost: IIf([Cost_Category]=Full Price,Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost])),
IIf(Cost_Category]=BOCES,Sum(nz([BOCES_Number_of_Participants])*nz([Cost_Per_Person]))

It keeps coming up with errors, saying that I misplaced a comma, parenthesis or quotation. I've tried playing with it, changing the syntax slightly but it doesn't seem to work.

View 2 Replies View Related

Modules & VBA :: Automatically Repeat Last Record Under Certain Conditions In A Table / Query

Jul 15, 2014

Iā€™d like to repeat the last not = 0 record under certain conditions, in a table or in a query.What I have:

Calendar_All Dates
Calendar dateMyValue
7/6/20140.00
7/7/2014108.94
7/8/2014107.71

[code]...

Basically, if Calendar date > Date(), if Calendar Date not Saturday or Sunday, weekday(Calendar date<>1 and <>7), AND Calendar Date not in (Holidays table).Then repeat the last not = 0 value of MyValue. I thought of changing the default value but the value is already 0, while default is null + I need to set the default value under certain conditions.

View 14 Replies View Related

Working With Time Fields

Mar 15, 2006

Can you calculated time in a query?
Such as hours during any given day?

View 2 Replies View Related

General :: IIF Statement - Calculated Fields Not Working

Apr 8, 2015

Access 2013

I've created a table for a college project and realised that one of my calculated fields isn't working. It doesn't produce an error message, it just doesn't calculate.

IIf([CycleSpeed]<10,[CycleTime]*236,IIf(10<=[CycleSpeed]<12,[CycleTime]*354,IIf(12<=[CycleSpeed]<14,[CycleTime]*472,IIf(14<=[CycleSpeed]<16,[CycleTime]*590,IIf(16<=[CycleSpeed]<=20,[CycleTime]*708,IIf([CycleSpeed]>20,[CycleTime]*944,0))))))

View 6 Replies View Related

Forms :: Disable / Enable Fields Not Working For A New Record

Aug 9, 2014

I have some code like this that sets some fields up to be disabled when the form loads, then enables them when a combo box ('Type') is selected to 'Instrument'. This works fine as far as it goes, but if the user has selected 'Instrument' and then goes to a new record, the fields remain enabled.

Private Sub Form_Load()
Me.CalibrationTolerance.Enabled = False
Me.AcceptanceLimit.Enabled = False
End Sub

[Code] ....

I've looked this up and it appears I need to use the property Form.NewRecord, but nothing I do seems to make it work.

View 8 Replies View Related

General :: Add Existing Fields And Property Sheet - Buttons Not Working

Sep 1, 2014

I have a problem with two buttons, add existing fields and property sheet

When i click them they do nothing at all, so i cannot do what i need to do?

View 9 Replies View Related

Forms :: Quick Filter Not Working When Memo-type Fields In Form

Jul 1, 2013

I recently made a new version of our database complete with forms, querys etc.. using Access 2010. It looked good and nice, but we encountered an unexpected problem. In all forms that contains memo-type fields quick filter is not working (there is no lists, checkboxes etc..). My users really like quick filters and are understandably irritated. I would understand if this disappearance would affect only memo-type fields, but it affects ALL the fields.

I also know that there are problems with memos in Access 2010. I have tried to make new forms, but every time I add memo fields quick filter stops functioning. I have also tried to change properties, options and what not, but to no use. In older versions of our forms this feature works just fine, but they were made in older version of Access (don't know which since oldest ones are from year 2003).
Television

View 3 Replies View Related

If 2 Conditions Are Met Then

Feb 26, 2007

I am trying to write a code that will enable an event to happen if 2 conditions are true, something like:

If [DueDate] >= [CurrentDate] And [EndDate] is Null Then
Event ..... take place

I tried above and did not work

it works only if write one condition of above

how can I set it up correctly

View 5 Replies View Related

Help With Conditions

May 26, 2005

I currently have a query which takes its criteria from a form called search. This all works fine, unless the user leaves a field in the search form blank. Then the query has no results.
What I'd like is for a blank field to act as a wildcard.
I guess the reason this doesn't automatically work is that Access (effectively) puts " & " around the criteria taken from a form, so the criteria ends up as " " not (there's a space there!).

I tried entering a criteria in the query that says:
IIF([Forms]![Search]![Date] Is Null,*, [Forms]![Search]![Date]
this doesn't work. For one thing Access edits the * into "*", so it is no longer a wildcard. Secondly, I've never used IIF before, so it's probably wrong anyway...

So, can anyone help me do this?
Even if a blank field cannot be used as a wildcard, any character would do...

View 2 Replies View Related

IIF With 2 Conditions?

Aug 15, 2007

Hi

Hope someone can help with this. Had a search through the forums but nothing quite the same (although I'm guessing it's a simple one to solve.)

I need to calculate a figure based on 2 criteria. The permutations are as follows

If condition A is less than 20 and condition B is No then return 9
If condition A is less than 20 and condition B is No then return 12
If condition A is greater than 20 and condition B is No then return 13
If condition A is greater than 20 and condition B is No then return 15

Any suggestions?

View 3 Replies View Related

Conditions

Jun 7, 2005

Hi, I have created a Yes/No attribute. Im just wondering if a text boxes visibility can be dependent on the result of this. E.g. If a row is set to yes then the text box will be shown and if it is set to no the text box is made invisible. Thanks for any help

View 7 Replies View Related

Participation Conditions - How To ?

Dec 21, 2007

Hi again :)
Since I am new to Access I am having a problem figuring out how to set the participation conditions (optional or mandatory) for my relationships. I have looked through MS access online and forums, google etc. and found nothing.
I would be very grateful for any help :)
Kind Regards
Marie

View 1 Replies View Related

Sql Update With 2 Where Conditions

Jan 12, 2005

The following SQL statement with 1 where condition works fine, but the Next SQL statement with 2 where conditions does not work, a error message says Run-Time error ‘13’ type mismatch , what is incorrect in the statement when the AND MinID = 15 is added. The table data type in set as number



SQL = "UPDATE MinistryInv2 Set MinID = " & 14 & " WHERE PID = " & Me!ID & ";"



SQL = "UPDATE MinistryInv2 Set MinID = " & 14 & " WHERE PID = " & Me!ID And MinID = 15 & ";"



Phil

View 3 Replies View Related

Setting Conditions

Jun 3, 2005

I have set up a querry that gathers information from different tables:

ID | Name | StartDate | EndDate | NbOfDays |

Then, i am using a form to fill up this querry. Normal stuff.

1- How and where can i set a condition so the input of EndDate could not be smaller than StartDate?
2- How and where can i set up and write the formula that calculates the NbOfDays? (meaning number of days worked form start date to end date)

Thank you for helping!

View 5 Replies View Related

Query Not Working...

Jan 30, 2007

Hi,

I hope someone is able to help me. I've been working on this for days. I have a report which has a 2 subreports in it. I have the subreport bound and have a query working in the recordsource. Here is the recordsource:

SELECT [Casual Roster Information].ID, [Casual Jobs/Dates].LinkID, Sponsors.SponsorID

I have one report with the Master/Child fields filled in and set but the sponsors subform I cannot fill in the Child field. It says I need to put SponsorID in the recordsource. I thought I did have this(see above.) That's my first problem.

My bigger problem which I think the previous problem is contributing too is my subreport shows up blank. When I run my query it works perfect. But, when I run it with the rest of the program the subreport shows up blank.

Anyone know how I can go about fixing this??
Thanks!!
Nick

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved