IIf Statement in Query Criteria 12-11-09

I have the following code in a query:

SELECT qryPointsWithDropOffDates.EMPLOYEEID, qryPointsWithDropOffDates.SSN, 
qryPointsWithDropOffDates.NAME, qryDisciplineNoticesReprint.LASTINCIDENTDATE, 
qryPointsWithDropOffDates.INCIDENTDATE, qryPointsWithDropOffDates.TYPESHORT, 
qryPointsWithDropOffDates.POINTVALUE
FROM qryDisciplineNoticesReprint INNER JOIN qryPointsWithDropOffDates ON 
qryDisciplineNoticesReprint.SSN=qryPointsWithDropOffDates.SSN
WHERE (((qryPointsWithDropOffDates.INCIDENTDATE) Between 
[qryDisciplineNoticesReprint.CALCDATE] And 
[qryDisciplineNoticesReprint.LASTINCIDENTDATE]) AND 
((qryPointsWithDropOffDates.POINTVALUE]<>0))
ORDER BY qryPointsWithDropOffDates.EMPLOYEEID, 
qryPointsWithDropOffDates.INCIDENTDATE;

This works fine in most circumstances.  However, I did fine a loop hole I 
need to fix.  If the field 
[qryDisciplineNoticesReprint.DISCIPLINE]="ATTENDANCE INFORMATION FORM", I 
need to change the between statement to read "Between 
[qryDisciplineNoticesReprint.CALCDATE] And 
[qryDisciplineNoticesReprint.DISCIPLINEDATE]).

The code I am trying looks like this:

SELECT qryPointsWithDropOffDates.EMPLOYEEID, qryPointsWithDropOffDates.SSN, 
qryPointsWithDropOffDates.NAME, qryDisciplineNoticesReprint.LASTINCIDENTDATE, 
qryPointsWithDropOffDates.INCIDENTDATE, qryPointsWithDropOffDates.TYPESHORT, 
qryPointsWithDropOffDates.POINTVALUE
FROM qryDisciplineNoticesReprint INNER JOIN qryPointsWithDropOffDates ON 
qryDisciplineNoticesReprint.SSN=qryPointsWithDropOffDates.SSN
WHERE 
(((qryPointsWithDropOffDates.INCIDENTDATE)=IIf([qryDisciplineNoticesReprint.DISCIPLINE]="ATTENDANCE 
INFORMATION FORM", (qryPointsWithDropOffDates.INCIDENTDATE] Between 
[qryDisciplineNoticesReprint.CALCDATE] And 
[qryDisciplineNoticesReprint.DISCIPLINEDATE]),(qryPointsWithDropOffDates.INCIDENTDATE) 
Between [qryDisciplineNoticesReprint.CALCDATE] And 
[qryDisciplineNoticesReprint.LASTINCIDENTDATE])) AND 
((qryPointsWithDropOffDates.POINTVALUE]<>0))
ORDER BY qryPointsWithDropOffDates.EMPLOYEEID, 
qryPointsWithDropOffDates.INCIDENTDATE;

With the added IIf statement in the criteria, the query returns no records.  
It should return 9 records.

Can someone tell me what I am doing wrong int he code above?  Thank you in 
advance.
0
Utf
12/11/2009 1:08:01 AM
access.queries 6343 articles. 1 followers. Follow

1 Replies
898 Views

Similar Articles

[PageSpeed] 41

You can't put the Betwen inside the IIf(). Try a WHERE CLAUSE of
WHERE INCIDENTDATE Between CALCDATE And 
IIF(DISCIPLINE]="ATTENDANCE INFORMATION FORM", 
DISCIPLINEDATE,LASTINCIDENTDATE) 
AND POINTVALUE<>0

-- 
Duane Hookom
Microsoft Access MVP


"AccessIM" wrote:

> I have the following code in a query:
> 
> SELECT qryPointsWithDropOffDates.EMPLOYEEID, qryPointsWithDropOffDates.SSN, 
> qryPointsWithDropOffDates.NAME, qryDisciplineNoticesReprint.LASTINCIDENTDATE, 
> qryPointsWithDropOffDates.INCIDENTDATE, qryPointsWithDropOffDates.TYPESHORT, 
> qryPointsWithDropOffDates.POINTVALUE
> FROM qryDisciplineNoticesReprint INNER JOIN qryPointsWithDropOffDates ON 
> qryDisciplineNoticesReprint.SSN=qryPointsWithDropOffDates.SSN
> WHERE (((qryPointsWithDropOffDates.INCIDENTDATE) Between 
> [qryDisciplineNoticesReprint.CALCDATE] And 
> [qryDisciplineNoticesReprint.LASTINCIDENTDATE]) AND 
> ((qryPointsWithDropOffDates.POINTVALUE]<>0))
> ORDER BY qryPointsWithDropOffDates.EMPLOYEEID, 
> qryPointsWithDropOffDates.INCIDENTDATE;
> 
> This works fine in most circumstances.  However, I did fine a loop hole I 
> need to fix.  If the field 
> [qryDisciplineNoticesReprint.DISCIPLINE]="ATTENDANCE INFORMATION FORM", I 
> need to change the between statement to read "Between 
> [qryDisciplineNoticesReprint.CALCDATE] And 
> [qryDisciplineNoticesReprint.DISCIPLINEDATE]).
> 
> The code I am trying looks like this:
> 
> SELECT qryPointsWithDropOffDates.EMPLOYEEID, qryPointsWithDropOffDates.SSN, 
> qryPointsWithDropOffDates.NAME, qryDisciplineNoticesReprint.LASTINCIDENTDATE, 
> qryPointsWithDropOffDates.INCIDENTDATE, qryPointsWithDropOffDates.TYPESHORT, 
> qryPointsWithDropOffDates.POINTVALUE
> FROM qryDisciplineNoticesReprint INNER JOIN qryPointsWithDropOffDates ON 
> qryDisciplineNoticesReprint.SSN=qryPointsWithDropOffDates.SSN
> WHERE 
> (((qryPointsWithDropOffDates.INCIDENTDATE)=IIf([qryDisciplineNoticesReprint.DISCIPLINE]="ATTENDANCE 
> INFORMATION FORM", (qryPointsWithDropOffDates.INCIDENTDATE] Between 
> [qryDisciplineNoticesReprint.CALCDATE] And 
> [qryDisciplineNoticesReprint.DISCIPLINEDATE]),(qryPointsWithDropOffDates.INCIDENTDATE) 
> Between [qryDisciplineNoticesReprint.CALCDATE] And 
> [qryDisciplineNoticesReprint.LASTINCIDENTDATE])) AND 
> ((qryPointsWithDropOffDates.POINTVALUE]<>0))
> ORDER BY qryPointsWithDropOffDates.EMPLOYEEID, 
> qryPointsWithDropOffDates.INCIDENTDATE;
> 
> With the added IIf statement in the criteria, the query returns no records.  
> It should return 9 records.
> 
> Can someone tell me what I am doing wrong int he code above?  Thank you in 
> advance.
0
Utf
12/11/2009 5:48:02 AM
Reply:

Similar Artilces:

Conditional Formatting not holding in pivot table querying Access
Hi, I have created a pivot table in Excel 2007 based on an Access query. I then created some conditional formatting. My values in the pivot table are percentages. I have red, green and yellow based on what the percentage is. Everything is fine until I refresh the pivot table. The pivot table size does not change (No new rows added). It wipes out all of the color coding though. When I select a cell in my pivot table and select Conditional Formatting, the rules are still there but there is not color. I originally did this file in Excel 2003 and did not have this problem. Can some...

parameter query
Hi, I need help to finish my Query, I want to let the user put a serial number to get it or put several serial numbers or collect it all so I did this ... (',' & [Enter SN's] & ',') Like ('*[, ]' & [Requisition Lines]![SN] & '[, ]*') this is work to give a specific serial, but if I want let the users show all serial numbers by clicking enter without write any number what is missing in this ??? I appreciate ur cooperation Try entering the following as the criteria: ((',' & [Enter SN's] & ',') Like...

if, and statement
I need to do an if and statement something like this- if A1=1 and A2x(A1)1>10 then no more than 10, but if A2=2 and A2xA1>25 then no more than 25 so if A2= 12 and A1=1 then the result will be 10 but if A2= 12 and A1=2 then the result will be 24 but if A2= 15 and A1=2 then the reult will be 25 I know it is a lot, but you can guys figure it out. Try this: =MIN((A1=1)*A2,10)+MIN(2*(A1=2)*A2,25) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------...

help with SQL query for HQ
Hello! Need two queries. 1. to clear the bin location field for all items 2. to assign a numberical value for the bin location field for each of our stores. ex. store 1 bin location field to read 1, store store bin location field to read 2 any help would as always be greatly appreciated... thank you hi zcsf, 1. To Clear bin location Run this UPDATE ITEM SET BINLOCATION='' 2. To Update the bin location with store id UPDATE ITEM SET BINLOCATION=storeid from Configuration Rate please. "ZCSF" wrote: > Hello! > Need two queries. > 1. to clear the bin location field ...

is it possible to set a persistant variable from a query?
I am trying to set a specific value to a variable to be retrieved on another line in the query where a specific value changes then the value from the variable will be returned, but so far am having no luck. here is the code for the module that contains the variable and the sql that I am using any help would be greatly appreciated. Thanks, Nate Option Compare Database Global GBV As String Public Function init_globals() GBV = Null End Function Public Function globalvar(ivalue) As String Function init_globals() GBV = ivalue End Function SELECT prm5051.level, prm5051.part, prm5051.desc, prm5051...

Custom Number Format #11
I want a format that will show "35.4" as "35.4 lb/ft" and "35" as "35 lb/ft". The trick being the decimal is not shown for the second number. I currently use two different versions depending on the number, but I would like this to be automaticly done in the format. Assuming 35.4 is in A1, in another cell, enter =A1&" lb/ft". Drag that down the column to capture the 35 and change it to 35 lb/ft. Carole O "Sloth" wrote: > I want a format that will show "35.4" as "35.4 lb/ft" and "35" as &qu...

Macro and If Statement
Here's the situation: I have a worksheet that will have an "X" in either cell F3 or G3 or H3. What I would like to do (via a macro), is to examine F3, G3 and H3, and if there is an "X" in anyone of those cells, to run another specific macro. For example: Run a macro (for example called "totalsummary")to determine... If there is an "X" in F3, then run a macro called "summaryEL"; If there is an "X" in G3, then run a macro called "summaryHT"; If there is an "X" in H3, then run a macro called "summaryFA&quo...

Downloading statement shows items to review but I can't find them (2007)!
The last few days, when I download my checking account statementm it succeeds and shows items to review (4 yesterday, 5 today) but the downloaded items are not showing up in the account. The last item in the register is from 11/21 (5 days ago). I have the current vesion (MSM Deluxe 2007, Version 16...1024). I've been using MSM for many years. Anyone have any ideas on this? Thanks, Gary In microsoft.public.money, GarDavis wrote: >The last few days, when I download my checking account statementm it >succeeds and shows items to review (4 yesterday, 5 today) but the >downloaded ...

Cross tab query solution
I have table Named OutStationTab having the data as below field EName - Text Field OsDate - Date Field OsAmt - Numeric field I want to make a cross tab query in which RowHeading- EName Column Heading- Expr1: Format([OsDate],"dd-mm-yyyy") Value- OsAmt Note the data is entered from 15th Date of a month to 14th date of next month. One Employee take 1 outstation charges only in a day. I am able to make the crosstab query and its working perfect. Like I filter the query (From 15-02-2010 to 14-03-2010), then made the CrossTab query. It works perfect. It does n...

IIF Statement syntax?
I have a timesheet template that allows a user to enter their Time In, Time out for Lunch, Time back In, Time Out for the day, then calculates the time differences to give total number of hours worked. I am trying to make it so that if the total time for the day is Negative, it assigns zero as the default. Here is the statement that I tried IIF(ROUND((((E7-E6)+(E4-E3))*24),2)<0, ROUND((((E7-E6)+(E4-E3))*24),2),0 Can anyone give me some help on what I've done wrong? I just get #NAME in the box Thanks for the help in advance Mac Could it be that IFF should be IF? Bernard "m...

IIF(AND) statement
Hi I have a number of things I need to test in the query. I need to use IIF statement with AND (for example in Excel you can do it),. Example I need this IIf (and(cat="HS",cat2="JTK",cat3="NT"),"1","2") how to do it in access? -- Greatly appreciated Eva On 7 apr, 21:09, Eva <E...@discussions.microsoft.com> wrote: > Hi > I have a number of things I need to test in the query. I need to use IIF > statement with AND (for example in Excel you can do it),. > Example > I need this > IIf (and(cat="H...

Passing a value from subform or main form to subform query
I am using MS ACCESS 2003 I have a main form and a subform that pulls up an existing case for the user to update the information already entered. The subform is designed and opens up in form view not in datasheet view. The subform knows which case to pull in based on the case number on the main form The textboxes on the main form are bound fields from a query. The user enters an ID which is how the main form query knows which record to pull and it works. The textboxes on the subform are bound with data from a different query (so I have a query for each form) The que...

XML query
Hi I am new to using XML, and have a bit of an issue. How do I define how the XML file looks when I export a query from Access? On Sep 12, 4:50 pm, rmorri...@davislangdon.com.au wrote: > Hi I am new to using XML, and have a bit of an issue. How do I define > how the XML file looks when I export a query from Access? XML is a markup language: http://www.w3.org/TR/2006/REC-xml11-20060816/#sec-origin-goals That means, it is a plain text file, it uses tags. When you export a query from Access 2003 to XML, several files with different purpose can be generated. 1. A file with .xml exten...

Hidden files in Ms-Query cause ODBC connect errors or Query is wac
I have a Query fetch_from_bob that moves data (collapses records,unique key search, etc...) from excel spreadsheet "bob" to excel spreadsheet "fred". Fred and bob live in the same directory" c:\123directory" on the same computer. I allow this query to execute/refresh data automatically when one of the query paramaters is changed. This works great. I also have a macro that invokes the query to refresh the data whenever the user desires. This works great as well. The macro literally has all the VB code to execute the query. But I'm just a part time progra...

IF statement question
Is it possible to test for more than 2 different conditions? ...example total number of hours paid hours 5 hrs 5 hrs 10 hrs 9.5 hrs 12 hrs 10 mins 12 hrs IF "total number of hours" <6 this will be "paid hours" ..... IF "total number of hours" >6 but <12 "paid hours" = "total number of hours" -.5 IF "total number of hours" >12 "paid hours" = "total number of hours" -.1 -----= Poste...

Update Query 07-11-07
I have a link to a text file that is updated daily. I run an append query to add any new records on the text file into a table stored in my database. I am attempting to update previously appended records based on a field that may change on a daily basis. The record remains on the report, but one field goes through 4 changes through the records duration on the report. (linked text file) Example Day 1 Field 21 = "Prelim" Day 2 Field 21 = "Prelim" Day 3 Field 21 = "Submitted" Day 4 Field 21 = "Finalized" I attempted to set up an update query that es...

Help Please
How can I update on an address list "Po address" name to read P.O. with out changing the rest of the address. I just want to change the "Po to read "P.O."? Any help will be greatly appreciated. Thank you. UPDATE YourTable SET Address = "P.O. " & MID([Address],4) WHERE Address Like "PO *" Assumption: Addresses to be changed all start with PO and a space If PO can be buried within the address (Route to PO Box 2132 Westchester) then it becomes a bit more complex UPDATE YourTable SET Address =TRIM( Replace(" " & [Address],&q...

iIF clause
Can you help me build the right IIf field in the query with the following conditions : The field DDU consissts of : DDU :[exworks]*2+0,4+0,01 To the above expression i must also add 0,001 if size = 205, etc according to the following table : 205 0.001 60 0.001 20 0.009 1 1.32 4 0,32 0,5 1,67 However Acces does not accept my query, obvioulsy i have errors : DDU : [exworks]*2+0,4+0,01 + IIf([size = 205],0,001,[size = 60],001) Will you help me ? ...

Excel printing query
How do you print an excel worksheet with row numbers? <File> <PageSetUp> <Sheet> tab, And *check* "Row & Column Headings". -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Linda" <Linda@discussions.microsoft.com> wrote in message news:808D8884-3E22-4018-90EC-932C90EC8570@microsoft.com... > How do you print an excel worksheet with row numbers? File | ...

What Does +IIF Do as Opposed to IIF?
What Does +IIF Do as Opposed to IIF?James Igoehttp://code.comparative-advantage.com/ AFAIK, there's no such function as +IIf in VBA. If I had to guess, I'd say that the + is simply arithmetic: add the results of the IIf function to whatever preceeded it.How are you seeing it used?-- Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele(no e-mails, please!)<james.igoe@gmail.com> wrote in message news:1173285991.823665.303480@p10g2000cwp.googlegroups.com...>> What Does +IIF Do as Opposed to IIF?>> James Igoe> http://code.comparative-advantage.com/> On 7 Mar 200...

IIF question
Hello. Doing a report that uses an IIF in a textbox that is text. If the IIF is false I would like the text (mainly 1 or 2 words) not to be underlined. How do I end the IIF so the text is not underlined? Thanks. John I am not sure how you mean, but if you want this underlined based on the true or false then use the properties box and choose the text decoration item and write an expression in there. "JohnE" <JohnE@discussions.microsoft.com> wrote in message news:5C6EEFBC-0DC0-41C0-A35D-3A01209E568A@microsoft.com... > Hello. Doing a report that uses an IIF...

Changing Background Color in "IF" statement
Greetings, Is there a way to color cell background from within an "IF" statement ? Thanks, -Paul Hi not with a formula. But use 'Format - Conditional Format' for this -- Regards Frank Kabel Frankfurt, Germany Paul Pruchnik wrote: > Greetings, > > Is there a way to color cell background from within an "IF" statement > ? > > Thanks, > > -Paul Paul Not without using Conditional Formatting or VBA. Formulas alone cannot change cell formats. You can place an "IF" statement in CF "formula is" Or perhaps just a &quo...

lookup with 2 criteria #2
=INDEX(Gaps!$C$9:$C$2103,MATCH(1,(Gaps!$A$9:$A$2103=A2)*(Gaps!$D$9:$D$2103=F2),0)) works great for getting me the correct acct #. Thank you again Frank! But now I need to take it a step further... The data that I'm working with will eventually be imported into another database program where we already have some of this data. We do not want duplicate data so I need to delete out what has already been input to the database. On my main sheet, cell P2 contains the month # for that invoice. On Gaps page my columns look like this: A B C D E F G H I Site Fuel Acct Meter Feb'04 ...

Money 2004 does not see downloaded bank statement...
I just upgraded (a couple of weeks ago) from 2003 to 2004. I am running Windows XP Pro. In 2003, I used to use Internet Explorer, go to my bank, select my date range, and download my transactions for "Money 99 or higher". If I had Money 2003 open, it would automatically see the downloaded file and import the transactions. I just did the EXACT same thing - about 10 times - in Money 2004 and it does NOT see the downloaded file or import the transactions. The download happens so fast that I don't know where the browser saved off the file. A quick search of the hard drive d...

change a date query fra a single to a several
I’m coming from Denmark, so excuse me for the bad English. I’ve got a table in an Access , I Have made by a Query, from a Employee “Clock in / out system”. They are also using the “Clock in / out system” to planed free (holliday), in this example At first , they put in the Employee number , next the start date ( 26-11-2007 ) and next the end date ( 30-11-2007 ) and at last the letter for Holliday “ H “ . and now the problems begin. Because all these information’s is taken into one records ( line ) Employee id Start date End date Letter for free H, ……. 1234 ...