iif combined with Between/And expressions not working

[Guest]

I need to get the data for last month if the current date is within a week of
the start of the month or else return the current months data. This is an
expession in the citeria for a query.

It's not the date expressions as they run fine on their own, and I have even
forced the evaluated expression to just True or False and still it does not.
I don't have any messages so no syntax errers are obvious.

IIf(DateDiff("ww",DateValue(Month(Date()) & "/1/" & Year(Date())), Date()) <
1, 'Between #2/1/2005# And #2/28/2005#', 'Between #1/1/2005# And #1/31/2005#')

 

[John Vinson]

On Wed, 2 Feb 2005 14:15:01 -0800, "Alan Lemen" <Alan

I need to get the data for last month if the current date is within a week of
the start of the month or else return the current months data. This is an
expession in the citeria for a query.

It's not the date expressions as they run fine on their own, and I have even
forced the evaluated expression to just True or False and still it does not.
I don't have any messages so no syntax errers are obvious.

IIf(DateDiff("ww",DateValue(Month(Date()) & "/1/" & Year(Date())), Date()) <
1, 'Between #2/1/2005# And #2/28/2005#', 'Between #1/1/2005# And #1/31/2005#')

You cannot pass *operators* such as BETWEEN using a parameter in this
way.

Try instead using a criterion

= DateSerial(Year(Date()), IIF(Day(Date() < 7, Month(Date()) - 1, Month(Date()), 1)

AND <= DateSerial(Year(Date()), IIF(Day(Date() < 7, Month(Date()),
Month(Date() + 1), 0)


John W. Vinson[MVP]

 

[Guest]

John, many thanks. I'll invert the logic like you suggest.
Makes sense, if only it would tell you. Is this documented somewhere?
I am not a VB user, but do a lot of programming elsewhere.

Alan

 

[John Vinson]

On Thu, 3 Feb 2005 08:11:02 -0800, "Alan Lemen" <Alan

John, many thanks. I'll invert the logic like you suggest.
Makes sense, if only it would tell you. Is this documented somewhere?
I am not a VB user, but do a lot of programming elsewhere.

Not really - I learned this trick years ago on this very newsgroup.
"Folk wisdom" gets passed on!

John W. Vinson[MVP]