datevalue - unexpected results

[Guest]

I have a query where I want to take the [exposed date] and if it's less than
or equal to now() then have it return "exposed". On the other side, if the
[exposed date] is greater than or equal to now() I want it to return "the
month and year". I'm getting unexpected results. On half of the results it's
working correctly but the other half it doesn't.

Could you please pass down some wisdom of where I'm going wrong. Thank you
as always!

Bucket2: IIf([exposed date]<=DateValue(Now()),"Exposed",IIf([exposed
date]>=DateValue(Now()),[exposed date], "mm/yyyy" ))

 

[John Spencer]

Bucket2: IIf([exposed date]<=Date(), "Exposed",
IIf([exposed date]>Date(), Format([exposed date], "mm/yyyy" ), Null))

I used Date() instead of DateValue(Now()) because Date() = DateValue(Now())
I Changed => to just > in the second IIF since you use <= in the first
I used the Format function to format the date (assumption is your were
trying to do this)
I added Null as the false outcome of the second IIF - not needed, but just
cleaner to my eyes.

 

[Guest]

Okay, I figured it out. My [exposed date] was a calculation field and so I
just combined that calculation into my statement and it works perfect!

Keith

 

[Marshall Barton]

I have a query where I want to take the [exposed date] and if it's less than
or equal to now() then have it return "exposed". On the other side, if the
[exposed date] is greater than or equal to now() I want it to return "the
month and year". I'm getting unexpected results. On half of the results it's
working correctly but the other half it doesn't.

Could you please pass down some wisdom of where I'm going wrong. Thank you
as always!

Bucket2: IIf([exposed date]<=DateValue(Now()),"Exposed",IIf([exposed
date]>=DateValue(Now()),[exposed date], "mm/yyyy" ))

Try this:

Bucket2: IIf([exposed date] <= Date(), "Exposed",
Format([exposed date], "mm/yyyy" )