Excel VBA INSERT INTO statement using variables
Why not make use of date/time field to include test time?
It is best to format dates, assuming that b5 is a date type:
testDate = Format(Range("b5").Value,"yyyy/mm/dd")
You will end up with text, but this is not important.
stSQL1 = "INSERT INTO [Friction Tests] ([Runway Number], [Runway Test Side], " _
& "[Test Date], [Test Time], [1/3], [2/3], [3/3], [Average]) " _
& "VALUES ( " & rwyNumber & ",#" & testDate & "#,#" & testTime _
& "#," & rwySide1 & "," & firstThird1 _
& "," & secondThird1 & "," & thirdThird1 _
& "," & averageFriction1 & ")"
If any of the fields are text, the value will have to be surrounded by single quotes or two double quotes, in a similar way to date and time.
I strongly recommend getting rid of spaces in field (column) and table names.
ScottK
Updated on June 04, 2022Comments
-
ScottK almost 2 years
I am using an SQL query to insert data from an excel worksheet into an Access database. I put all the data that I want from the worksheet into variables:
rwyNumber = Range("b13").Value & Range("c13").Value testDate = Range("b5").Value testTime = Range("b6").Value rwySide1 = Range("b14").Value firstThird1 = Range("b28").Value secondThird1 = Range("b29").Value thirdThird1 = Range("b30").Value averageFriction1 = Range("b23").Value
If I want to store testDate and testTime as Date/Time, do I need to convert them when I store them in the variable (above) or will access automatically make them a Date/Time as long as I have the data type set to Date/Time for those columns in the table? Also with doubles and strings.
What is the correct syntax for the rest of my query:
stSQL1 = "INSERT INTO Friction Tests (Runway Number, Runway Test Side, Test Date, Test Time, 1/3, 2/3, 3/3, Average)" stSQL1 = "VALUES ( " ....
The table has an auto PK. The first two values are strings, the next two are Date/Time, and the last four are doubles.