Between Date Query

[Rainbowhawk]

Hi All

I need to modify a date query that currently looks like

Between #05/03/2007# and date())

I need to set it so that from the 05/03/08 it starts reporting a rolling 12 months

Which would normally look like

Between DateAdd("m",-12,Date())And Date()

However as I am on leave when this change needs to happen I am trying to create a query that would use the correct query depending on the date

I have got as far as

iif(date()>=#05/03/2007#,Between DateAdd("m",-12,Date()) And Date(),Between #05/03/2007# and date())

But this does not appear to work at all.

I also tried adding brackets to each part

iif(date()>=#05/03/2007#,(Between DateAdd("m",-12,Date()) And Date()),(Between #05/03/2007# and date()))

but this did not work either.

Any thoughts and suggestions would be appreciated.

 

[gemma-the-husky]

instead of tring to build the whole comparison in the criteria row, put a small function in a module to test the date

i think this should be ok, but test it

function needdate(indate as date) as boolean
if date <= #5/3/08# then
   needdate = indate>=#5/3/07# and indate<=date
else
   needdate = indate>=date and indate<=date+365
end if

now in your query put a column needdate(checkdate)
and test for true

 

[Rainbowhawk]

Thanks for that however I am now getting undefined function in expression as an error.

??

 

[gemma-the-husky]

have you put the function in a general code module , not a form module

are you sure the function name is the same in the query, as in the module

acces is saying it find the named function

 

[Rainbowhawk]

Hi I have checked and it is a General Module, also name is correct on query still no joy, I have however solved the problem with

>=#05/03/2008# And Between DateAdd("m",-12,Date()) And Date()

which seems to work.

Thanks for your help.