Update query error

[Billy B]

I have the following code and get an error message at the docmd.runsql line.
What is wrong with my syntax?

Private Sub Form_Close()

Dim datCurrDate As Date, datNewDate As Date
Dim strSQL1 As String, strSQ2 As String, strSQLFull As String

strSQL1 = "UPDATE sysTbl SET [ReturnVal] = "
strSQL2 = "WHERE [Comparison]='Backup’;"

datCurrDate = Date
datNewDate = datCurrDate + 30
strSQLFull = strSQL1 & datNewDate & strSQL2


'Gets text date value from tblSys
strResetsysTblDate = DLookup("[ReturnVal]", "tblSys", "[Comparison] =
""Backup""")

'Converts text in sysTbl to date value
strResetsysTblDate = DateValue(strResetsysTblDate)

If datCurrDate >= strResetsysTblDate Then
MsgBox "It has been more than 30 days since your last backup " &
vbNewLine & _
"Please consider doing a backup after this form closes " &
vbNewLine & vbNewLine & _
"You will not be reminded for another 30 days "
DoCmd.RunSQL strSQLFull
End If

 

[Jeff L]

After concatenating your strings together, your SQL Statement looks
like this:

UPDATE sysTbl SET [ReturnVal] = 6/21/2006
WHERE [Comparison]='Backup';

In your statement, your date is being evaluated as 6 divided by 21
divided by 2006, obviously not what you want. You need to put single
quotes around the date value.

Hope that helps.

 

[John Spencer]

strSQL1 = "UPDATE sysTbl SET [ReturnVal] = "

'Add a space before the WHERE Clause
strSQL2 = " WHERE [Comparison]='Backup';"

datCurrDate = Date
datNewDate = datCurrDate + 30
'Add # marks around the date field if you are using an mdb
strSQLFull = strSQL1 & "#" & datNewDate & "#" & strSQL2

'For testing purposes, add these two lines
Debug.Print StrSQLFull
Stop

Copy the resulting string into a new query and see if it runs. If it does,
then go back and delete the two added lines.