VBA Sql Syntax

[TidyTrax]

I have a value held within a VBA variable which i which to later using the docmd.RunSql statement insert into a table, what is the correct syntax for this

DoCmd.RunSQL "INSERT INTO tblTest2 (f1) VALUES (myVar)"

This is what i have just now which doesnt work.

The variable myVar is the vba variable i wish to insert into the table tblTest2

 

[quatermass]

Hi,

You will need to modify the statement to have the variable name outside the quoted sql command string

DoCmd.RunSQL "INSERT INTO tblTest2 (f1) VALUES (" & myVar & &quot"

You may have issues with data formatting. In which case, either change the variable data type, or put conversion functions into the sql statement.


Hope it helps

Kevin

**************************************************************
Rock is Dead (Long Live Paper and Scissors)**************************************************************

 

[TonyJollans]

Hi TidyTrax,

It depends on the data type of your column and variable. You need to build an SQL string which contains appropriate delimiters (quotes for strings, hashes for dates).

So if f1 (and myVar) are numeric, use

DoCmd.RunSQL "INSERT INTO tblTest2 (f1) VALUES (" & myVar & ")"

But if f1 is text (and myvar a string), use

DoCmd.RunSQL "INSERT INTO tblTest2 (f1) VALUES (

""

" & myVar & "

""

)"

And if f1 is a date (and myvar a string), use

DoCmd.RunSQL "INSERT INTO tblTest2 (f1) VALUES (

#

" & myVar & "

#

)"

Enjoy,
Tony

 

[TidyTrax]

Yeah i figured out it would be something to do with quote.

The problem i have at the moment, i can transfer my vba vars that are strings over to the DB, but i also have a series of longs that need to go into numeric fields - but im getting a type mis-match at the moment

DoCmd.RunSQL "INSERT INTO tblInput (pkId, location, cDate, Criminal Reports Received, Accused in Criminal Reports, Misc Reports Received, Death Reports Received, Total Reports Received)
VALUES ('" + f1 + "' , '" + f2 + "','" + f3 + "'," + f4 + "," + f5 + "," + f6 + "," + f7 + "," + f8 + &quot"


This is the code to populate tblInput, the last five ie: f4,f5,f6,f7,f8 are all type long, and are going to numeric DB fields. The expression works fine with just f1,f2,f3 which are strings, but addd the longs and it fails

 

[TidyTrax]

I now know its because of the spaces in the field headers of the table! How do i handle these??

 

[TonyJollans]

Hi TidyTrax,

You have two problems here.

The first, which is giving you the 'type' error, is using "+" as a concatenation operator. This works (and is slightly different from "&&quot when all the expressions are strings. With numeric expressions "+" is treated as meaning "add" and when a non-numeric expression is found the invalid type error is raised. To fix it, use "&" both sides of any numeric expressions - it would be best to be consistent and use "&" all through but it is only NECESSARY around the numbers.

The second problem is that your column names have spaces in them and so they MUST be enclosed in brackets. Use “[Criminal Reports Received]” instead of just “Criminal Reports Received”.

Enjoy,
Tony

 

[TidyTrax]

Thanks Tony, got it all going - so thanks a lot for your help your a star!