Variable and SQL UPDATE Query?

G

Guest

I'm trying to pass a variable to a SQL query and am not having success. I'm
calling the function from a macro with RUNCODE.

Note that when I replace the variable "concatquoteheader" with the number 2
in the SQL statement, it runs correctly. When I use the variable, a message
appears asking me to input text for "concatquoteheader" (much like a
parameter). Also, if I set concatquoteheader variable = "test", it also
fails.

One other note ... the control "quoteheader" is a text box on a form and the
field tblSalesJobMastQuotes.quoteheader is type memo.

Here's the module:

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 is greatly appreciated. Thanks in advance.

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 trying to pass a variable to a SQL query and am not having success. I'm
calling the function from a macro with RUNCODE.

Note that when I replace the variable "concatquoteheader" with the number 2
in the SQL statement, it runs correctly. When I use the variable, a message
appears asking me to input text for "concatquoteheader" (much like a
parameter). Also, if I set concatquoteheader variable = "test", it also
fails.

One other note ... the control "quoteheader" is a text box on a form and the
field tblSalesJobMastQuotes.quoteheader is type memo.

Here's the module:

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 is greatly appreciated. Thanks in advance.

Gary
Click to expand...
 
G

Guest

Thanks, that did it!!!

Ofer said:
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 trying to pass a variable to a SQL query and am not having success. I'm
calling the function from a macro with RUNCODE.

Note that when I replace the variable "concatquoteheader" with the number 2
in the SQL statement, it runs correctly. When I use the variable, a message
appears asking me to input text for "concatquoteheader" (much like a
parameter). Also, if I set concatquoteheader variable = "test", it also
fails.

One other note ... the control "quoteheader" is a text box on a form and the
field tblSalesJobMastQuotes.quoteheader is type memo.

Here's the module:

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 is greatly appreciated. Thanks in advance.

Gary
Click to expand...
Click to expand...
 
G

Guest

Any time. have a nice weekend

G said:
Thanks, that did it!!!

Ofer said:
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 trying to pass a variable to a SQL query and am not having success. I'm
calling the function from a macro with RUNCODE.

Note that when I replace the variable "concatquoteheader" with the number 2
in the SQL statement, it runs correctly. When I use the variable, a message
appears asking me to input text for "concatquoteheader" (much like a
parameter). Also, if I set concatquoteheader variable = "test", it also
fails.

One other note ... the control "quoteheader" is a text box on a form and the
field tblSalesJobMastQuotes.quoteheader is type memo.

Here's the module:

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 is greatly appreciated. Thanks in advance.

Gary
Click to expand...
Click to expand...
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? 1
SQL and Variable 2
passing string variable into sql query 2
SQL Update Query and variables 8
Incorporating a public variable in an Update Query that updates atable 14
SQL Query and Parameters 3
Garbage in a string variable loaded from a field 2
SQL string in VBA 4

Top