MDB to ADP sql date criteria issue

[chainedtodesk]

i had an MDB which had the following block of code which verified licensing dates, in the mdb it worked fine but in ADP i get an error saying DATE invalid, i saw a thread that was similar and they used getdate but that doesnt work either, can this type of function be done in ADP? thanks

Private Sub esc_monitor_Click()
DoCmd.SetWarnings False
Dim SQL As String

SQL = "SELECT ESC_new.NAME, ESC_new.Licensing, ESC_new.[Current License expiration date], if(GetDate(),)>[current license expiration date],'Expired',if(GetDate()+15,)>=[current license expiration date],'Expires within 15 Days',if(GetDate()+30,)>=[current license expiration date],'Expires within 30 Days',if(GetDate()+45,)>=[current license expiration date],'Expires within 45 Days','NA')))) AS Expr1 INTO ESC_Exp " & _
"FROM ESC_new " & _
" WHERE (((if(GetDate(),)>[current license expiration date],'Expired',if(GetDate()+15,)>=[current license expiration date],'Expires within 15 Days',if(GetDate()+30,)>=[current license expiration date],'Expires within 30 Days',if(GetDate()+45,)>=[current license expiration date],'Expires within 45 Days','NA')))))<>'NA')) "

DoCmd.RunSQL SQL

Dim frmname As String
frmname = "frm_ESC_LicExp"
DoCmd.OpenForm frmname

End Sub

 

[cmmrfrds]

Typically the IIF function is Access can be replaced with the CASE Statement in Transact_sql (the SQL for sql server).

Look up Case in the BOL documentation. Maybe a search case statement will work.

(case
when (getdate()+45)>=[current license expiration date] then 'Expired in 45 days'
when (getdate()+30)>=[current license expiration date] then 'Expired in 30 days'
when (getdate()+15)>[current license expiration date]
then 'Expired in 15 days'
when (getdate())>[current license expiration date]
then 'Expired' else 'NA' end) as expr1