Qry is not working, looking for correcr code.

[WAG]

I have a calibration database for controlling the calibration of equipment. One of the fields is a calculated field based on the length of time before the tool has to be re-calibrated. As an example if the calibration date is 01/01/2014 and the length of time before it has to be calibrated again is 1 year the access will calculate the calibration due date as 01/01/2015. My problem is that everything works except for the "Out of Service" selection. When the "Out of Service selection is chosen access is supposed to calculate a calibration due date the is 100 years in the future. 01/01/2014 would calculate to 01/01/2114. This ensures that this tool will not show up in any of the calibration due date reports that I run on a weekly basis




Below is the code that I am using:

IIf([tblCalibCert]![Calibration Interval]="6Months",DateAdd("m",6,[tblCalibCert]![CalibrationDate]),IIf([tblCalibCert]![Calibration Interval]="12Months",DateAdd("d",365,[tblCalibCert]![CalibrationDate]),IIf([tblCalibCert]![Calibration Interval]="24Months",DateAdd("m",24,[tblCalibCert]![CalibrationDate]),IIf([tblCalibCert]![Calibration Interval]="60Months",DateAdd("yyyy",5,[tblCalibCert]![Calibration Date]), IIf([tblCalibCert]![CalibrationInterval]="Out of Service",DateAdd("yyyy",99,[tblCalibCert]![CalibrationDate]),Date())))))Or <[Enter Date To Report Past Due Certificates:]

Does anyone have any ideas as to why the "Out of Service" does not calculate the date. The term length come from a pull down list and all of the terms are there. When the "Out of Service" is selected the calibration due date shows to be the current date. Please HELP

Thatks
WAG

[ItsMe]

Why not use the "Out of Service" field in a where clause within your SQL?

[ranman256]

FYI, if calculated fields get THIS complicated...build a FUNCTION().
They are easier to read and debug:
usage in the query: calcInterval([Calibration Interval])

Public Function calcInterval(ByVal pvIntv)
Select Case pvIntv
 Case "6Months"
    calcInterval = DateAdd("m", 6, pvIntv)
 
 Case "12Months"
    calcInterval = DateAdd("m", 12, pvIntv)
 
 Case "24Months"
    calcInterval = DateAdd("m", 24, pvIntv)
 
 Case "60Months"
    calcInterval = DateAdd("yyyy", 5, pvIntv)
 
 Case "Out of Service"
    calcInterval = DateAdd("yyyy", 99, pvIntv)
 
 Case Else
    calcInterval = Date
End Select
End Function

[rpeare]

or have a table with your calibration choices and in that table have the number of months you want to add and work that into the calculation

Also, doesn't require you to change formulas, you'd just have to update month values if they changed.