IIF in qurey criteria

I am trying to add an IIF statement to my query criteria. I have a form with 
an option group (optRept)with 2 choices: 1. Give me all records where total 
sales > [Forms]![frmOrderRept].[txtSlsMin] (this a text box on the form to 
enter sales $ criteria) or 2. Give me all records where total sales <= 
[Forms]![frmOrderRept].[txtSlsMin]. When I run the query I am not getting the 
row returned that I should.

The IIF state is as 
follows:IIf([Forms]![frmOrderRept].[optRept]=1,([tblSalesSumm].[TotalSls])<Val([Forms]![frmOrderRept].[txtSlsMin]),([tblSalesSumm].[TotalSls])>=Val([Forms]![frmOrderRept].[txtSlsMin]))

When I have option 1 selected I get no rows returned. When I have option 2 
selected I get only the records where TotalSales = 0.

To add to my confusion if I eliminate the IIF statement and just use one of 
the criteria I get the results I want. Any help would be greatly appreciated.
0
Utf
5/4/2007 8:29:00 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
809 Views

Similar Articles

[PageSpeed] 6

You can't create criteria like that in Access.

If you're comfortable working with the SQL, try setting the WHERE clause to 
something like:

WHERE ([tblSalesSumm].[TotalSls]<Val([Forms]![frmOrderRept].[txtSlsMin]) AND 
[Forms]![frmOrderRept].[optRept]=1)
OR ([tblSalesSumm].[TotalSls]>=Val([Forms]![frmOrderRept].[txtSlsMin]) AND 
[Forms]![frmOrderRept].[optRept]<>1)

Through the graphical query builder, you'll have to have criteria on two 
separate lines.


-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Bill Phillips" <BillPhillips@discussions.microsoft.com> wrote in message 
news:9EDEF7C0-4F24-47AA-BCCC-E9B64EDF1CB5@microsoft.com...
>I am trying to add an IIF statement to my query criteria. I have a form 
>with
> an option group (optRept)with 2 choices: 1. Give me all records where 
> total
> sales > [Forms]![frmOrderRept].[txtSlsMin] (this a text box on the form to
> enter sales $ criteria) or 2. Give me all records where total sales <=
> [Forms]![frmOrderRept].[txtSlsMin]. When I run the query I am not getting 
> the
> row returned that I should.
>
> The IIF state is as
> follows:IIf([Forms]![frmOrderRept].[optRept]=1,([tblSalesSumm].[TotalSls])<Val([Forms]![frmOrderRept].[txtSlsMin]),([tblSalesSumm].[TotalSls])>=Val([Forms]![frmOrderRept].[txtSlsMin]))
>
> When I have option 1 selected I get no rows returned. When I have option 2
> selected I get only the records where TotalSales = 0.
>
> To add to my confusion if I eliminate the IIF statement and just use one 
> of
> the criteria I get the results I want. Any help would be greatly 
> appreciated. 


0
Douglas
5/4/2007 9:16:51 PM
"Bill Phillips" <BillPhillips@discussions.microsoft.com> wrote in message 
news:9EDEF7C0-4F24-47AA-BCCC-E9B64EDF1CB5@microsoft.com...
>I am trying to add an IIF statement to my query criteria. I have a form 
>with
> an option group (optRept)with 2 choices: 1. Give me all records where 
> total
> sales > [Forms]![frmOrderRept].[txtSlsMin] (this a text box on the form to
> enter sales $ criteria) or 2. Give me all records where total sales <=
> [Forms]![frmOrderRept].[txtSlsMin]. When I run the query I am not getting 
> the
> row returned that I should.
>
> The IIF state is as
> follows:IIf([Forms]![frmOrderRept].[optRept]=1,([tblSalesSumm].[TotalSls])<Val([Forms]![frmOrderRept].[txtSlsMin]),([tblSalesSumm].[TotalSls])>=Val([Forms]![frmOrderRept].[txtSlsMin]))
>
> When I have option 1 selected I get no rows returned. When I have option 2
> selected I get only the records where TotalSales = 0.

It's not at all clear what you think you're trying to do from the above, but 
maybe all you need is a bit of clarification on what your IIF statement is 
doing. If you don't use the third parameter of an IIF statement, it will 
return 0 (false) when the condition in the first parameter is not met.  Your 
first parameter will also return only 0 or 1, since it is a boolean 
evaluation of whether or not the value in one of your controls is greater or 
equal to the value of another control.  So your Where clause will evaluate 
to LIKE 0 or LIKE 1 (You can only use IIF in a LIKE expression).

HTH;

Amy 


0
Amy
5/5/2007 4:40:22 AM
Reply:

Similar Artilces:

Return a value if criteria contained in cell
I would like to return a value if criteria is met. If A2 contains the word "apples", then in cell B2, write "apples". If A2 contains the word "oranges, then in cell B2, write "oranges". If A2 does not contain the words "apples" or "oranges", leave the cell blank. Can you help me with a formula for this? Thanks in advance. IF(OR(A2="apples",A2="oranges"),A2,"") "jhicsupt" wrote: > I would like to return a value if criteria is met. > > If A2 contains the word "apples", then in...

3464 Data Type Mismatch in Criteria Expression
I have a double-click event that's making me nuts. I'm getting the 3464 error message and the debugger points to the line below in >< Any ideas what's wrong? I'm drowning! Private Sub cbSupplier_DblClick(Cancel As Integer) If IsNull(Me!cbSupplier) Then ' no supplier ID selected; show all ... DoCmd.OpenForm "frmPKSuppliers" Else ' Show selected supplier name. ' First, open frmPKSuppliers to show the supplier name ... DoC...

custom views-no available values in 'edit filter criteria'
hi there, i am trying to create a custom view that is based on one of the values in a picklist within a custom field. when i go into edit filter criteria, i select the field i want, choose equals and then when i get to 'enter value' i have no options in the 'available values' area. I gather i should have the picklist values as available values to choose from? I tested it using another standard field; marital status then i have options in the available values area. but i had added another 2 options to the standard picklist-de-facto and separated; which do not appear in va...

Help with IIF statement in macro
I am using the followign statement in my macro: Sheets("Split").Range("K" & LoopID).Value = IIf(Val(Sheets("New York").Range("I" & StartPoint) & "") < 4, "DATABASE", "FURNITURE") So I am saying if value in Range I < 4 then its DATABASE else its FURNITURE I want to add one more parameter.. that is if I < 4 then its Database if its between 4 to 7 then its Furniture and if its more than 7 then its Leasehold. Could someone please advise me on what the statement should be modified to. Th...

Outlook Contact Sync Criteria
Hello, Out users want to be able to sync CRM contacts down to their Outlook contacts. But they want to sync more than the set of contacts that they own, without downloading the entire contact database from crm. Is there a way to allow them to sync contacts that have been shared to them, as well as the contacts that they own? Thanks - they will get all contacts they can see in CRM. You may want to look at the security roles and se if you can split the users up into seperate business units. I am assuming they can all see all the contacts in your crm system? ======================= J...

2 criterias filtering
hy, i need help with this: i have a table, with data, and i want to filter this table by criterias I attached an image with my table..so, i want to select in A2 fo example, first criteria...{ Nicu }... then second criteria , in B2 ...{ Marti }, .....and in C2 and D2, i want to see the all the matche for this two criteria, like in table 2.I want criteria to be dropdow boxes. Can this be done even if my table data it's random? I tried to say this as simple as posible...sorry for my gramatica errors. thank +------------------------------------------------------------------- |Filename: ta...

Avoiding duplicate data based on criteria for another field in a q
I look after the admin for our fishing club. I've created a database (access 2003) with around 1200 records. we have members and non members who fish with seperate tables for each. Ive been working on a method to show the takings from between two dates for the non members. I've achieved it with one date (eg todays, or yesterdays) but if I put dates in covering several days or weeks It serves up junk! The problem is to create a query which will show each date on which someone fished (between the criteria dates entered using a form into the "date of fishing" field in the...

VBA Advanced Filter Criteria (How to insert UsedRange in Range Sta
Using XL 2003 & 97 My Code is: Sheets("Filtered Data").Range("A1:Q5217").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Criteria Sheet").Range(BEM_CUY), _ CopyToRange:=Range( "A1"), Unique:=False How do I get the Range statement to sense the used range? Otherwise Q5127 is a constant. TIA Dennis How about this (not tried I am afraid) Set oRng = Sheets("Filtered Data").UsedRange oRng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Criteria Sheet").Range(BEM_CUY), _ CopyToRange:=Range( &...

multiple criteria in one field 4a,4b etc of countif?
Now trying to do COUNTIF(A5:X5,"5a,5b") etc but it keeps telling me I have an error More help would be appreciated. =SUMPRODUCT(--(A1:A10={"5a","5b"})) -- Regards, Peo Sjoblom "Anne-Marie" <AnneMarie@discussions.microsoft.com> wrote in message news:D2AFB162-BB23-442A-827D-60C9A66CF9D7@microsoft.com... > Now trying to do COUNTIF(A5:X5,"5a,5b") etc but it keeps telling me I have an > error > More help would be appreciated. ...

SUMIF with two criteria #4
Here's the senario. Column A has dates of sales. Column B has the sales person's name. Column C has the amount of the sale. What I am looking for is a sumif statement that will total the sales in column C by salesperson in between specific dates. So for example, I want all the sales for John Smith totaled between the dates of 4/1/05 and 4/30/05. I've tried a bunch of different things but have had no luck getting this to work. Any help is appreciated. Try... =SUMPRODUCT(--($A$2:$A$10>=DATE(2005,4,1)),--($A$2:$A$10<=DATE(2005,4,30) ),--($B$2:$B$10="John Smit...

Criteria for age calculation
I have a simple query Name and Date of Birth. I want to calculate the age in a query. I have tried different criteria from MS Access Tutorials. Can someone please help me with a correct criteria to calculate the contacts age as of today from the date of birth. Thanks, Deeds37 Deeds37, Without seeing what you tried... can you tell me why the below didn't work for you? =DateDiff("yyyy", [YourBirthDateField], Now())+ Int( Format(Now(), "mmdd") < Format( [YourBirthDateField], "mmdd") ) -- Gina Whipp 2010 Microsoft MVP (Access) ...

2 Criteria Formula
I am trying to write a formula that says something like if(a2="Tier 2" and A3="January","Monthly","". I don't know how to do the and for a 2 criteria formula. Any help? -- Coltsfan ------------------------------------------------------------------------ Coltsfan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19982 View this thread: http://www.excelforum.com/showthread.php?threadid=501779 Try IF(AND See: http://www.officearticles.com/excel/if_statements_in_formulas_in_microsoft_excel.htm Likely, =IF(AND(a2="...

Multiple Criteria in Listbox
I'm hoping someone can help me. I'm not a programmer but found this really nice piece of code that I have been using but it needs to be altered. I am using Access 2002. I have code on a button click event. This code allows me to pick multiple course titles, [txtCourseTitle] from a list box and generate reports for all the courses I've chosen. Now I need to add a second piece of criteria. I now have a list box with two fields, [txtCourseTitle] and [dtmStartDate]. The [dtmStartDate] is entered as 01/01/2010 but is formatted as a long date. I need the date too because...

Multiple criteria count
Hi ppl, Well what i want the formula to calculate is, count the rows whic contains lets say "Companyname" in column B and "yes" in column I. But with the formula i use now it doesn't include the rows wit "Companyname USA" so how can i achieve this ?? And this is what i got so far: {=SUM(IF('2003'!B1:B1500="companyname";IF('2003'!I1:I1500="yes";1;0)))} Thnx in Advance Siny- ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://...

Problem With Query Criteria
This is probably not very hard but I am very new to access and am trying to build an entire database. I am making a query from a table and I need to pull out records that are done in April in the Month field but I want it to leave out the records that are 13/1 and 13/2 in the Sheet No field. It will let me filter the April records taking out the 13/1 or the 13/2 but if i try to ask it to leave out both 13/1 and 13/2 it will no longer only take the records from the month of April. Any suggestions on how to learn more about expressions as well would help! -- Chels We're not the...

Counting on two Criteria
I have a spreadsheet that records units of work completed by a project team. Column B records team member names. Column C records the status of each work unit � �pending�, �beta� or �completed�. It�s easy to set up a formula to count how many work units are allocated to each team member - =COUNTIF(B1:B1000,"Joe"). But what I need to do is to count how many units Joe has pending, in beta, or completed. This means setting up formulas which assess data in both Column B and Column C � and COUNTIF won�t do this. This doesn�t strike me as an especially uncommon thing to want to do, and...

Help with query criteria
I am creating an inventory database. Inventory is taken 3 times per day based on shift start. I want to be able to create a parameter query and then a report that will show the inventory taken at the start of each shift. There are nine areas that are inventoried each shift. I need to be able to query by date and time. I want the times to be broken down by shift name (i.e. Days, Afternoons, Nights). The form that feeds the table where the query is run has a txtDate field and txtTime field with default values of =Date() and =Time() so that when the data is saved to the Inventory table the ...

sorting using formatting criteria
Is it possible to sort a spreadsheet using formatting as a criteria. I have a spreadsheet with thousands of rows, and the fonts for several of the rows have been changed to indicate edited rows. Steve Hi Steve this would be only possible using VBA. e.g. returning some formating info through a user defined function in a helper column and sorting this helper column. For example try the following to check of a bold font: Public Function Is_Bold(rng As Range) As Boolean Dim vTemp As Variant Application.Volatile vTemp = rng.Font.Bold If IsNull(vTemp) Then ...

How to display query criteria in my Report?
Hi, I have searched but found no answers, so I guess this is standard :P Anyhow, how do I get a certain fields query criteria written in the report? (For example, my data source is a query that selects all customers (cust_id) with annual turnover gretater than 10,000 (criteria is >10000). Now, sometimes I change the criteria so it would be nice to have it dynamically turn up on the report (rather than static text). But what's the "call function" for this? Kindly, Mikael Mikael Lindqvist wrote: >I have searched but found no answers, so I guess this is standard :P &g...

Sumif with multiple criteria Pt 2
OK If sumif wont work what will.. Range A1:F1 is yes or blank Range A2:F2 is number positive or negative G2 sum of negative nos where A2:F2 is neg & A1:F1 is yes H2 sum of positive nos where A2:F2 is pos & A1:F1 is yes Thanks Steve, SUMPRODUCT AS the man said G2: =SUMPRODUCT((A1:F1="yes")*(A2:F2<0),(A2:F2)) H2: =SUMPRODUCT((A1:F1="yes")*(A2:F2>0),(A2:F2)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steve" <stevenh5361@aol.com> wrote in message ...

two criteria in DLookup
No error when compiling but when open form, got 'type mismatch'. Here is the code: Private Sub Form_Load() If DLookup("[userid]", "users", "Trim([userid]) = '" & Left(fOSUserName(), 8) & "' AND [EmailAll] = " & -1) Then cmdMassEmail.Enabled = True ElseIf DLookup("[userid]", "users", "Trim([userid]) = '" & Left(fOSUserName(), 8) & "' AND [Grade] = " & -1) Then cmdGrade.Enabled = True End If End Sub Both [EmailAll] a...

URGENT : Adding search criteria ..............
Hi, I have created a new field in contact form, now how can i add this newly added field in the search criteria. (ex) Added a new field in contact form named MemberId(string field). in the search box of contact form how can i add this new field in the search. Thanks in advance. Richard: What search box are you talking about - the "Find Contacts" box? I believe it only seraches the name fields. If you want to find contacts based on other fields (including custom ones) you need to use the advanced find (from the Tools menu). Dave "Richard I.P" <iamiamiam_77@yaho...

calculation based on meeting two criteria
I have the following formula and it returns the wrong calculation K1 =.03 K2 =.02 In this situation, its supposed to multiply F82*K2, instead it is multiplying F82* K1 =IF(E82="service a",F82*$K$1,IF(AND(E82="Service B",MATCH(C82,'Sheet 2!$C$2:$C$15)),F82*$K$1,F82*$K$2)) any help is appreciated. To simplify your formula try this: =IF(OR(E82="service a",AND(E82="Service B",MATCH(C82,'Sheet 2!$C$2:$C$15))),F82*$K$1,F82*$K$2) -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://w...

SUMIF date criteria
Can anyone help with a Date range criteria in a SUMIF statement. I'm trying to add up Values in line 4 between 2 dates a b c d e f 1 start 11/11 2 end 13/11 3 dates 10/11 11/11 12/11 13/11 14/11 4 value 2 3 4 5 6 5 sumif(be:f3,">="&b1,b4:f4) will do more or = to one date how do i do a range. Any help would be welcome Thanks Hi Try this formula: =SUMIF(A3:E3,">="&A1,A4:E4)-SUMIF(A3:E3,">" &A2,A4:E4)...

Multiple Listbox selectin and query criteria
I've read many of the posts here about using multiple list box selections to filter queries yet I still cant get it to work. I'll post my understanding then my code (which I am totally clueless about) and see which is at fault. My table is called "sales", my query is "sales summary Query", my filter form is " Filter Report", my listbox is "customerselect" and set to simple multi select I then have a hidden control named "customerselect2" with on click set to event procedure, then in the procedure I've pasted the code ...