Working with dates in Access

[BobCrawf]

I have a numerical field "PM_Month" with 1 through 12 indicating the month a PM is due on a piece of equipment.

Another field which is date format needs to hold the PM date for the current year using the month from the PM_Month field.

I am attempting to use an update query to calculate the PM_Date field but can't get it to work.

Basically I am attempting to change the month in Now() to the number stored in PM_Month and put the new date value in the PM_Date field.

Any help would be appreciated.

Bob Crawford

 

[Muttley]

Create a new field using the Year function to extract the year from your Now() statement, then use one of the string manipulation functions (left, mid etc.) to add the month from your PM month field to the year statement. Something like this should work:

Left([mytable]![PM_Month],2) & "/" & Year(Now())

Replacing placeholders of course. You will need to check your formatting to deal with e.g leading zeros in dates, but if you treat fields as text this should not be a problem.

Hope this helps

Adrian Hope