re-write code more efficiently

[lndsy]

Can anyone please help me re-write this code more efficiently?

Private Sub RESULTS_AfterUpdate()

DoCmd.RunSQL "UPDATE [EMPLOYEE HEALTH] SET [EMPLOYEE HEALTH].[TB DUE DATE] = " & _
"IIf([TB DUE DATE] > DMax(""[DATE]"", ""EH - TB"", ""[ID]=" & Me![id] & """) Or [TB DUE DATE] Is Null, " & _
"DateAdd(""yyyy"", 1, DMax(""[DATE]"", ""EH - TB"", ""[ID]=" & Me![id] & """)), " & _
"IIf([TB DUE DATE]<DMax(""[DATE]"", ""EH - TB"",""[ID]=" & Me![id] & """), " & _
"DateAdd(""yyyy"",1,[TB DUE DATE]))) WHERE [ID]=" & Me![id] & " AND [TYPE] <> ""CHEST X-RAY"""


End Sub

It works, but how can I simplify it in a module so it is more clear and DMax(""[DATE]"", ""EH - TB"",""[ID]=" & Me![id] & """) is declared once and then referenced throughout the code?

Thanks

 

[SkipVought]

Hi,

How's this?

Private Sub RESULTS_AfterUpdate()
    sDMax = _
    "DMax(""[DATE]"", ""EH - TB"", ""[ID]=" & Me![Id] & """)"
    sSQL = _
    "UPDATE [EMPLOYEE HEALTH] " & _
    "SET [EMPLOYEE HEALTH].[TB DUE DATE] = " & _
    "IIf([TB DUE DATE]>" & sDMax & " Or " & _
    "[TB DUE DATE] Is Null, DateAdd(""yyyy"", 1, " & sDMax & " ), " & _
    "IIf([TB DUE DATE]<" & sDMax & " , DateAdd(""yyyy"",1,[TB DUE DATE]))) " & _
    "WHERE [ID]=" & Me![Id] & " AND [TYPE] <> ""CHEST X-RAY"""
    DoCmd.RunSQL sSQL
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[lndsy]

Thank you skip...this is great!!! I appreciate your help