Solved

One line of VBA ... frustrating , quotes, single and double etc

Posted on 2011-02-28
11
265 Views
Last Modified: 2012-05-11
This nex t line below works fine

CurrentDb.Execute "insert into [tblAuditlog] (Auditstr) values ( ' " & AuditDetails & " ')"


However, I also have a field AuditDate in tblAuditlog and I want to put now() into this field.

How do I change my VBA to add this date.

... tried multiple things so far but failed..
0
Comment
Question by:Patrick O'Dea
  • 2
  • 2
  • 2
  • +3
11 Comments
 
LVL 59

Assisted Solution

by:Chris Bottomley
Chris Bottomley earned 100 total points
Comment Utility
If it's truly a date then try date() rather than now()

Chris
0
 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 100 total points
Comment Utility
CurrentDb.Execute "insert into [tblAuditlog] (Auditstr,AuditDate) values ( ' " & AuditDetails & " ','" & Now() & "')"

Or use date()
0
 
LVL 12

Assisted Solution

by:Paul_Harris_Fusion
Paul_Harris_Fusion earned 100 total points
Comment Utility
Build a string and include the datetime  as a formatted string (using the format function)

e.g.    
dim s as string
s = "INSERT INTO [tblAuditLog]  mydateField VALUES "
s = s & "(#" & format$(Now() , "ddd/mmm/yyyy hh:nn:ss")  & "#)"

CurrentDb.Execute s
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 150 total points
Comment Utility
CurrentDb.Execute "insert into [tblAuditlog] (Auditstr,auditDate) values ( '" & AuditDetails & "',Now())"

or this


CurrentDb.Execute "insert into [tblAuditlog] (Auditstr,auditDate) values ( '" & AuditDetails & "',#" & Now() &" #)"

0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Depending on string or numeric (I think) it would be :

CurrentDb.Execute "insert into [tblAuditlog] (AuditDate) values ( ' " & date() & " ')"
or
CurrentDb.Execute "insert into [tblAuditlog] (AuditDate) values (date())"

Chris
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Patrick O'Dea
Comment Utility
Thanks for you help.
I have lots of solutions.

Before I close the query...

Can anybody refer me to a site that explains the the usage of quotes and single quotes when inserting into a table.
I understand most of this area but seem to struggle (and guess) everytime.
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
Comment Utility
SQL uses single quotes to denote strings.
VBA uses double qotes to denote strings

So a VBA string that contains an SQL statement will use double-quotes for the vb part but single quotes internally.

e.g.    
dim sql as string
sql = " INSERT INTO MYTABLE (FIRST_NAME)  VALUES (  'paul' )   "
0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
Access accepts both quotes and double quotes. SQL only accepts quotes. Quotes force the value to be considered as string. Usually, when you use quotes with dates, it gets converted automatically (same as numbers when inserting the string '5' into a numeric field, for example).
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 50 total points
Comment Utility
None seems complete:

CurrentDb.Execute "Insert Into tblAuditlog (Auditstr, AuditDate) Values ('" & AuditDetails & "', Now())"

/gustav
0
 

Author Closing Comment

by:Patrick O'Dea
Comment Utility
thanks all
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
<None seems complete:>
post at http:#a34997270  a copy of post from http:#a34997031
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

771 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

10 Experts available now in Live!

Get 1:1 Help Now