IIF statement in query criteria, help!

I have a query in which one date need to be filtered based on another field.  
If the field [last] =1 , the data needs to be filtered showing only data 
where [ShiftDate]>=[Start1].  If the field [last]=2, it is 
[shiftdate]>=[Start2].  So in the criteria for field [ShiftDate], I entered 
"iif([last]=1,[shiftdate]>=[start1],[shiftdate]>=[start2])".  I don't get an 
error, but I also don't get any results.  If I enter  ">=[start1]" or 
[shiftdate]>=[start1] instead of the iif statement, I get the results 
expected.  I'm pretty sure that I've used iif statements in criteria before, 
but clearly there's something wrong with this one.  Can anyone help?
0
Utf
1/27/2010 12:02:01 AM
access.queries 6343 articles. 1 followers. Follow

3 Replies
1396 Views

Similar Articles

[PageSpeed] 46

Please post the expression you are using...

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Angela" <Angela@discussions.microsoft.com> wrote in message 
news:0F513632-9CCF-407D-A08A-E690E70708AC@microsoft.com...
>I have a query in which one date need to be filtered based on another 
>field.
> If the field [last] =1 , the data needs to be filtered showing only data
> where [ShiftDate]>=[Start1].  If the field [last]=2, it is
> [shiftdate]>=[Start2].  So in the criteria for field [ShiftDate], I 
> entered
> "iif([last]=1,[shiftdate]>=[start1],[shiftdate]>=[start2])".  I don't get 
> an
> error, but I also don't get any results.  If I enter  ">=[start1]" or
> [shiftdate]>=[start1] instead of the iif statement, I get the results
> expected.  I'm pretty sure that I've used iif statements in criteria 
> before,
> but clearly there's something wrong with this one.  Can anyone help? 


0
Jeff
1/27/2010 12:13:06 AM
Hi Angela,

     Do this in the query designer (showing only necessary fields):

Field Line:           last      ShiftDate
Criteria Line 1:     1          >=[Start1]
Criteria Line 2:     2          >=[Start2]

In SQL view it would look something like this:

SELECT ...
FROM ...
WHERE ([last] = 1 AND [ShiftDate] >= [Start1]) OR ([last] = 2 AND [ShiftDate]
>= [Start2]);

        Clifford Bass

Angela wrote:
>I have a query in which one date need to be filtered based on another field.  
>If the field [last] =1 , the data needs to be filtered showing only data 
>where [ShiftDate]>=[Start1].  If the field [last]=2, it is 
>[shiftdate]>=[Start2].  So in the criteria for field [ShiftDate], I entered 
>"iif([last]=1,[shiftdate]>=[start1],[shiftdate]>=[start2])".  I don't get an 
>error, but I also don't get any results.  If I enter  ">=[start1]" or 
>[shiftdate]>=[start1] instead of the iif statement, I get the results 
>expected.  I'm pretty sure that I've used iif statements in criteria before, 
>but clearly there's something wrong with this one.  Can anyone help?

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1

0
Clifford
1/27/2010 12:20:36 AM
If [Last] can only be equal to 1 or 2 then this criteria goes below 
[shiftdate] in the design grid --
  >= IIF([Last]=1, [start1], [start2]) 

Or SQL --
WHERE [shiftdate]>= IIF([Last]=1, [start1], [start2]) 


-- 
Build a little, test a little.


"Angela" wrote:

> I have a query in which one date need to be filtered based on another field.  
> If the field [last] =1 , the data needs to be filtered showing only data 
> where [ShiftDate]>=[Start1].  If the field [last]=2, it is 
> [shiftdate]>=[Start2].  So in the criteria for field [ShiftDate], I entered 
> "iif([last]=1,[shiftdate]>=[start1],[shiftdate]>=[start2])".  I don't get an 
> error, but I also don't get any results.  If I enter  ">=[start1]" or 
> [shiftdate]>=[start1] instead of the iif statement, I get the results 
> expected.  I'm pretty sure that I've used iif statements in criteria before, 
> but clearly there's something wrong with this one.  Can anyone help?
0
Utf
1/27/2010 12:59:01 AM
Reply:

Similar Artilces:

Favorites tab in Office Help
When using 'offline' Help in all MS Office app's, I would like to have a Favorites 'tab' in the results window. As I am not using all the app's constantly, I cant always remember how I achieved a particular result & what search term(s) I used to find the answer when searching the Help built into all the app's. I'm using Office 2007/2010 Beta on desktop & laptop PC's. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I ...

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...

help with customized forms
Hello, we are just starting to use CRM and one of the first modifications I have made is to the drop down choices for Category under Accounts / Details. I have made the change through Settings / System Customization / Accounts then choosing Customize Form , select the Details Tab, selected Category and choose to Change Properties. Change dto the Values tab and entered the new drop down choices and removed the default ones. If I go to the preview of the form all looks great too. but that is as far as the chages go??? When creating a new Account or modifying an existing Account the old...

HELP
I need help...I would like to add to the Mail Delivery Failure Automatic message..Does anyone know how Can not be done. Thomas White wrote: > I need help...I would like to add to the Mail Delivery Failure Automatic > message..Does anyone know how > > ...

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...

Print preview in dialog window, Help!
Hello All, I've got problem with print preview in MFC Dialog based application. I heard, that i can use CView class to print preview, not only in doc/view programs. I'm drawing on Printer Device Content and i would make print preview using CView class. I've created my own class based on CView class CsingleView : public CView { }; And I add new template in InitInstance CSingleDocTemplate* pDocTemplate; pDocTemplate = new CSingleDocTemplate( IDR_MAINFRAME, RUNTIME_CLASS(CsingleDoc), RUNTIME_CLASS(CMainFrame), RUNTIME_CLASS(CsingleView)); theApp.AddDocTemplate(pDocTemplate); Now,...

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...

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 ...

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 ! -------------------------------...

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...

Cell help!
I put in 8 lines of info with in acell but when I click outside of th cell it appears as a long string of dollar signs in the cell. How can I get the cell to reflect the contents I inseterted into i when I click outside -- Message posted from http://www.ExcelForum.com Widen the cell anau < wrote: > I put in 8 lines of info with in acell but when I click outside of the > cell it appears as a long string of dollar signs in the cell. > > How can I get the cell to reflect the contents I inseterted into it > when I click outside? > > > --- > Message posted from...

Pivot Table Help #5
Hi All Is there any way that I can change the source data range after the pivot table has been created, without having to do a new sheet. I.e. Curent range is A1:D25 Want to amend to A1: F1000 Cheers Paul T 1.. Right-click a cell in the pivot table 2.. Choose PivotTable Wizard 3.. Click the Back button, and select the new range 4.. Click Finish. "PJ" <paul.thomas5@ntlworld.com> wrote in message news:am9yj.52197$os2.41778@newsfe3-win.ntli.net... > Hi All > > Is there any way that I can change the source data range after the pivot > table has been ...

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...

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...

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...

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...

Re: Please help with an excel mathematical formula
Re: Please help with an excel mathematical formula r = pay rate (say 1.13) p : extra profit in percentage on b ( say 50% more on b) previos lost bet: a (say 100) estimated new bet : b total receivable : b x r ( should be greater than a+b and with extr profit of 50% on b) Please help with a formula as to decide the value of b ! Thanks in advance Eduard -- EduardoDo ----------------------------------------------------------------------- EduardoDon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=849 View this thread: http://www.excelforum.com/showthr...

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...

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...

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...

Urgent Help
Dear All, We are Planing to Have One Forest, with Multiple Domains Trees as the following :- 1. KTC.COM 2. MUX.COM 3. TRU.COM 4. LON-UK.COM and Each one of the abouve Domain have 25 users as the following :- 1. KTC.COM ( Have 25 Users ). 2. MUX.COM ( Have 25 Users). 3. TRU.COM ( Have 25 Users). 4. LON-UK.COM ( have 25 Users ). So the Total Users will be 100 Users. i am going to install KTC.COM as the Forest Root Domain, & Install DNS server on one Machine and configure one Forward Zone with name of the Forest Root Doamin which is ( KTC.COM) and this DNS server is the Root ( .) . a...

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 ? ...