SQL INSERT INTO - Commas Problem

[Winger]

This is a common problem I seem to be having...incorrect use of quotes and
double quotes. From looking at prevoius threads I still can't solve my
current problem.

I want to insert a single record, and the code again works fine with
absolute values, but won't put the variable value in.

DoCmd.Run SQL "INSERT INTO tblResults (FundName, Matches,Score) Values (
Funder,Matches,Score)"

I've tried variations such as .........Values (
&Chr$(34)Funder&Chr$(34)....etc
and also ....Values('[Funder]',.... but then I get "expected end of
statement" error.

If any one can provide the corect syntax for the above problem, and maybe a
brief explanation as to how quotes, double quotes, and use of Chr$(34) should
be used to differentiate betwenn absolute values and variables etc, I sure
that would help many people.

Many thanks

Winger

 

[Paul Warshawsky]

Are Funder, Matches and Score the actual values you want to insert or
variables?

If they are the actual values, use:

DoCmd.Run SQL "INSERT INTO tblResults (FundName, Matches,Score) Values (
'Funder', 'Matches', 'Score')"

If they are string variables, use:

DoCmd.Run SQL "INSERT INTO tblResults (FundName, Matches,Score) Values ( '"
& Funder & "', '" & Matches & "', '" & Score & "';"

If one of them is a numeric value then it doesn't need the single quotes.
For example if Score is a numeric value then use:

DoCmd.Run SQL "INSERT INTO tblResults (FundName, Matches,Score) Values ( '"
& Funder & "', '" & Matches & "', " & Score & ";"

What you are doing is building up a string (& concatenates two parts of a
string together). If Funder is "Me", Matches is "You" and score is 10, then
the final string ends up as:

DoCmd.Run SQL "INSERT INTO tblResults (FundName, Matches,Score) Values (
'Me', 'You', 10);"

Good luck,

Paul