Help with IIF DateSerial exp

[Bill Fischer via AccessMonster.com]

Please help, maybe I have overlooked something but think I have the following
not working in either the true or false because of the >=. Doing searches I
came up no information on this issue. I plugged a #date# into both true/
false and it works fine until I insert the >= . I have even tried turning
the equation around and also without the field. So many attempts I am
beginning to think it is not possible.

What I'm looking for on this date field is Current Month records and if
before the 4th of the Current Month then Last Months records also.



IIf(Date()<=(DateSerial(Year(Date()),Month(Date()),4)),(DateSerial(Year
(Date()),Month(Date())-1,1)<=[AMonthYear]),(DateSerial(Year(Date()),Month
(Date()),1))<=[AMonthYear])

Any help will be greatly appreciated.

Thanks,

Bill

 

[Guest]

Bill,

I'm don't understand what you are trying to do. How are you useing the
formula? Is it in a query the criteria row for a date field or a boolean
field?

There is nothing wrong with the formula.... *but* both the Truepart and the
falsepart evaluate to either TRUE or FALSE ..... not a date.

If [AMonthYear] is the date field you want to search on (and you are useing
a query), the WHERE clause of the query would look like:

WHERE
((AMonthYear>=IIf(Date()<=(DateSerial(Year(Date()),Month(Date()),4)),DateSerial(Year(Date()),Month(Date())-1,1),DateSerial(Year(Date()),Month(Date()),1))))


Post back with more info if this doesn't work or you are trying to something
different.

 

[Bill Fischer via AccessMonster.com]

Steve thanks for the reply,

As I look back at my orginal post I can see why it was hard to understand my
intention (posted just before I need to take off for that American retail
cash cow tradition of Val day).

At any rate my orginal syntax was as below before I started messing with it
to get it functional and is from the criteria row for a date field query.

IIf(Date() <=(DateSerial(Year(Date()),Month(Date()),4)), (>=DateSerial(Year
(Date()),Month(Date())-1,1)), (>=DateSerial(Year(Date()),Month(Date()),1)))

Thanks for your post (works perfect and just as I needed it to), as it looks
like I would have needed to tweak a little from my orginal attempt but
obviously got lost and headed the wrong direction.

Thought I would post back for a BIG THANKS and to finish off in case any one
else could possibly have a need in this type of expression.

Bill

Bill,

I'm don't understand what you are trying to do. How are you useing the
formula? Is it in a query the criteria row for a date field or a boolean
field?

There is nothing wrong with the formula.... *but* both the Truepart and the
falsepart evaluate to either TRUE or FALSE ..... not a date.

If [AMonthYear] is the date field you want to search on (and you are useing
a query), the WHERE clause of the query would look like:

WHERE
((AMonthYear>=IIf(Date()<=(DateSerial(Year(Date()),Month(Date()),4)),DateSerial(Year(Date()),Month(Date())-1,1),DateSerial(Year(Date()),Month(Date()),1))))

Post back with more info if this doesn't work or you are trying to something
different.

Please help, maybe I have overlooked something but think I have the following
not working in either the true or false because of the >=. Doing searches I

[quoted text clipped - 15 lines]

Bill