Query Criteria IIf statement

I'm using MSAccess 2003 and this seems like a simple process, especially 
compared to some of the things I'm trying to do in my database.  In the 
criteria of a query field, I am using the following IIf statement:

IIf([FYAGR]>3000000,3,<4)

[FYAGR] is a different field in the same query.  When [FYAGR] is greater 
than 3,000,000, I get 3 as expected, but no matter how I word the range of 
numbers I'm looking for in the false part of the statement, my query returns 
no records.  If I remove the IIf statement and place '<4' I get the correct 
range, but no matter the combination I can't seem to get the desired criteria 
out of the IIf statement.  I can also place a single digit in my false part 
and return that digit with no problems.  Is this a limitation of the IIf 
statement that I am unaware of?  Maybe there is something simple I'm missing, 
but any help would be appreciated.
0
Utf
3/10/2010 5:35:01 PM
access 16762 articles. 3 followers. Follow

4 Replies
1631 Views

Similar Articles

[PageSpeed] 23

hi Ian,

On 10.03.2010 18:35, ian wrote:
> IIf([FYAGR]>3000000,3,<4)
IIf() can only return a value, not an expression nor an expression part.

> Maybe there is something simple I'm missing, but any help would be appreciated.
A precise question?

I guess your trying to solve something like

SELECT *
FROM yourTable
WHERE [fieldA] = IIf([FYAGR]>3000000,3,<4);

Do you? In this case you can use it like this

SELECT *
FROM yourTable
WHERE IIf([FYAGR]>3000000, [fieldA] = 3, [fieldA] < 4);


mfG
--> stefan <--
0
Stefan
3/10/2010 5:45:45 PM
are you trying to return a text value ?  "<4"

"ian" wrote:

> I'm using MSAccess 2003 and this seems like a simple process, especially 
> compared to some of the things I'm trying to do in my database.  In the 
> criteria of a query field, I am using the following IIf statement:
> 
> IIf([FYAGR]>3000000,3,<4)
> 
> [FYAGR] is a different field in the same query.  When [FYAGR] is greater 
> than 3,000,000, I get 3 as expected, but no matter how I word the range of 
> numbers I'm looking for in the false part of the statement, my query returns 
> no records.  If I remove the IIf statement and place '<4' I get the correct 
> range, but no matter the combination I can't seem to get the desired criteria 
> out of the IIf statement.  I can also place a single digit in my false part 
> and return that digit with no problems.  Is this a limitation of the IIf 
> statement that I am unaware of?  Maybe there is something simple I'm missing, 
> but any help would be appreciated.
0
Utf
3/10/2010 6:16:05 PM
On Wed, 10 Mar 2010 09:35:01 -0800, ian <ian@discussions.microsoft.com> wrote:

>I'm using MSAccess 2003 and this seems like a simple process, especially 
>compared to some of the things I'm trying to do in my database.  In the 
>criteria of a query field, I am using the following IIf statement:
>
>IIf([FYAGR]>3000000,3,<4)
>
>[FYAGR] is a different field in the same query.  When [FYAGR] is greater 
>than 3,000,000, I get 3 as expected, but no matter how I word the range of 
>numbers I'm looking for in the false part of the statement, my query returns 
>no records.  If I remove the IIf statement and place '<4' I get the correct 
>range, but no matter the combination I can't seem to get the desired criteria 
>out of the IIf statement.  I can also place a single digit in my false part 
>and return that digit with no problems.  Is this a limitation of the IIf 
>statement that I am unaware of?  Maybe there is something simple I'm missing, 
>but any help would be appreciated.

You can't pass an operator such as < in an IIF statement - only an actual
value.

Try instead a criterion of

WHERE (([FYAGR] > 3000000 AND [thisfield] = 3) OR (FYAGR] <= 3000000 AND
[thisfield] < 4)

-- 

             John W. Vinson [MVP]
0
John
3/10/2010 6:34:20 PM
Both of these solutions accomplished exactly what I was looking for!  Thanks!

"ian" wrote:

> I'm using MSAccess 2003 and this seems like a simple process, especially 
> compared to some of the things I'm trying to do in my database.  In the 
> criteria of a query field, I am using the following IIf statement:
> 
> IIf([FYAGR]>3000000,3,<4)
> 
> [FYAGR] is a different field in the same query.  When [FYAGR] is greater 
> than 3,000,000, I get 3 as expected, but no matter how I word the range of 
> numbers I'm looking for in the false part of the statement, my query returns 
> no records.  If I remove the IIf statement and place '<4' I get the correct 
> range, but no matter the combination I can't seem to get the desired criteria 
> out of the IIf statement.  I can also place a single digit in my false part 
> and return that digit with no problems.  Is this a limitation of the IIf 
> statement that I am unaware of?  Maybe there is something simple I'm missing, 
> but any help would be appreciated.
0
Utf
3/10/2010 7:35:01 PM
Reply:

Similar Artilces:

Further Info on changing text in one cell to to criteria of another.
I have a workbook that charts the progress of my clients. One of m cells automatically determines their age, and when they become olde than six, I need another cell's text to automatically change to "AO (standing for "Aged Out" of my program). I cannot us the functio =IF(A1>6,"AO"," ") because I already have the clients status imputed i that cell. I need it to be a type of conditional formatting or VB cod so that it will automatically change once the reach 6. I have attache an example of my workbook. It has a VB code that changes the row colo based on ...

Append Query 02-28-08
I am trying to append data from tblitemline to tblitemlist. I only want to append new records. The unique field is ListID. I want to append 4 fields total. I can't get it to limit it to new records. Any help would be appreciated. Thanks! New on the basis of what criteria.? Whatever that criteria is it should either the primary key (there can be multiples fields involved as the primary key) or Indexed with NO duplicates, (this will work if the criteria is a single field). Once you set the table up that way, the append query will only add "new" records. You can still have a ...

Email of Statements
Can you use another pdf Writer other than Adobe for this feature No, you must use Adobe to use this particular feature. Kind of odd, isn't it? -- Charles Allen, MVP "rcr" wrote: > Can you use another pdf Writer other than Adobe for this feature yes and no. When print to pdf was added - 6.0 i think - there weren't many other options for creating pdf's. OK, now there are. But OK which ones do we add support for? Them all? Support for PDF output must support being able to automatically name the created output file without intervention. And I assume many bu...

vlookup from two sources
[Excel 2003] Is it possible to use syntax to perform a vlookup from a source and then another if the first is False? For example, My primary table of data is called "Materials" and my second source is a range A81:E140 on the same sheet as the vlookup, So something like : =vlookup(A1,Materials,2,vlookup(A1,A81:E140),2,False) Can anyone help with advice on syntax? Thankyou, Roger the false in a Vlookup is the mtach type, not an value to use if the formula is false. =if(iserror(vlookup(A1,Materials,2,false)),Vlookup(A1,A81:E140,2,false),Vlookup(A1,Mate...

database query/names issue, causing excel to be sluggish
I have an issue with an excel worksheet I've created. WorkbookA has "Names" which are used to define drop-down menus in WorksheetA. These "Names" are defined to reference a WorksheetB (also in WorkbookA) which itself is populated by a database query, pulling from another Excel WorkbookB. The logic is that WorkbookB should not be touched by the User, only an Admin, and Workbook A is used by User. The Names in WorkbookA are to prevent incorrect entries. Now that I've set it up, WorkbookA is moving really sluggish, but if I delete all the Names it speeds up again....

which query is faster, better to use?
There are multiple AdjournmentDates for any given NoticeID in tblNotices_AdjournemntDates. I want the most recent date for each NoticeID. Both of these produce the correct results. Is either one better than the other? SELECT DISTINCT NoticeID, (SELECT TOP 1 AdjournmentDate FROM dbo.tblNotices_AdjournmentDates WHERE NoticeID = A.NoticeID ORDER BY AdjournmentDate DESC) FROM dbo.tblNotices_AdjournmentDates A SELECT NoticeID, AdjournmentDate FROM dbo.tblNotices_AdjournmentDates A WHERE (AdjournmentDate IN (SELECT TOP 1 AdjournmentDate FROM tblNotices_AdjournmentDates WHER...

Load query with outdata until search
I have a form with sub form on it I use this form to search for products we keep in stock with prices, I have a Search text box on the form then i type in the product then hit the search button and it requerys the sub form Only problem i have is when i first load the form it show every product in the subform, so takes a long time to load Is there a way to load the page with out the date then i can type in product i want to search for then it displays the products Simon What about setting the .RecordSource property of that form (?subform) to "" in the OnLoad event,...

Using lookup instead of if-statements, how?
Aloha, Here is screen where I try to explain my problem: http://hem.bredband.net/maromb/example.html On sheet 1 I have a ranking list of with diffrent cities, haulier an differnt cost/weight. Sheet1 Weight A | B | C | D | City | Haulier | 0-2,5 | 5-7| 7-10... ____________________________ City1|Haulier1 | 100 | 95| 80 City1|Haulier2 | 110 | 100| 90 City1|Haulier3 | 120 | 110| 91 City2|Haulier1 | 95 | 90| 87 City2|Haulier2 | 105 | 100| 89 City2|Haulier3 | 107 | 105| 88 On sheet2 I have made a VBA User ...

query problem 02-11-08
Hi to Everyone I have a table (tblMunka) with the following columns: JobId; PersonalId;AreaId;StartDate;EndDate;ActiveJob I would like to a query which tell me how many personal was in a term in an area. I have a form (frmLetszam) where user can choose start and end dates from a calendar. I've tried with this but the result is not good: HAVING (((tblMunka.StartDate) Between [Forms]![frmLetszam]![cboEndDate] And [Forms]![frmLetszam]![cboEndDate])) OR (((tblMunka.StartDate)<[Forms]! [frmLetszam]![cboStartDate]) AND ((tblMunka.EndDate) Is Not Null)); Please help me! Best regards -- Mes...

Selection Criteria for Statements
When printing statements, no matter what type of seleciton criteria we enter (ie, print only statements for customers with balance > 0) all statements print. Any ideas ? Store Operations 1.2 SP1 ...

Uk Postcode Search Criteria
I Have a Text Box Set to Text for Post Codes, As The Input Mask is Rubbish!! What I Want to Be able To Do With In My Query, Is On The Post Code Field is to do a Search Criteria In The Box That Will Bring Up Results By Only Inputing the First Part E.G B77 and Not B77 3NW Or WS11 and Not WS11 0JN Even Though The Postcode Field Will Hold The Full Postcode On 24 Jul, 11:02, Andy Hull <AndyH...@discussions.microsoft.com> wrote: > Hi Dan > > If I understand correctly, you want to be able to enter part of a postcode > in a text box on a form and have a query use this to ...

Money 2003 statement download problems
I used Money 2003 with no issues for over a year. It's always been cranky, but now it is no longer automatically downloading bank statements. The information for the accounts is correct and it can connect me to the web pages just fine. IN some cases, Money also does not show new information when I download the ActiveStatement Money file manually from a bank webpage. Also, every time I do manage to download a file, Money crashes when opening it and needs to be restarted. Ideas or suggestions? I swear if I have to buy a new version, it won't be a MS product. Maybe the onl...

Query with Combo Boxes on a Form
I'm using QBF with about six different combo boxes using: [forms]! [formmain]! [combo1] or [forms]![formmain1]![combo1] -like in a VBA book. This is so users can select criteria on a form with the combo boxes, and when they are done, they hit the search button, and it opens up another form based on the query just performed by the combo selection. This worked for about three combo boxes, but when I added another one, it freezes up and opens up a blank page. It's supposed to open the new form based on the query. Is there a better way to do this? Any help would...

Nested IF statement #2
Excel only allows 7 nested If statements, is there a way around this? -- jgannon ------------------------------------------------------------------------ jgannon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29003 View this thread: http://www.excelforum.com/showthread.php?threadid=487455 Without more information, (Like exactly what are you trying to do that you need more than seven nested if statements?) I can't help you. BUT you might try spreading out your function over more than one column, such that the output of one function gives an incomplet...

LDAP Query #4
im sorry if this post is in the wrong group how can i create an ldap query to get all the users with exchange boxes inside a specific OU this OU might also have OUs indise it with users that hafve mailboxes i want this to be able to create customized address lists Thank you Address Lists cannot be targeted at particular OUs. You need to use some user attribute that's common for users in that OU - like department, location, company name, etc. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Nazgyl" <naz...

If statement with 9 sequences.. problem
Hello everybody, I currently have an IF statement w/8 sequences and it is working properly. I'm doing this for tax purposes. When I try to add one more sequence, it doesn't work. basically I just want to say If B4<529,0. I want it to return a value of zero if cell B4 is < 529. This is my current formula: =IF(B15<943,(B15-529)*0.1,IF(B15<2914,(B15-942)*0.15,IF(B15<3618,(B15-2913)*0.25,IF(B15<4772,(B15-3617)*0.27,IF(B15<5580,(B15-4771)*0.25,IF(B15<8347,(B15-5579)*0.28,IF(B15<14670,(B15-8346)*0.33,IF(B15>14669,(B15-14669)*0.35,0)))))))) P...

Creating a Report using two queries
I have created a chart report (bar chart) using one query. How can I create another single chart showing the results of both queries. One will show the bars of first query data and the other will show bars of the data from second query together in a single chart report. Any suggestions? -- FL This depends on how/if the two queries are related. I would expect the "X" axis values might be related. If so, join the two queries into a single query and graph the new query. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www...

why doesnt ar account age if it is marked no statement
what does aging have to do with if a customer's statement? ---------------- 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 Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=193905fd-c73a-4c65-a9b0-6410e0795658&dg=microsoft.public.g...

Counting with 2 criteria
I need to be able to pull counts from a frequently updated list based on associate's name and status of order. -- Amethyst =SUMPRODUCT(--(A1:A100="associate name"),--(B1:B100="status")) should do it for you, adjust the columns/rows to match your sheet. You could have a couple of cells to enter associate name/status into to make things easier; as associate name in C1 and status in D1: =SUMPRODUCT(--(A1:A100=C1),--(B1:B100=D1)) "Amethyst" wrote: > I need to be able to pull counts from a frequently updated list based on > associate&#...

Complex Update Query in SQl Server 2000
I need to update ACTINDX in UPR40500 for each JOBTITLE in MYTABLE. We have modified the ACTNUMBR in 'MYTABLE' whose segments are contained in GL00100 GL00100 ACTINDX ACTNUMBR_1 ACTNUMBR_2 ACTNUMBR_3 ACTNUMBR_4 ACTNUMBR_5 30562 222 411001 000000 150 350 30563 222 411001 000000 155 355 UPR40500 JOBTITLE ACTINDX 209401 30562 150001 30563 MYTABLE JOBTITLE ACTNUMBR 209401 222-411001-000000-150-350 150001 222-411001-000000-155-355 Sean: If you are storing the account number as a string in my table, an...

Select Query Broken
Does any one know, of the issue in A2k3 With the Query, I was creating some combo list filters, via query's then putting the SQL in a Marco Apply filter. work great then all i sudden, now, when i view the query in normal mode, (NOT Design mode) Its Showing all the fields that are related on the table the query is based on, even though only two fields have been selected. and the show tick is ticked? just for 2, any ideas whats happening here? ive tried repairing and compacting database and also restarting access no luck? On 10 Sep, 16:42, KARL DEWEY <KARLDE...@discussions.microsoft...

Public folders: query regarding the "synchronize now" function
Exchange 2003 SP2 I have a query regarding the "synchronize now" function for public folders. Say I have a public folder called "Bob" with replicas on 3 servers (ServerA, ServerB, ServerC) If I drill down to Folders/Public Folders Select the "BOB" public folder and select the "Status Tab" on say ServerA, I see all 3 replicas listed as expected: If I right click a replica I can select "synchronize now" what does this do exactly? Does it: 1) Push updates out from the replica where you right click and select "synchronize now" ...

Query Button Problem
On my form, I need to put a button to run a query. When using the toolbox in the Design View, I use the Command Button Toolbar, then use MIscellaneous, then Run Query. I select the correct one, and save it to the form. When I try to use it, I get an error message, and it will not work. What is going wrong? Can't figure it out if you don't provide an error message. On Fri, 1 Feb 2008 10:52:00 -0800, Shari <Shari@discussions.microsoft.com> wrote: >On my form, I need to put a button to run a query. When using the toolbox in >the Design View, I use the Command Button To...

criteria for filtering check boxes in a query
I am trying to do a permanent sort for columns using check boxes. I need to know the formula for the criteria box On Wed, 20 Jun 2007 14:54:41 -0700, Desiree wrote: > I am trying to do a permanent sort for columns using check boxes. I need to > know the formula for the criteria box What is "a permanent sort for columns using check boxes". Perhaps your terminology is clear to you, but I think you would do well to re-think and repost your question. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail On Thu, 21 Jun 2007 15:38:03 -0700, Desir...

How do u create criteria with a LIKE "xxx" or/and
I would like to find out how to use Query to filter out selective data like start with an alphabet "d" and "e"? How do I define in my query? In the criteria row of the relevant field column in your query put this (case is irrelevant) Like "d*" Or Like "e*" "stacy sin" wrote: > I would like to find out how to use Query to filter out selective data like > start with an alphabet "d" and "e"? How do I define in my query? stacy sin wrote: | I would like to find out how to use Query to filter out sele...