Excel VBA - SQL statement for access

Question

I have quite literally tried every combination and asked a friend for the reason why there's an issue with the syntax of this SQL statement to an access database...

strSQL = "INSERT INTO Usage (User, Tool, OpenDate, ProjectNum, UseNum)" _
             & " VALUES ('" & Username & "','" & ToolName & "','" & myDate _
             & "','" & ProjNum & "',1)"

This line generates this statement which I track in the watch window:

"INSERT INTO Usage (User, Tool, OpenDate, ProjectNum, UseNum) 
VALUES ('204073081','D:\Tools_Dev\Wind_Farm_Automation_Dev\Current\testWind.xlsm','2/17/2012','30',1)"

Thanks.

Fionnuala · Accepted Answer · 2012-02-17 14:39:56Z

4

You have everything as text, whereas I would expect numbers and dates to be entered as such:

"INSERT INTO Usage (User, Tool, OpenDate, ProjectNum, UseNum) 
VALUES (204073081,'D:\Tools_Dev\Wind_Farm_Automation_Dev\Current\testWind.xlsm',#2/17/2012#,30,1)"

If everything is to be saved to text type data, why is the final 1 not text?

answered Feb 17, 2012 at 14:39

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

thebiglebowski11 Over a year ago

Everything is entered as text, except for the date and UseNum

Fionnuala Over a year ago

The delimiter for date data type is hash (#), not apostrophe (') and numeric data type should not be delimited. What is important is not the value, but the datatype of the Access field (column).

thebiglebowski11 Over a year ago

Right well all the access columns are text, except the data (entered as a date) and UseNum (entered as a number).

Collectives™ on Stack Overflow

Excel VBA - SQL statement for access

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related