Another update query

  • Thread starter Thread starter Jack Sheet
  • Start date Start date
J

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.
 
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))
 
Thanks again, as always.

John Spencer said:
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))
Click to expand...
 
Back
Top