Query Criteria Error message


C

CrissyF

I am trying to get this If statement to work in the criteria field of my
query. If the field does not match either of the conditions, then I do not
want a criteria put on the field.

However, I am getting the following message:
The expression you entered has a function containing the wrong number of
arguments
when I enter this formula:
IIF(Year([DATE])<2008,<=-150000 Or
=150000,Year([DATE])=2008,IIF(Month([DATE])<7,<=-150000 Or >=150000))

Can someone please help me with the correct way to write this formula.

Thank you in advance for your help
 
Ad

Advertisements

J

Jerry Whittle

Write out what you want the IIf to do in individual sentences. For example:

If the year is less than 2008, the data should say?????
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


CrissyF said:
I am trying to get this If statement to work in the criteria field of my
query. If the field does not match either of the conditions, then I do not
want a criteria put on the field.

However, I am getting the following message:
The expression you entered has a function containing the wrong number of
arguments
when I enter this formula:
IIF(Year([DATE])<2008,<=-150000 Or
=150000,Year([DATE])=2008,IIF(Month([DATE])<7,<=-150000 Or >=150000))

Can someone please help me with the correct way to write this formula.

Thank you in advance for your help
 
K

KARL DEWEY

A couple of thing wrong. Syntax for IIF as follows --
IIF(Test for true, true results, false results)
Nested --
IIF(Test for true, true results, IIF(Test for true, True results, false
results))

You can not use '<=' or '>=' for IIF results. You can not use 'Or' as
result.
If you only used '<=-150000 Or >=150000' as criteria then you get all
records.

So, what are you looking for for criteria?
--
KARL DEWEY
Build a little - Test a little


CrissyF said:
I am trying to get this If statement to work in the criteria field of my
query. If the field does not match either of the conditions, then I do not
want a criteria put on the field.

However, I am getting the following message:
The expression you entered has a function containing the wrong number of
arguments
when I enter this formula:
IIF(Year([DATE])<2008,<=-150000 Or
=150000,Year([DATE])=2008,IIF(Month([DATE])<7,<=-150000 Or >=150000))

Can someone please help me with the correct way to write this formula.

Thank you in advance for your help
 
C

CrissyF

If the date is less than June of 2008 then use this Criteria: <=-150000 Or
=150000 Else use no criteria

Jerry Whittle said:
Write out what you want the IIf to do in individual sentences. For example:

If the year is less than 2008, the data should say?????
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


CrissyF said:
I am trying to get this If statement to work in the criteria field of my
query. If the field does not match either of the conditions, then I do not
want a criteria put on the field.

However, I am getting the following message:
The expression you entered has a function containing the wrong number of
arguments
when I enter this formula:
IIF(Year([DATE])<2008,<=-150000 Or
=150000,Year([DATE])=2008,IIF(Month([DATE])<7,<=-150000 Or >=150000))

Can someone please help me with the correct way to write this formula.

Thank you in advance for your help
 
L

Lord Kelvan

the error message is comming from the OR in your query that and your
query makes no sence

you need to change the structure of your query

SELECT Year([date]) as yearofdate, Month([ddate]) AS monthodfate,
nz([value],0) AS thevalue
FROM thetable
WHERE (((Year([date]))<2008) AND ((nz([value],0))<=-150000 Or
(nz([value],0))=>=150000)) OR (((Month([date]))<7) AND ((nz([value],
0))<=-150000 Or (nz([value],0))=>=150000)) OR (((nz([value],0)) Like
"*"));

replace thetable with your table name and value with what ever field
has that value in it

to use this query create a new query click view then sqlview then
paste the above with the modifications.

regards
Kelvan
 
L

Lord Kelvan

actually as an update try

SELECT Year([date]) as yearofdate, Month([ddate]) AS monthodfate,
nz([value],0) AS thevalue
FROM thetable
WHERE (((Year([date]))<2008) AND ((nz([value],0))<=-150000 Or
(nz([value],0))=>=150000)) OR (((Month([date]))<7) AND ((nz([value],
0))<=-150000 Or (nz([value],0))=>=150000)) OR (((nz([value],0)) Like
"*" and (Year([date])>2008 or Month([date])>7)));
 
Ad

Advertisements

K

KARL DEWEY

Try this ---
SELECT [YourTable].[Date], [YourTable].x, IIf([Date]<#6/1/2008#,1,2) AS Expr1
FROM [YourTable]
WHERE ((([YourTable].x) <=-150000 Or ([YourTable].x) >=150000) AND
((IIf([Date]<#6/1/2008#,1,2))=1)) OR (((IIf([Date]<#6/1/2008#,1,2))=2))
ORDER BY [YourTable].[Date] DESC;

--
KARL DEWEY
Build a little - Test a little


CrissyF said:
If the date is less than June of 2008 then use this Criteria: <=-150000 Or
=150000 Else use no criteria

Jerry Whittle said:
Write out what you want the IIf to do in individual sentences. For example:

If the year is less than 2008, the data should say?????
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


CrissyF said:
I am trying to get this If statement to work in the criteria field of my
query. If the field does not match either of the conditions, then I do not
want a criteria put on the field.

However, I am getting the following message:
The expression you entered has a function containing the wrong number of
arguments
when I enter this formula:
IIF(Year([DATE])<2008,<=-150000 Or
=150000,Year([DATE])=2008,IIF(Month([DATE])<7,<=-150000 Or >=150000))

Can someone please help me with the correct way to write this formula.

Thank you in advance for your help
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top