Solved

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

Posted on 2011-03-21
6
294 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
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now