I need to use an update query to calculate the following 1st of April for each of 3000+ records, the only exception is if the Start Date is the 1st of April in which case the date will remain the same.

Examples

StartDate AdjStartDate

01/03/08 01/04/08
10/09/06 01/04/07
15/04/08 04/04/09
01/04/08 01/04/08

Can anyone advise the best way to do this?

Regards

Hawk

Ok

Create the following Function in a module:

Function AdjDate(AnyDate As Date) As Date

If Month(AnyDate) < 4 Then

AdjDate = CVDate("01/04/" & Year(AnyDate))

Else

AdjDate = CVDate("01/04/" & (Year(AnyDate) + 1))

End If

End Function


Then Design a new select query

Drag down the date field you want to update, and place the following in the first column

NewDate:AdjDate([YourField])

Next bring down the date field again into the next column

Run the query in datasheet view to look at the results.

Verify it works ok

Then highlight the NewDate column and select Copy.
Highlight the Orignan date column and select paste.

This will update your 3000+ records in one fail swoop.

CodeMaster::cool:

The above fails on 1st April when it adds a year.

Brian

Cheers,

Fixed Minor Glitch with the following change

Function AdjDate(AnyDate As Date) As Date

If AnyDate = ("01/04/" & Year(AnyDate)) Then

AdjDate = CVDate("01/04/" & Year(AnyDate))

Else

If Month(AnyDate) < 4 Then

AdjDate = CVDate("01/04/" & Year(AnyDate))

Else

AdjDate = CVDate("01/04/" & (Year(AnyDate) + 1))

End If

End If

End Function