Change date to set month

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi,

I have a TestDue date which I want to change in some cases to a fixed month
PRIOR to the TestDue Date. One table contains a field holding the value of
the month for each location. Depot1 has MonthTest = 5 for May.

How can I change the TestDue date to be the same day of the month but in the
month obtained from the table.

E.g. If Test Month is 5

Then 11/21/2007 will become 5/21/2007
and 4/21/2008 will become 5/21/2007

Thanks,

John
 
Use DateSerial() to create the date.

This example builds a date from the year and day of TestDue, and the month
number in MonthTest:
DateSerial(Year([TestDue]), [MonthTest], Day([TestDue])

If you have other conditions, you may need IIf(), IsNull(), etc.
 
Worked well once I sorted out the IIF statements. Thanks Allen.

John

Allen Browne said:
Use DateSerial() to create the date.

This example builds a date from the year and day of TestDue, and the month
number in MonthTest:
DateSerial(Year([TestDue]), [MonthTest], Day([TestDue])

If you have other conditions, you may need IIf(), IsNull(), etc.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

John said:
Hi,

I have a TestDue date which I want to change in some cases to a fixed
month PRIOR to the TestDue Date. One table contains a field holding the
value of the month for each location. Depot1 has MonthTest = 5 for May.

How can I change the TestDue date to be the same day of the month but in
the month obtained from the table.

E.g. If Test Month is 5

Then 11/21/2007 will become 5/21/2007
and 4/21/2008 will become 5/21/2007

Thanks,

John
Click to expand...
Click to expand...
 
Back
Top