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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.