SQL query .execute delete (almost there!)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kidkarma - 08 Mar 2007 23:33 GMT

Hi all,

This is my SQL query

.Execute "DELETE * FROM tblData WHERE ([StaffName] = '" & mystaff & "'
And [Date] = '" & startDate & "'"

comes out as a run time error

Missing ),], or item in query expression '([StaffName]= 'Smith John'
And [Date]='09/03/2007".

I know its going to be forgetting an apostrophe or something but ive
only just picked up Access and i can;t seem to get this running

Thanks in advance

Reply to this Message

Regan - 09 Mar 2007 01:26 GMT

the error says you need another ) at the end
the one below should work

try this(not tested)

"DELETE * FROM tblData WHERE staffname = """ & mystaff & """
AND date =#" & startdate & "#"

and you shouldn't use date as a field name, use something like entrydate.
hope it works

>Hi all,
>
[quoted text clipped - 12 lines]
>
>Thanks in advance

Signature

Regan,
Paeroa
World famous in New Zealand

Reply to this Message

Douglas J. Steele - 09 Mar 2007 11:39 GMT

Since kidkarma is showing 09/03/2007 as the value for the date field, I'm
going to assume that he/she has his/her Short Date format set to dd/mm/yyyy
(through Regional Settings). Regardless of what the Short Date format has
been set to, Access is always going to interpret #09/03/2007# as 03 Sept,
2007.

You're absolutely correct that date should not be used as a field name,
since date is a reserved word. However, if kidkarma cannot (or will not)
change the field name, enclosing it in square bracket should help minimize
problems.

.Execute "DELETE * FROM tblData WHERE " & _
"staffname = """ & mystaff & """ & _
" AND [date] =" & Format(startdate, "\#mm\/dd\/yyyy\#")

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> the error says you need another ) at the end
> the one below should work
[quoted text clipped - 23 lines]
>>
>>Thanks in advance

Reply to this Message