Problem Query in VBA 2

[jpl458]

Using Office 10 and Windows 7.

Have the following routine in the Timer event on a form. When finished it will run every 2 minutes.

Dim GetBalls As Variant
GetBalls = "SELECT dbo_Transaction_Table.Location_Id, Sum(dbo_Transaction_Table.Balls) AS SumOfBalls "
GetBalls = GetBalls + "FROM dbo_Transaction_Table "
GetBalls = GetBalls + "WHERE (((dbo_Transaction_Table.Location_Id) Between ""DW01"" And ""DW40"") AND ((dbo_Transaction_Table.Date_of_Transaction)=Date)) "
GetBalls = GetBalls + "GROUP BY dbo_Transaction_Table.Location_Id "
GetBalls = GetBalls + "ORDER BY dbo_Transaction_Table.Location_Id"


'Set up select query
Dim cnndbo As ADODB.Connection
Set cnndbo = CurrentProject.Connection
Dim rsdbo As New ADODB.Recordset
rsdbo.ActiveConnection = cnndbo
rsdbo.CursorType = adOpenDynamic
'----------------------------------------------------------------------------
'Send query to SQL Server
rsdbpen GetBalls
End Sub

The query compiles but the record set is always empty after execution. I have commented out the SQL and replaced it with a very simple select query and it runs. When I comment out the Where Clause line it compiles but get no data. I put double quotes around the Text fields and took the () off of the Date function -Just want todays date. BTW I link to the table in the On Load event and the link is there and open. I am checking for results in the immediate window. Stared at this thing till I am blue in the face and can't see the problem.

Thanks for your time and help

jpl

 

[PHV]

What about this ?

GetBalls = GetBalls & "WHERE Location_Id Between 'DW01' And 'DW40' AND Date_of_Transaction=getdate() "

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I would try:

Dim GetBalls As Variant
GetBalls = "SELECT Location_Id, Sum(Balls) AS SumOfBalls "
GetBalls = GetBalls & "FROM dbo_Transaction_Table "
GetBalls = GetBalls & "WHERE Location_Id Between 'DW01' And 'DW40' " _
  "AND Date_of_Transaction = #" & format(date,"mm/dd/yyyy") & "# "
GetBalls = GetBalls & "GROUP BY Location_Id "
GetBalls = GetBalls & "ORDER BY Location_Id"
Debug.print GetGalls

Duane
Hook'D on Access
MS Access MVP

 

[jpl458]

Thanks for the quick response guys, but ubfortunately dhookums suggestion wouldn't compile, and PHVs getdate() is an undefined function.

jpl

 

[PHV]

If dbo_Transaction_Table is a linked table in an access db, then replace getdate() with Date().

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Forgot the &

Dim GetBalls As Variant
GetBalls = "SELECT Location_Id, Sum(Balls) AS SumOfBalls "
GetBalls = GetBalls & "FROM dbo_Transaction_Table "
GetBalls = GetBalls & "WHERE Location_Id Between 'DW01' And 'DW40' " [red][b]&[/b][/red] _
  "AND Date_of_Transaction = #" & format(date,"mm/dd/yyyy") & "# "
GetBalls = GetBalls & "GROUP BY Location_Id "
GetBalls = GetBalls & "ORDER BY Location_Id"
Debug.print GetGalls

Duane
Hook'D on Access
MS Access MVP