SQL statement works in a query, but not in VBA

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!
 
J

Jim Burke in Novi

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.
 
J

John Spencer

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...
 
R

Ralph

You also have to move to the last record for recordcount to work.

rst.movelast
rst.movefirst
MsgBox rst.RecordCount
 
E

EAB1977

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;"
 
J

John Spencer

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...
 

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

SQL CODE WORKS IN ACCESS BUT NOT IN VBA 3
Works in query, but not in VBA 1
SQL Query works -- just not in VBA 3
Code works in query but not in vba - help 4
Query works, SQL in VBA does not 4
Query within a Query - SQL in VBA 7

Top