iif statement with And in true statement

[johnnyc]

Hey Guys,

i have a query with the following criteria in one of the fields:

>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())

fom is a function for first of the current month. i need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())

i have also added the column name to each expression and it still doesnt produce any results.
Please help!!!

 

[plog]

I sort of follow what you are trying to do, but don't fully understand where you are putting this code. Where is that Iif statement going? If you are trying to put it in the criteria section of the query it won't work like that.

I think it would be best to take 2 steps back and demonstrate what you want to occur with sample data. So, provide me with 3 sets of sample data:

A. Will be sample starting data from the table the query is based on. Basically, its sample data that you are feeding into your query.

B. Will be what the query shoudl produce from A if you run the query on 9/1/2014.

C. Will be what the query should produce from A if you run the query on 10/1/2014.

Be sure to provide table and field names for your data. Use this format for posting:

TableNameHere
Field1NameHere, Field2NameHere, Field3NameHere
David, 34, 5/1/2010
Larry, 19, 4/3/2008
Sally, 55, 7/19/2010

Again, 3 sets of data--starting and then 2 resulting data.

 

[Brianwarnock]

Presumably this is the criteria for a datefield so I would expect criteria like, using SQL

Where (month(date())>=10 And myfield between DateAdd("m",-12,fom()) And DateAdd("m",1,fom())) Or (month(date())<10 And myfield Between DateAdd("m",-12,fom()) And fom())

This is air code so there could be syntax errors, bracketing , but I can't see any.

Brian

 

[Mihail]

@Brian
Am I understand well ?
The criteria (in design view window for query) can be an SQL statement ?
Thank you !

 

[Brianwarnock]

No , I was writing the criteria as it would appear in SQL view. I often used to switch from design to SQL to complete a query, I felt that it was easier to follow the criteria logic in SQL view.

Brian