CurrentDb.Execute "insert into [tblAuditlog] ,,,,,, Quotes again

Hi See code below.  

I have added a new field "AuditCategory" onto my tblAuditlog.
My VBA insert to this table previously worked but now I want to add this new field to the insert statement..

How Do I add the variable "strAuditCategory" into my insert statement - (it should be written to the field name AuditCategory obviously).  

I have tried a few combinations but have got confused by the quotes, commas etc.  (This confusion is a speciality of mine!)
BEFORE:
CurrentDb.Execute "insert into [tblAuditlog] (Auditstr,auditDate) values ( '" & AuditDetails & "',#" & Now() & " #)"

Open in new window

Patrick O'DeaAsked:
Who is Participating?
 
McOzConnect With a Mentor Commented:
Try this:
BEFORE: 
CurrentDb.Execute "insert into [tblAuditlog] (Auditstr,auditDate,AuditCategory) values ( '" & AuditDetails & "',#" & Now() & " #,'" & strAuditCategory & "')"

Open in new window

0
 
joeywConnect With a Mentor Commented:
I've struggled with the quotes thing on sql fields in the past.  The easiest thing that I have is to use the chr statement

values (chr(34) & auditdetails & chr(34) ....
0
 
Patrick O'DeaAuthor Commented:
Thanks joeyw ,

McOz,  I probably should know but what does the # do.

(You code works perfectly by the way).
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
21Dewsbury,
if you are just taking notes, you should know this by now as this was posted sometime ago in one of your questions..

for number data type you use  " & myNumbervariable  & "
for text                                '" & mytextVariable  & "'
                 (exploded view )   ' " & myTextvariable & " '

for Date  Data Type               #" & dateVariable & "#
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
in addition.. if string have single quotes like  varName= O'Brien
you use

" & chr(34) & varName & chr(34) & "

0
 
Patrick O'DeaAuthor Commented:
Thanks all,

Incidentally, capricorn1, I did actually refer to the query of a few weeks ago.

However, I misinterpreted the solution posted by Mcoz and thought that there was additional #'s due to the additional string.

On second reading the # is not relevant to my query.

Thanks again,
0
All Courses

From novice to tech pro — start learning today.