Query Criteria Error message

[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

 

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

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

 

[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

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

 

[CrissyF]

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

=150000 Else use no criteria

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.

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

 

[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

 

[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)));

 

[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

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

=150000 Else use no criteria

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.

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