variables in strings

[Damon Grieves]

Hi I'm having trouble with variables in an INSERT INTO string that I'm
executing in A97 to SQL Server 2k tables.
I can get the SQL command to work using values such as :
strSQL2 = "INSERT INTO tblEmployee "
strSQL2 = strSQL2 & "SELECT tblProducts.CostCentre, tblProducts.Class,
tblProducts.ProductGroup, 9016255 AS EmployID, tblProducts.Description,
tblProducts.Division, tblProducts.BusinessUnit, tblProducts.Category,
tblProducts.EmplDefault AS Allocation, tblProducts.Vis, Now() AS Modified,
CurrentLogin() AS ModifiedBy"
strSQL2 = strSQL2 & " FROM tblProducts"
strSQL2 = strSQL2 & " WHERE (((tblProducts.CostCentre)= 221) AND
((tblProducts.Vis)=True));"

But as soon as I replace 916255 with " & rst!EmployeeNumber & " it stops
working. EmployeeNumber is a field in a recordset and is active when this
query is executed.
Same with the cost centre 221
I can only guess I'm messing up the number of quotes around the variable. By
trial and error some appear to work but the data just isnt being inserted.
Am I doing something else wrong?.
TIA

[Justin Hoffman]

"Damon Grieves" <da****@vfeNOSPAMmail.net> wrote in message
news:42**********************@news.optusnet.com.au ...

Hi I'm having trouble with variables in an INSERT INTO string that I'm
executing in A97 to SQL Server 2k tables.
I can get the SQL command to work using values such as :
strSQL2 = "INSERT INTO tblEmployee "
strSQL2 = strSQL2 & "SELECT tblProducts.CostCentre, tblProducts.Class,
tblProducts.ProductGroup, 9016255 AS EmployID, tblProducts.Description,
tblProducts.Division, tblProducts.BusinessUnit, tblProducts.Category,
tblProducts.EmplDefault AS Allocation, tblProducts.Vis, Now() AS Modified,
CurrentLogin() AS ModifiedBy"
strSQL2 = strSQL2 & " FROM tblProducts"
strSQL2 = strSQL2 & " WHERE (((tblProducts.CostCentre)= 221) AND
((tblProducts.Vis)=True));"

But as soon as I replace 916255 with " & rst!EmployeeNumber & " it stops
working. EmployeeNumber is a field in a recordset and is active when this
query is executed.
Same with the cost centre 221
I can only guess I'm messing up the number of quotes around the variable.
By
trial and error some appear to work but the data just isnt being inserted.
Am I doing something else wrong?.
TIA

I would guess it is to do with the quotes. You need a consistent way to
handle them, but you depending on how you are inserting rows into SQL Server
you will need either single (pass-through queries, etc) or double (odbc)
quotes. Assuming it is odbc just use the function below to add quotes, but
be careful with null values - just go slowly

e.g.
If EmployeeNumber is text (not a real number)
strTemp = Trim(Nz(rst!EmployeeNumber,""))
If Len(strTemp)>0 Then
strSQL=strSQL & AddDoubleQuotes(strTemp)
End if
....
....

Public Function AddDoubleQuotes(strValue) As String

Dim strReturn As String

strReturn = Replace(strValue, """", """""")
strReturn = """" & strReturn & """"

AddDoubleQuotes = strReturn

End Function

[Johnny Meredith]

I don't deal with SQL Server, but in Access when I have a variable in a
SQL statement that's a string, I do this (using a WHERE clause just to
illustrate):

...."WHERE T.Field = " & Chr(34) & value & Chr(34) & .....

(The Chr(34) function call returns a quotation mark.)

Although this approach yields longer SQL strings, I never have any more
quotes-related issues (that is, if I remember to put them in there!)

From you post above, however, I get the idea that the employee ID field

is a number??? If it really is a string, this could be the issue.

Johnny