Another update query

[Jack Sheet]

Field DOB contains a date

Field LATEST contains a date

Field DEADLINE is blank but will contain a date

Field NEXT contains an integer (representing a year), ie 2006

Field YEM contains an integer in the range 1 to 12 inclusive (representing a
month)

Field YED contains an integer in the range 1 to 31 inclusive (representing a
day in the month).

It can be taken as read that the range of values in YED are suitably
restricted for months of less than 31 days specified by YEM.



I should be grateful doe an SQL statement (Access 97) for an Update query
that will enter in "DEADLINE" the equivalent of the following Excel
expression:

=MIN(DATE(NEXT,YEM+10,YED),DATE(YEAR(DOB),MONTH(DOB)+22,DAY(DOB)-1)

Any help gratefully received, thanks.

 

[John Spencer]

Assumption: None of the values will be null

Use Date Serial to calculate the two dates.

DateSerial(Next,YEM+10,YED)

DateSerial(Year(DOB),Month(Dob)+22,Day(Dob)-1)

Combine that in an IIF statement - watch out this will wrap in the
newsgroup, so you will have to get it back into one line.

IIF(DateSerial(Next,YEM+10,YED)<DateSerial(Year(DOB),Month(Dob)+22,Day(Dob)-1),DateSerial(Next,YEM+10,YED),DateSerial(Year(DOB),Month(Dob)+22,Day(Dob)-1))

 

[Jack Sheet]

Thanks again, as always.

Assumption: None of the values will be null

Use Date Serial to calculate the two dates.

DateSerial(Next,YEM+10,YED)

DateSerial(Year(DOB),Month(Dob)+22,Day(Dob)-1)

Combine that in an IIF statement - watch out this will wrap in the
newsgroup, so you will have to get it back into one line.

IIF(DateSerial(Next,YEM+10,YED)<DateSerial(Year(DOB),Month(Dob)+22,Day(Dob)-1),DateSerial(Next,YEM+10,YED),DateSerial(Year(DOB),Month(Dob)+22,Day(Dob)-1))