Query Criteria Error message

  • Thread starter Thread starter CrissyF
  • Start date Start date
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))
Click to expand...

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

Thank you in advance for your help
 
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))
Click to expand...

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

Thank you in advance for your help
Click to expand...
 
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))
Click to expand...

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

Thank you in advance for your help
Click to expand...
 
If the date is less than June of 2008 then use this Criteria: <=-150000 Or
=150000 Else use no criteria
Click to expand...

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))
Click to expand...

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

Thank you in advance for your help
Click to expand...
Click to expand...
 
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
 
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)));
 
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
Click to expand...

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top