Finding a field from last month to put in new month

[dssrun]

In a query, or maybe it can be done in VB, how do I search for a record from last month. There is a date field in each record, and the primary key is the name of the employee AND the month. I am trying to carry over a number field from last month to the new month data for that employee and I can accomplish this in excel using the edate function but I cannot figure this out in access as I am migrating my data to access. I am familiar with SQL.

Thanks

[ConneXionLost]

Create a calculated field like:

Recordmonth: Month([YourMonthField])

then put on criteria like:

=Month(Now())-1

Cheers,

[dssrun]

Hm, I would think this would work but it is going back one year, not one month. My date field (not the calculated one) is setup like 10/1/2010, and the calculation field is bring back 10. I will try to look into this as well, but any ideas?

[dssrun]

actually sorry it went back one month AND one month and one year. Is there a way to do this on one field without dividing the date field into a month column and year column? (e.g. in your criteria introduce AND to get -1 and the year now(), even though this will come back to bite me when the new year switches over)

[ConneXionLost]

If you have more than one years worth of records in your data, then use this as your calculated field:

RecordMonth: Year([YourDateField]) & " " & Month([YourDateField])

and this as criteria:

=IIf(Month(Now())=1,Year(Now())-1,Year(Now())) & " " & IIf(Month(Now())=1,12,Month(Now())-1)

[dssrun]

works! thanks. I tried something similar but was using the "AND" instead of "&" and the query was returning nothing. But now it is good.