Problem with passing values to an append query 1

[jordanking]

Hello

I am having trouble passing a string variable to an append query based on values.

lCusID = 529
strCusNm = "*BD- Serink, Rosemarie"
dCrntTtl = 0
dAgdTtl = 0
dAmtTtl = 0
strAppend = "INSERT INTO tblSDBCust ( lID, strName, curCurrent, curAged, curTotal ) VALUES ( " & lCusID & ", " & strCusNm & ", " & dCrntTtl & ", " & dAgdTtl & ", " & dAmtTtl & " );"

DoCmd.RunSQL strAppend

if I debug.print the value of the string created it returns:

INSERT INTO tblSDBCust ( lID, strName, curCurrent, curAged, curTotal ) VALUES ( 529, *BD- Serink, Rosemarie, 0, 0, 0 );

I get the following error:

"Syntax Error, (missing Operator) in Expression '*BD- Serink'"

I need to be able to pass the value of the string including commas in a way that does not effect the execution of the query. If I make all the variables numeric then it works. But it breaks if the variables are strings(with or wihtout commas).

Any suggestions greatly appreciated.

Thank You

JK

 

[PHV]

strAppend = "INSERT INTO tblSDBCust ( lID, strName, curCurrent, curAged, curTotal ) VALUES ( " & lCusID & ", [!]'[/!]" & strCusNm & "[!]'[/!], " & dCrntTtl & ", " & dAgdTtl & ", " & dAmtTtl & " );"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jordanking]

Thanks for the quick reply,

I tried the changes you suggested and teh following error appeard:

Run-Time error '30753:

Syntax error in string in query expression "*BD- Serink, Rosemarie'.

 

[PHV]

Could you please post your actual code ?
Seems there is a typo ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jordanking]

I am getting the values from an external database. The for loop is where I use the append query as follows:

            For i = 1 To 1 'lNumRecs
                iRet = wSDBFetchRecord(iDBLink, iTBLink, pRecord, 1, iRecRtn) ' a function that returns the next record in the previously established recordset.  The recordset is returned as byte data
                'Debug.Print "iRet = " & iRet
                'Debug.Print "iRecRtn = " & iRecRtn
                Call CopyMemory(lCusID, pRecord.Data(0), 4)
                Call CopyMemory(abytTemp(0), pRecord.Data(4), 35)
                For k = 0 To 34
                    strTemp = Chr(CLng(abytTemp(k)))
                    strCusNm = strCusNm & strTemp
                Next
                'Debug.Print strCusNm
                Call CustAgd(iDBLink, lCusID, dCrntTtl, dAgdTtl, dAmtTtl)
                dCrntTtl = Math.Round(dCrntTtl, 2)
                dAgdTtl = Math.Round(dAgdTtl, 2)
                dAmtTtl = Math.Round(dAmtTtl, 2)
                'strCusNm = "NAME"
                strAppend = "INSERT INTO tblSDBCust ( lID, strName, curCurrent, curAged, curTotal ) VALUES ( " & lCusID & ", '" & strCusNm & "', " & dCrntTtl & ", " & dAgdTtl & ", " & dAmtTtl & " );"
                Debug.Print strAppend
                DoCmd.RunSQL strAppend
                Me!lblTest1.Caption = "Customer Name: " & strCusNm
                Me!lblTest2.Caption = "Customer ID#: " & lCusID
                Me!lblTest3.Caption = "Current Total = " & dCrntTtl
                Me!lblTest4.Caption = "Aged Overdue = " & dAgdTtl
                Me!lblTest5.Caption = "Total Owing = " & dAmtTtl
                Me.Repaint
                strCusNm = ""
                dCrntTtl = 0
                dAgdTtl = 0
                dAmtTtl = 0
            Next

 

[PHV]

What is the output of this instruction ?
Debug.Print strAppend

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jordanking]

the return is as follows when I made the changes you suggested:

INSERT INTO tblSDBCust ( lID, strName, curCurrent, curAged, curTotal ) VALUES ( 529, '*BD- Serink, Rosemarie', 0, 0, 0 );

when the variable is not nested with single quotes the return is as follows:

INSERT INTO tblSDBCust ( lID, strName, curCurrent, curAged, curTotal ) VALUES ( 529, *BD- Serink, Rosemarie, 0, 0, 0 );

On a side note, when i do not define the value of strCusNm I am prompted for a parameter by access. I can type in the string exactly as I want it and the append query will work, but the sql string shows the "parameter name" strCusNm instead of the actual string it represents. See below:

INSERT INTO tblSDBCust ( lID, strName, curCurrent, curAged, curTotal ) VALUES ( 529, strCusNm, 0, 0, 0 );

I don't know if this helps.

 

[PHV]

I wonder '*BD- Serink, Rosemarie' is 22 char long and not 35 ?
What happens if you change the loop like this:
For k = 0 To 34
strTemp = Chr(CLng(abytTemp(k)))
If strTemp < " " Then Exit For
strCusNm = strCusNm & strTemp
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jordanking]

Thanks for your time,

I tried that but it does not change the error thrown by the databse. I even insert my own value just previous to the sqlString construction and it still breaks. Is there a way to associate a parameter with a variable through vba. Like the parameters that can be set in the database's query design window?

 

[jordanking]

one last note:

I passed the following string to the sql view of the access database and it worked:

INSERT INTO tblSDBCust ( lID, strName, curCurrent, curAged, curTotal ) SELECT 529 AS Expr1, '*BD- Serink, Rosemarie ' AS Expr2, 0 AS Expr3, 0 AS Expr4, 0 AS Expr5;

When I past this exact string into a module, it works as well. But when I try to construct the string it fails. But the output string matches exactly the string above, I have no idea what is going wrong.