R
russellhq
I'm trying to make an SQL statement on the fly using form variables to
use in a query for a report. I've copied the SQL statement from the
query and assigned it to a string in VBA using what I think is the
correct format but I keep getting the error in the subject. I've gone
through the code many times and can't see the issue.
The only problem I can see is the SQL string is very long. It has to
account for 20 tables and 115 fields.
The code below is what I've used except for the SQL string, as its
quite long.
Private Sub cmdCert_Click()
Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim rptCert As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryTest")
strSQL = "SELECT tblWPS.QualifyingRange, ...... ORDER BY
tblWelderQualification.[Test Number];"
'"WHERE tblWelderQualification.[Test Number]=" & Chr(34) & [Test
Number] & Chr(34) & _
qdf.SQL = strSQL
DoCmd.Close acReport, "rptTest" 'Closes report if allready open
DoCmd.OpenReport "rptTest", acViewPreview 'Opens report
End Sub
use in a query for a report. I've copied the SQL statement from the
query and assigned it to a string in VBA using what I think is the
correct format but I keep getting the error in the subject. I've gone
through the code many times and can't see the issue.
The only problem I can see is the SQL string is very long. It has to
account for 20 tables and 115 fields.
The code below is what I've used except for the SQL string, as its
quite long.
Private Sub cmdCert_Click()
Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim rptCert As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryTest")
strSQL = "SELECT tblWPS.QualifyingRange, ...... ORDER BY
tblWelderQualification.[Test Number];"
'"WHERE tblWelderQualification.[Test Number]=" & Chr(34) & [Test
Number] & Chr(34) & _
qdf.SQL = strSQL
DoCmd.Close acReport, "rptTest" 'Closes report if allready open
DoCmd.OpenReport "rptTest", acViewPreview 'Opens report
End Sub