SQL query .execute delete (almost there!)

  • Thread starter Thread starter kidkarma
  • Start date Start date
K

kidkarma

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
 
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,

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
Click to expand...
 
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\#")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Regan via AccessMonster.com said:
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,

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
Click to expand...
Click to expand...
 
Back
Top