Solved

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

Posted on 2011-03-21
6
299 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Patrick O'Dea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 9

Accepted Solution

by:
McOz earned 125 total points
ID: 35180730
Try this:
BEFORE: 
CurrentDb.Execute "insert into [tblAuditlog] (Auditstr,auditDate,AuditCategory) values ( '" & AuditDetails & "',#" & Now() & " #,'" & strAuditCategory & "')"

Open in new window

0
 
LVL 4

Assisted Solution

by:joeyw
joeyw earned 125 total points
ID: 35180744
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
 

Author Comment

by:Patrick O'Dea
ID: 35180823
Thanks joeyw ,

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

(You code works perfectly by the way).
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 35181015
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 35181048
in addition.. if string have single quotes like  varName= O'Brien
you use

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

0
 

Author Closing Comment

by:Patrick O'Dea
ID: 35181108
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

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question