Populate Previous Month

[cori25]

Hello...

I am attempting to create code in a query to state: If the [LastDate] occurred in the previous month then 1, otherwise 0.

This is what I attempted to do:
[PHP]OTLast Mth:iiif([LastDate]=DateSerial(Year(Date()), Month(Date()), 1, 0) [/PHP]

It is not working.....anyone have any suggestions?

Thanks

[Stewart Ross]

...
I am attempting to create code in a query to state: If the [LastDate] occurred in the previous month then 1, otherwise 0.

This is what I attempted to do:
[PHP]OTLast Mth:iiif([LastDate]=DateSerial(Year(Date()), Month(Date()), 1, 0) [/PHP]

Hi Cori. Could you clarify what you need to do? Is it that you are checking whether the value of LastDate is in the current calendar month? I am not clear what you need to calculate. if, for instance, you wanted to know whether or not the LastDate had the same month as the current month you could use the DatePart function or the Month function:

IIF(Month(Date())=Month([LastDate], 1, 0)

but this is just an example pending clarification of what you really need to compare.

-Stewart

[cori25]

Hi Stewart,

Like I stated in the original post, it is looking for the previous month.

I actually was able to figure it out using this code:

[PHP]WorkedPrevMth: IIf(Format(qry_WorkedPrevMth_1!Date,"m")-1=Format(qry_WorkedPrevMth_1!LastDate,"m"),1,0)[/PHP]

Thanks for the response!

[NeoPa]

Hi Stewart,

Like I stated in the original post, it is looking for the previous month.
...

Unfortunately your original post was not expressed very well - hence clarification was required.
Consider - What does "the previous month" mean when there is no relative item? Prior to what?
I looked at your code to try and reverse engineer the question. This latest one seems to refer to two fields from the query, yet your original post referred to the Date() function. Consider how confusing this is for someone who can only understand what you need from what you say in your posts.

Anyway, assuming that the fields used in your latest post are what you need (you stated after all that this works) then your code would probably be something like :

WorkedPrevMth: IIf((Month(qry_WorkedPrevMth_1.Date) - 1) = Month(qry_WorkedPrevMth_1.LastDate), 1, 0)

I assume that [qry_WorkedPrevMth_1] is one of the data sources in the query you're working with.