Proper simple Access VBA SQL syntax

  • Thread starter Thread starter James
  • Start date Start date
J

James

I'm attempting to pass the input of a textbox from an Access form as a
parameter for a SQL procedure, however, I'm missing something very elementary
and I can't find it either through MSDN or Google and would please like some
assistance. I originally thought it was a quotes issue but that doesn't seem
to be the case since I can hardcode any given number in between the quotes
(i.e. "'1017QT'") and the code works fine.

=================================
Private Sub btn_spSQL_MailMerge_Click()
On Error GoTo Err_btn_spSQL_MailMerge_Click

Dim strSQL As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strLeaseNumber As String

strSQL = "EXEC QDBSQL.dbo.spMailMerge " 'note the extra space to
separate the parameter
strSQL2 = "EXEC QDBSQL.dbo.spSQLDataImport"
strSQL3 = "EXEC QDBSQL.dbo.Reporting"
strLeaseNumber = "'Forms!frmLeaseWorksheet.txtReportFilter'"

DoCmd.RunSQL strSQL & strLeaseNumber
DoCmd.RunSQL strSQL2
DoCmd.RunSQL strSQL3
DoCmd.Close

Exit_btn_spSQL_MailMerge_Click:
Exit Sub

Err_btn_spSQL_MailMerge_Click:
MsgBox Err.Description
Resume Exit_btn_spSQL_MailMerge_Click

End Sub
=================================

Now ultimately in MSSQL this is what the above code is supposed to
accomplish to better give you an idea of what I'm trying to accomplish:

EXEC QDBSQL.dbo.spMailMerge "1017QT"
EXEC QDBSQL.dbo.spSQLDataImport
EXEC QDBSQL.dbo.Reporting

Thank you for your assistance!

James
 
James said:
I'm attempting to pass the input of a textbox from an Access form as
a parameter for a SQL procedure, however, I'm missing something very
elementary and I can't find it either through MSDN or Google and
would please like some assistance. I originally thought it was a
quotes issue but that doesn't seem to be the case since I can
hardcode any given number in between the quotes (i.e. "'1017QT'")
and the code works fine.

=================================
Private Sub btn_spSQL_MailMerge_Click()
On Error GoTo Err_btn_spSQL_MailMerge_Click

Dim strSQL As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strLeaseNumber As String

strSQL = "EXEC QDBSQL.dbo.spMailMerge " 'note the extra space to
separate the parameter
strSQL2 = "EXEC QDBSQL.dbo.spSQLDataImport"
strSQL3 = "EXEC QDBSQL.dbo.Reporting"
strLeaseNumber = "'Forms!frmLeaseWorksheet.txtReportFilter'"

DoCmd.RunSQL strSQL & strLeaseNumber
DoCmd.RunSQL strSQL2
DoCmd.RunSQL strSQL3
DoCmd.Close

Exit_btn_spSQL_MailMerge_Click:
Exit Sub

Err_btn_spSQL_MailMerge_Click:
MsgBox Err.Description
Resume Exit_btn_spSQL_MailMerge_Click

End Sub
=================================

Now ultimately in MSSQL this is what the above code is supposed to
accomplish to better give you an idea of what I'm trying to
accomplish:

EXEC QDBSQL.dbo.spMailMerge "1017QT"
EXEC QDBSQL.dbo.spSQLDataImport
EXEC QDBSQL.dbo.Reporting

Thank you for your assistance!

James
Click to expand...

Try with

strLeaseNumber = "'" & Forms!frmLeaseWorksheet.txtReportFilter & "'"

which should enter the value from the form reference, not the form
reference itself.
 
Brian,

Thanks for the response. I tried and neither one worked. Using four gives
you a compile error, "Expected end of statement", and three doesn't work
either. With three it appears to run, but when you look in the resulting
table, nothing is there. If I hard code strLeaseNumber with say "'3040-1'"
as what would have been entered in the form field then it works fine. This
is quite the stumper.

Any other ideas?

James
 
I think you are right about the quotes, but you need to get strLeaseNumber to
include double quotes after it is resolved to its value. Try four sets of
double quotes before & after, like this:

strLeaseNumber = """" & Forms!frmLeaseWorksheet.txtReportFilter & """"
Msgbox strLeaseNumber
Msgbox strSql & strLeaseNumber


The first Msgbox should show this (including the double quotes): "1017QT",
and the second Msgbox should show the complete SQL string, replete with
double quotes.

Try just three pairs of double quotes on each side if that is not correct (I
did not test it).
 
James,

SQL Server uses single quotes to identify a string, and two single quotes to
denote a single quote inside a quoted string constant, for example:
Access SQL Server
"abc" 'abc'
"a'bc" 'a''bc'
"a""bc" 'a"bc'

As you construct a string to execute you will need to wrap the string
parameter values into single quotes and remember to double internal quotes.

Hope this helps
 
Sergey,

Thank you for your response! I had read that in an Access 2007 book and had
reconstructed my variable and it didn't work if I used the forms! method.
However, if I replaced the forms! with the actual hardcoded entry it works
fine. For example, the following code works perfect so I know the quotes are
setup right:

=====================
Private Sub btn_spSQL_MailMerge_Click()
On Error GoTo Err_btn_spSQL_MailMerge_Click

Dim strSQL As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strLeaseNumber As String

strSQL = "EXEC QDBSQL.dbo.spMailMerge " 'note the extra space to
separate the parameter
strSQL2 = "EXEC QDBSQL.dbo.spSQLDataImport"
strSQL3 = "EXEC QDBSQL.dbo.Reporting"
strLeaseNumber = "'1017QT'"

DoCmd.RunSQL strSQL & strLeaseNumber
DoCmd.RunSQL strSQL2
DoCmd.RunSQL strSQL3
DoCmd.Close

Exit_btn_spSQL_MailMerge_Click:
Exit Sub

Err_btn_spSQL_MailMerge_Click:
MsgBox Err.Description
Resume Exit_btn_spSQL_MailMerge_Click

End Sub
=====================

However, if I do it the following way so that I'm using the form field name,
it does not work, even though it is setup the same way. It runs but never
errors out but it is as if the parameter isn't getting passed at all:

=====================
Private Sub btn_spSQL_MailMerge_Click()
On Error GoTo Err_btn_spSQL_MailMerge_Click

Dim strSQL As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strLeaseNumber As String

strSQL = "EXEC QDBSQL.dbo.spMailMerge " 'note the extra space to
separate the parameter
strSQL2 = "EXEC QDBSQL.dbo.spSQLDataImport"
strSQL3 = "EXEC QDBSQL.dbo.Reporting"
strLeaseNumber = "'Forms!frmLeaseWorksheet.txtReportFilter'"

DoCmd.RunSQL strSQL & strLeaseNumber
DoCmd.RunSQL strSQL2
DoCmd.RunSQL strSQL3
DoCmd.Close

Exit_btn_spSQL_MailMerge_Click:
Exit Sub

Err_btn_spSQL_MailMerge_Click:
MsgBox Err.Description
Resume Exit_btn_spSQL_MailMerge_Click

End Sub
=====================

In addition, I've also tried setting the variable to (double quote, single,
double):

strLeaseNumber = "'"Forms!frmLeaseWorksheet.txtReportFilter"'"

and that doesn't work either.

I'm sure I'm doing something very minor wrong but I'm just not seeing it. I
really appreciate your time and any other ideas you might have.

Thanks!

James
 
James,

If you want your code to read a control value on a form, then omit the
quotes surrounding the reference to control and add them into the string, for
example:

strLeaseNumber = prepareForSqlServer(Forms!frmLeaseWorksheet.txtReportFilter)

then declare the following function in your code:
Function prepareForSqlServer(value As String) As String
prepareForSqlServer = "'" & Replace(value, "'", "''") & "'"
End Function

Hope this helps
 
Sergey,

Thank you for your follow up post. I don't fully understand what you did
but it works wonderfully! Thank you VERY, VERY much! I'll take the time to
learn what it is you've got going in your small amount of code as soon as I
get a chance as I would have never come up with that, wow...thank you again!

James
 
Back
Top