SQL statement works in a query, but not in VBA

  • Thread starter Thread starter EAB1977
  • Start date Start date
E

EAB1977

Hello everyone,

This SQL statement below is confusing the heck out of me.

I built my query from the query builder in Access(97). I then went to
the SQL view, copied it, and pasted this into a VBA module. It returns
records in the query, but returns zero records when I try to access
this from VBA code. I have tried using just the query, but that gives
me a error.

Here's the code I have so far:

Private Sub cmdExport_Click()
Dim objExcel As Object, dbs As DAO.Database, rst As DAO.Recordset

On Error GoTo cmdExport_ErrHandler

Set objExcel = CreateObject("Excel.Application")
Set dbs = CurrentDb
With objExcel
.ScreenUpdating = True
.Visible = True
.DisplayAlerts = True
.Workbooks.Open "J:\QA\Database\CQAAnalysis\Dev 6-3-2008\CQA
Analysis Test Time Report.xls"
.Range("A2").select

Set rst = dbs.OpenRecordset("SELECT Plant.PlantName,
AnalysisTestGroup.TestID, qryAnalysisCount.Sets," _
& " AnalysisTestGroup.StartDate,
AnalysisTestGroup.StartTime, AnalysisTestGroup.EndDate," _
& " AnalysisTestGroup.EndTime, tblEmployee.UserName,
Sum(AnalysisTestGroupTime.Time) AS TestTime" _
& " FROM (tblEmployee INNER JOIN (qryAnalysisCount INNER
JOIN (Plant INNER JOIN AnalysisTestGroup ON" _
& " Plant.PlantCode = AnalysisTestGroup.PlantCode) ON
qryAnalysisCount.TestID = AnalysisTestGroup.TestID)" _
& " ON tblEmployee.EmployeeID =
AnalysisTestGroup.TestPerson) INNER JOIN AnalysisTestGroupTime ON" _
& " AnalysisTestGroup.TestID =
AnalysisTestGroupTime.TestID" _
& " WHERE (((AnalysisTestGroup.EndDate) Is Not Null) AND
((AnalysisTestGroup.StartDate)>=" & [Forms]![EricsForm]![txtStartDate]
& ") AND ((AnalysisTestGroup.EndDate)<=" & [Forms]![EricsForm]!
[txtEndDate] & "))" _
& " GROUP BY Plant.PlantName, AnalysisTestGroup.TestID,
qryAnalysisCount.Sets, AnalysisTestGroup.StartDate," _
& " AnalysisTestGroup.StartTime,
AnalysisTestGroup.EndDate, AnalysisTestGroup.EndTime," _
& " tblEmployee.UserName ORDER BY tblEmployee.UserName,
AnalysisTestGroup.StartDate, AnalysisTestGroup.EndDate")

MsgBox rst.RecordCount


End With

Exit Sub

cmdExport_ErrHandler:
Call LogError(Err.Number, Err.Description, Me.FormName, , True)
End Sub

Any help would be appreciated. Thanks!
 
When you specify date values in an SQL string in VBA you need to have '#"
around the date. So you need to add a # before and after the date values that
are coming from your form. When you use a date value from a form as criteria
in a 'stored' query you don't need to do that. Here's a simple example:

SQLText = "SELECT field1 " & _
"FROM table1 " & _
"WHERE tblDateValue >= #" & Forms!Form1!txtStartDate & "# AND "
& _
"tblDateValue <= #" & Forms!Form1!txtEndDate & "#"

That's probably what's causing your error.
 
You need to include the date delimiter (#) around your references to controls
containing dates

....
& " WHERE (((AnalysisTestGroup.EndDate) Is Not Null) AND
((AnalysisTestGroup.StartDate)>=#" & [Forms]![EricsForm]![txtStartDate]
& "#) AND ((AnalysisTestGroup.EndDate)<=#" & [Forms]![EricsForm]!
[txtEndDate] & "#))" _

Even safer is to use
Dim sFormat as String
sFormat = "#yyyy/mm/dd#"

& " WHERE (((AnalysisTestGroup.EndDate) Is Not Null) AND
((AnalysisTestGroup.StartDate)>=" & _
Format([Forms]![EricsForm]![txtStartDate],sFormat) & _
") AND ((AnalysisTestGroup.EndDate)<=" & _
Format([Forms]![EricsForm]![txtEndDate],sFormat) & "))" _



....
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Hello everyone,

This SQL statement below is confusing the heck out of me.

I built my query from the query builder in Access(97). I then went to
the SQL view, copied it, and pasted this into a VBA module. It returns
records in the query, but returns zero records when I try to access
this from VBA code. I have tried using just the query, but that gives
me a error.

Here's the code I have so far:

Private Sub cmdExport_Click()
Dim objExcel As Object, dbs As DAO.Database, rst As DAO.Recordset

On Error GoTo cmdExport_ErrHandler

Set objExcel = CreateObject("Excel.Application")
Set dbs = CurrentDb
With objExcel
.ScreenUpdating = True
.Visible = True
.DisplayAlerts = True
.Workbooks.Open "J:\QA\Database\CQAAnalysis\Dev 6-3-2008\CQA
Analysis Test Time Report.xls"
.Range("A2").select

Set rst = dbs.OpenRecordset("SELECT Plant.PlantName,
AnalysisTestGroup.TestID, qryAnalysisCount.Sets," _
& " AnalysisTestGroup.StartDate,
AnalysisTestGroup.StartTime, AnalysisTestGroup.EndDate," _
& " AnalysisTestGroup.EndTime, tblEmployee.UserName,
Sum(AnalysisTestGroupTime.Time) AS TestTime" _
& " FROM (tblEmployee INNER JOIN (qryAnalysisCount INNER
JOIN (Plant INNER JOIN AnalysisTestGroup ON" _
& " Plant.PlantCode = AnalysisTestGroup.PlantCode) ON
qryAnalysisCount.TestID = AnalysisTestGroup.TestID)" _
& " ON tblEmployee.EmployeeID =
AnalysisTestGroup.TestPerson) INNER JOIN AnalysisTestGroupTime ON" _
& " AnalysisTestGroup.TestID =
AnalysisTestGroupTime.TestID" _
& " WHERE (((AnalysisTestGroup.EndDate) Is Not Null) AND
((AnalysisTestGroup.StartDate)>=" & [Forms]![EricsForm]![txtStartDate]
& ") AND ((AnalysisTestGroup.EndDate)<=" & [Forms]![EricsForm]!
[txtEndDate] & "))" _
& " GROUP BY Plant.PlantName, AnalysisTestGroup.TestID,
qryAnalysisCount.Sets, AnalysisTestGroup.StartDate," _
& " AnalysisTestGroup.StartTime,
AnalysisTestGroup.EndDate, AnalysisTestGroup.EndTime," _
& " tblEmployee.UserName ORDER BY tblEmployee.UserName,
AnalysisTestGroup.StartDate, AnalysisTestGroup.EndDate")

MsgBox rst.RecordCount


End With

Exit Sub

cmdExport_ErrHandler:
Call LogError(Err.Number, Err.Description, Me.FormName, , True)
End Sub

Any help would be appreciated. Thanks!
Click to expand...
 
You also have to move to the last record for recordcount to work.

rst.movelast
rst.movefirst
MsgBox rst.RecordCount
 
Got one more, please. BTW, the # signs worked for the first one...

Set rst = dbs.OpenRecordset("SELECT Plant.PlantName,
AnalysisTestGroup.TestID, qryAnalysisCount.Sets," _
& " AnalysisTestGroup.StartDate,
AnalysisTestGroup.StartTime, AnalysisTestGroup.EndDate," _
& " AnalysisTestGroup.EndTime, tblEmployee.UserName,
Sum(AnalysisTestGroupTime.Time) AS TestTime" _
& " FROM (tblEmployee INNER JOIN (qryAnalysisCount INNER
JOIN (Plant INNER JOIN AnalysisTestGroup ON" _
& " Plant.PlantCode = AnalysisTestGroup.PlantCode) ON
qryAnalysisCount.TestID = AnalysisTestGroup.TestID)" _
& " ON tblEmployee.EmployeeID =
AnalysisTestGroup.TestPerson) INNER JOIN AnalysisTestGroupTime ON" _
& " AnalysisTestGroup.TestID =
AnalysisTestGroupTime.TestID" _
& " WHERE (((AnalysisTestGroup.StartDate)>=#" & [Forms]!
[frmAHCycle]![txtStartDate] & "#) AND" _
& " ((AnalysisTestGroup.EndDate) Is Not Null And
(AnalysisTestGroup.EndDate)<=#" & [Forms]![frmAHCycle]![txtEndDate] &
"#)" _
& " AND ((tblEmployee.EmployeeID) In (SELECT
tblEmployee.EmployeeID FROM tblEmployee" _
& " WHERE tblEmployee.UserName=" & [Forms]![frmAHCycle]!
[cboTech] & ");))) GROUP BY Plant.PlantName," _
& " AnalysisTestGroup.TestID, qryAnalysisCount.Sets,
AnalysisTestGroup.StartDate, AnalysisTestGroup.StartTime," _
& " AnalysisTestGroup.EndDate, AnalysisTestGroup.EndTime,
tblEmployee.UserName ORDER BY tblEmployee.UserName," _
& " AnalysisTestGroup.StartDate;"
 
The first suggestion is to assign the sql string to a string(text) variable.

That way you can print out the resulting SQL string (Debug.Print strSQL) and
examine it. If necessary, you can copy and paste the resulting string into a
query and try to run it there. You will get better error messages and
possibly the cursor pointing to the location where it encountered the error.

I think there are two errors in your where clause. See below. I didn't
attempt to check to make sure the unneeded parentheses correctly match up.


Dim strSQL as String

strSQL = "SELECT Plant.PlantName,
AnalysisTestGroup.TestID, qryAnalysisCount.Sets," _
& " AnalysisTestGroup.StartDate,
AnalysisTestGroup.StartTime, AnalysisTestGroup.EndDate," _
& " AnalysisTestGroup.EndTime, tblEmployee.UserName,
Sum(AnalysisTestGroupTime.Time) AS TestTime" _
& " FROM (tblEmployee INNER JOIN (qryAnalysisCount INNER
JOIN (Plant INNER JOIN AnalysisTestGroup ON" _
& " Plant.PlantCode = AnalysisTestGroup.PlantCode) ON
qryAnalysisCount.TestID = AnalysisTestGroup.TestID)" _
& " ON tblEmployee.EmployeeID =
AnalysisTestGroup.TestPerson) INNER JOIN AnalysisTestGroupTime ON" _
& " AnalysisTestGroup.TestID =
AnalysisTestGroupTime.TestID" _
& " WHERE (((AnalysisTestGroup.StartDate)>=#" & [Forms]!
[frmAHCycle]![txtStartDate] & "#) AND" _
& " ((AnalysisTestGroup.EndDate) Is Not Null And
(AnalysisTestGroup.EndDate)<=#" & [Forms]![frmAHCycle]![txtEndDate] &
"#)" _
& " AND ((tblEmployee.EmployeeID) In (SELECT
tblEmployee.EmployeeID FROM tblEmployee" _

==============================================================================
==============================================================================
Add quote marks around the value you are checking for userName and remove the
semi-colon - only one semi-colon allowed in the query and that is at the very end.

& " WHERE tblEmployee.UserName=""" & [Forms]![frmAHCycle]![cboTech] & """"))))
GROUP BY Plant.PlantName," _
==============================================================================
==============================================================================


& " AnalysisTestGroup.TestID, qryAnalysisCount.Sets,
AnalysisTestGroup.StartDate, AnalysisTestGroup.StartTime," _
& " AnalysisTestGroup.EndDate, AnalysisTestGroup.EndTime,
tblEmployee.UserName ORDER BY tblEmployee.UserName," _
& " AnalysisTestGroup.StartDate;"

Debug.Print StrSQL

Set rst = dbs.OpenRecordset(strSQL)

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Got one more, please. BTW, the # signs worked for the first one...

Set rst = dbs.OpenRecordset("SELECT Plant.PlantName,
AnalysisTestGroup.TestID, qryAnalysisCount.Sets," _
& " AnalysisTestGroup.StartDate,
AnalysisTestGroup.StartTime, AnalysisTestGroup.EndDate," _
& " AnalysisTestGroup.EndTime, tblEmployee.UserName,
Sum(AnalysisTestGroupTime.Time) AS TestTime" _
& " FROM (tblEmployee INNER JOIN (qryAnalysisCount INNER
JOIN (Plant INNER JOIN AnalysisTestGroup ON" _
& " Plant.PlantCode = AnalysisTestGroup.PlantCode) ON
qryAnalysisCount.TestID = AnalysisTestGroup.TestID)" _
& " ON tblEmployee.EmployeeID =
AnalysisTestGroup.TestPerson) INNER JOIN AnalysisTestGroupTime ON" _
& " AnalysisTestGroup.TestID =
AnalysisTestGroupTime.TestID" _
& " WHERE (((AnalysisTestGroup.StartDate)>=#" & [Forms]!
[frmAHCycle]![txtStartDate] & "#) AND" _
& " ((AnalysisTestGroup.EndDate) Is Not Null And
(AnalysisTestGroup.EndDate)<=#" & [Forms]![frmAHCycle]![txtEndDate] &
"#)" _
& " AND ((tblEmployee.EmployeeID) In (SELECT
tblEmployee.EmployeeID FROM tblEmployee" _
& " WHERE tblEmployee.UserName=" & [Forms]![frmAHCycle]!
[cboTech] & ");))) GROUP BY Plant.PlantName," _
& " AnalysisTestGroup.TestID, qryAnalysisCount.Sets,
AnalysisTestGroup.StartDate, AnalysisTestGroup.StartTime," _
& " AnalysisTestGroup.EndDate, AnalysisTestGroup.EndTime,
tblEmployee.UserName ORDER BY tblEmployee.UserName," _
& " AnalysisTestGroup.StartDate;"
Click to expand...
 
Back
Top