SQL and Variable

[Guest]

I'm having a problem with SQL Code when I try to use a variable. I'm calling
the function callconcatquote from a macro and a box appears asking me to
enter the data for variable concatquoteheader (much like a parameter).

The problem appears to be that the variable isn't being passed to SQL
correctly ... when I replace concatquoteheader in the SQL code with a literal
2 (number two), the field in the table is updated correctly.

Here's the code (note that frmSalesJob]![quoteheader] is a text box and that
tblSalesJobMastQuotes.quoteheader is type memo):


++++++++++++++

Public Sub ConcatenateHeader()

Dim concatquoteheader As String

Dim header1 As String
Dim header2 As String


header1 = Mid(Forms![frmSalesJob]![quoteheader], 1, 250)
header2 = Mid(Forms![frmSalesJob]![quoteheader], 251, 250)

concatquoteheader = header1 & header2

DoCmd.RunSQL "UPDATE tblSalesJobMastQuotes SET
tblSalesJobMastQuotes.quoteheader = concatquoteheader WHERE
(((tblSalesJobMastQuotes.documentnumber)=[Forms]![frmSalesJob]![salesnumber]
& [Forms]![frmSalesJob]![documentversion]))"

End Sub

++++++++++++++

Public Function callconcatquote()

Call ConcatenateHeader

End Function

++++++++++++++

Any help that you can provide is greatly appreciated. Thanks in advance.

Gary

 

[MacDermott]

DoCmd.RunSQL "UPDATE tblSalesJobMastQuotes SET
tblSalesJobMastQuotes.quoteheader = " & concatquoteheader & " WHERE
(((tblSalesJobMastQuotes.documentnumber)=[Forms]![frmSalesJob]![salesnumber]
& [Forms]![frmSalesJob]![documentversion]))"

I'm having a problem with SQL Code when I try to use a variable. I'm calling
the function callconcatquote from a macro and a box appears asking me to
enter the data for variable concatquoteheader (much like a parameter).

The problem appears to be that the variable isn't being passed to SQL
correctly ... when I replace concatquoteheader in the SQL code with a literal
2 (number two), the field in the table is updated correctly.

Here's the code (note that frmSalesJob]![quoteheader] is a text box and that
tblSalesJobMastQuotes.quoteheader is type memo):


++++++++++++++

Public Sub ConcatenateHeader()

Dim concatquoteheader As String

Dim header1 As String
Dim header2 As String


header1 = Mid(Forms![frmSalesJob]![quoteheader], 1, 250)
header2 = Mid(Forms![frmSalesJob]![quoteheader], 251, 250)

concatquoteheader = header1 & header2

DoCmd.RunSQL "UPDATE tblSalesJobMastQuotes SET
tblSalesJobMastQuotes.quoteheader = concatquoteheader WHERE
(((tblSalesJobMastQuotes.documentnumber)=[Forms]![frmSalesJob]![salesnumber]
& [Forms]![frmSalesJob]![documentversion]))"

End Sub

++++++++++++++

Public Function callconcatquote()

Call ConcatenateHeader

End Function

++++++++++++++

Any help that you can provide is greatly appreciated. Thanks in advance.

Gary

 

[Van T. Dinh]

Slightly different:

DoCmd.RunSQL "UPDATE tblSalesJobMastQuotes SET
tblSalesJobMastQuotes.quoteheader = " & Chr$(34) & concatquoteheader &
Chr$(34) &
" WHERE
(((tblSalesJobMastQuotes.documentnumber)=[Forms]![frmSalesJob]![salesnumber]
& [Forms]![frmSalesJob]![documentversion]))"

--
HTH
Van T. Dinh
MVP (Access)

DoCmd.RunSQL "UPDATE tblSalesJobMastQuotes SET
tblSalesJobMastQuotes.quoteheader = " & concatquoteheader & " WHERE
(((tblSalesJobMastQuotes.documentnumber)=[Forms]![frmSalesJob]![salesnumber]
& [Forms]![frmSalesJob]![documentversion]))"

I'm having a problem with SQL Code when I try to use a variable. I'm calling
the function callconcatquote from a macro and a box appears asking me to
enter the data for variable concatquoteheader (much like a parameter).

The problem appears to be that the variable isn't being passed to SQL
correctly ... when I replace concatquoteheader in the SQL code with a literal
2 (number two), the field in the table is updated correctly.

Here's the code (note that frmSalesJob]![quoteheader] is a text box and that
tblSalesJobMastQuotes.quoteheader is type memo):


++++++++++++++

Public Sub ConcatenateHeader()

Dim concatquoteheader As String

Dim header1 As String
Dim header2 As String


header1 = Mid(Forms![frmSalesJob]![quoteheader], 1, 250)
header2 = Mid(Forms![frmSalesJob]![quoteheader], 251, 250)

concatquoteheader = header1 & header2

DoCmd.RunSQL "UPDATE tblSalesJobMastQuotes SET
tblSalesJobMastQuotes.quoteheader = concatquoteheader WHERE

(((tblSalesJobMastQuotes.documentnumber)=[Forms]![frmSalesJob]![salesnumber]

& [Forms]![frmSalesJob]![documentversion]))"

End Sub

++++++++++++++

Public Function callconcatquote()

Call ConcatenateHeader

End Function

++++++++++++++

Any help that you can provide is greatly appreciated. Thanks in advance.

Gary