Variable and SQL UPDATE Query?

G

Guest

I'm having difficulty with SQL code in a module. I am trying to use the
variable "concatquoteheader" in the SQL code, but the SQL code doesn't appear
to accept it. When I call the function "callconcatquote" from a macro, a box
appears and asks to input "concatquoteheader" (much like a parameter).

I replaced "concatquoteheader" with the literal "2" and the SQL code works
fine (updates a field in a table).

Here's the code (Forms![frmSalesJob]![quoteheader] is a form text box and
tblSalesJobMastQuotes.quoteheader is a memo field in a table):

Option Compare Database

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

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 welcome. Thanks in advance.

Regards,

Gary
 
G

Guest

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

You need to take the variable concatquoteheader out of the SQL string.


G said:
I'm having difficulty with SQL code in a module. I am trying to use the
variable "concatquoteheader" in the SQL code, but the SQL code doesn't appear
to accept it. When I call the function "callconcatquote" from a macro, a box
appears and asks to input "concatquoteheader" (much like a parameter).

I replaced "concatquoteheader" with the literal "2" and the SQL code works
fine (updates a field in a table).

Here's the code (Forms![frmSalesJob]![quoteheader] is a form text box and
tblSalesJobMastQuotes.quoteheader is a memo field in a table):

Option Compare Database

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

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 welcome. Thanks in advance.

Regards,

Gary
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Variable and SQL UPDATE Query? 3
SQL and Variable 2
Help with If Statement 3
SQL string in VBA 4
append SQL no records 8
Trouble with SQL String in VBA 3
Programming Loop with SQL Update Query 7
SQL Update Query and variables 8

Top