Running SQL from VBA

[macca]

I want to update a date field (called dater) in a table called tbldate.

The function dow gets the week day based on the system clock e.g dow= 4

The function prevdate then calculates which date to use based on a Select Case, e.g 28/07/2009

The problem lies in the function dated, the Debug.Print strsql shows the following: UPDATE tbldate SET tbldate.dater = 28/07/2009

But when I check the updated field (dater) it has the value 30/12/1899. Can anyone help

Public Function dow()

dow = Weekday(Now())

End Function
-------------------------------------------------------------------------
Public Function prevdate()

Select Case dow
'dow is Tue (3) to Sat (7)
    Case 3 To 7
    prevdate = Date - 1
    Debug.Print prevdate

    Case Else
    prevdate = Date - 3
    Debug.Print prevdate
End Select

End Function
--------------------------------------------------------------------------
Public Function dated()
Dim strsql As String
Dim db As Database

DoCmd.SetWarnings False
   strsql = ("UPDATE tbldate SET tbldate.dater = " & prevdate & "")

 'Print the SQL so we can paste into the query build if there are errors
   Debug.Print strsql
   
'Use Current Database
        Set db = CurrentDb()
 
    'Run the SQL Query
               
           DoCmd.RunSQL strsql

DoCmd.SetWarnings True

End Function

 

[Scooterbug]

Try using # at the beginnig and end of the date. ie:

strsql = ("UPDATE tbldate SET tbldate.dater = #" & prevdate & "#")

I also noticed that you had one too many " at the end of your statement

Also, you can use:

Currentdb.execute "SQLstatement"

instead of the docmd.runsql You wont need to set the warnings with the .execute command

 

[Galaxiom]

Dates within SQL must always be in MM/DD/YYYY format and enclosed in #s.

The reuslt you are getting is the arithmetical expression 28/7/2009 which equates to 0.00199
So if you included time in your date field format it would show 30/12/1899 00:02:52 AM.

 

[macca]

Thanks to all. It is now working