delete query using docmd.runsql

[bindurajeesh]

I have the following code piece:

strsql2 = "delete los_service.* from los_service where member_number = " &
intmembernumber & " and los_beg_time = #" & rs1!los_beg_time & _
"# and los_end_time = #" & rs1!los_end_time & "#
and service = '" & rs1!service & "' and los_service_date = #" &
rs1!los_service_date & "#"
DoCmd.RunSQL strsql2

when i run the procedure and then query the los_service table the record is
still in the table. Why isnt the delete query working?

 

[Dirk Goldgar]

I have the following code piece:

strsql2 = "delete los_service.* from los_service where member_number = " &
intmembernumber & " and los_beg_time = #" & rs1!los_beg_time & _
"# and los_end_time = #" & rs1!los_end_time & "#
and service = '" & rs1!service & "' and los_service_date = #" &
rs1!los_service_date & "#"
DoCmd.RunSQL strsql2

when i run the procedure and then query the los_service table the record
is
still in the table. Why isnt the delete query working?

One possibility is that the criterion on los_service_date is being
interpreted incorrectly. If, for example, your locale uses day/month/year
order for dates, then you should ensure that date literals embedded in SQL
are either in US data format -- month/day/year -- or else in an unambiguous
format. One way to do this is to explicitly format the date value as you
build the SQL string; for example:

"' and los_service_date = " & Format(rs1!los_service_date,
"\#mm/dd/yyyy\#")

That may not be the problem in this case, but it is one possible cause. If
that doesn't fix it, post the value of strsql2 after it has been built,
before it is executed. Maybe the fault will become clear.

 

[Dirk Goldgar]

One possibility is that the criterion on los_service_date is being
interpreted incorrectly.

Another possibility is that the date field, los_service_date, actually
contains both a date and a time. That would be the case, for example, if it
had been set to a value returned by the Now() function.

 

[bindurajeesh]

here is strsql2 after built

delete los_service.* from los_service where member_number = 94 and
los_beg_time = #10:15:00 AM# and los_end_time = #10:30:00 AM# and service =
'PRAG' and los_service_date = #5/5/2008#

thanks for your wisdom.