Problems with deleting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to delete a record from a table. The filed is in the format
"dd/mm/yy hh:mm"

My SQL statement is: DoCmd.RunSQL "DELETE * FROM Tbl_Times_Available WHERE
[Food Time] = " & Selected_Food_Time & ";"

When I run the code I get an error message:

'Run-time error '3075'

Syntax error (missing operand) is query expression '[Food Time] = 16/02/2006
1:00:00 PM'

I suspect it wil be due to the space between the year and the hour. The
'Food Time' field in the table is defined as General Date.
 
Try Date Delimiters,

DoCmd.RunSQL "DELETE * FROM Tbl_Times_Available WHERE [Food Time] = #" &
Selected_Food_Time & "#;"
 
DoCmd.RunSQL "DELETE * FROM Tbl_Times_Available WHERE [Food Time] =
#" & Selected_Food_Time & "#;"
Click to expand...


For Andrew - when passing dates to the jet engine, you have to take care
of formatting: Jet will not interpret DMY dates reliably. When using
RunSQL, the user interface will reformat the command behind your back. On
the other hand, using RunSQL will pop up those crass "you are about to
delete two thousand records, are you happy?" warnings, and using
..SetWarnings False is just too dangerous to contemplate.

The cleaner way is to use direct database access and to take care of the
formatting explicitly.


Const jetDate as string = "\#yyyy\-mm\-dd\#"
dim jetSQL as string

'
' Note that Jet ALWAYS expects an ISO date format or a USA
' one, regardless of what your regional settings think
'
jetSQL = "DELETE FROM tbl_Times_Available " & vbNewLine & _
"WHERE [Food Time] = " & Format(selected_food_time, jetDate)

' programmer-friendly check...
debug.assert vbYes=MsgBox(jetSQL, vbYesNo, "Is this okay?")

' use the execute method to avoid the UI warnings
currentdb().Execute jetSQL, dbFailOnError



Hope that helps


Tim F
 
Back
Top