Report And Query

[ADMOODY]

Good afternoon,

Good afternoon,

I'm currently new to using Access and VBA. I'm trying to create a form that when you selecting a month (combo box) it should activate the query. I have the SQL statement and the months but I'm having problems getting the connections with the 'Report_Open click statement'. Here is what I have so far:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String

strSql = ""
strSql = strSql & "SELECT dbo_Client.ClientName, dbo_BusinessType.Description, Count(dbo_Client.ClientName) AS [Count]"
strSql = strSql & "FROM (((dbo_Client INNER JOIN dbo_ClientIndicators ON dbo_Client.NationalClientIndicator = dbo_ClientIndicators.NationalClientIndicator) INNER JOIN dbrderRecord ON dbo_Client.ClientID = dbrderRecord.ClientID) INNER JOIN dbo_Product ON dbrderRecord.ProductID = dbo_Product.ProductID) INNER JOIN dbo_BusinessType ON dbo_Product.BusinessType = dbo_BusinessType.BusinessType "
strSql = strSql & "WHERE dbrderRecord.Created between " & Month(Date) - 1 & "/1/" & Year(Date) & " and " & Month(Date) & "/1/" & Year(Date) & " AND dbo_Client.NationalClientIndicator='p' "
strSql = strSql & " GROUP BY dbo_Client.ClientName, dbo_BusinessType.Description;"


Set db = CurrentDb
Set rs = db.OpenRecordset(strSql)
Me.frmClient.Form.Recordset = rs

Please Help!!

 

[CasperTFG]

I would suggest this. Put the following test piece of code into your function. Then copy the output and in Access create a new Query putting in this as the Query SQL. Then see what the results are and where or why it fails. You can easily debug your SQL here and then re-import the changes you needed into your code.
[tt]
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String

strSql = ""
strSql = strSql & "SELECT dbo_Client.ClientName, dbo_BusinessType.Description, Count(dbo_Client.ClientName) AS [Count]"
strSql = strSql & "FROM (((dbo_Client INNER JOIN dbo_ClientIndicators ON dbo_Client.NationalClientIndicator = dbo_ClientIndicators.NationalClientIndicator) INNER JOIN dbrderRecord ON dbo_Client.ClientID = dbrderRecord.ClientID) INNER JOIN dbo_Product ON dbrderRecord.ProductID = dbo_Product.ProductID) INNER JOIN dbo_BusinessType ON dbo_Product.BusinessType = dbo_BusinessType.BusinessType "
strSql = strSql & "WHERE dbrderRecord.Created between " & Month(Date) - 1 & "/1/" & Year(Date) & " and " & Month(Date) & "/1/" & Year(Date) & " AND dbo_Client.NationalClientIndicator='p' "
strSql = strSql & " GROUP BY dbo_Client.ClientName, dbo_BusinessType.Description;"

Debug.Print strSql
Set db = CurrentDb
Set rs = db.OpenRecordset(strSql)
Me.frmClient.Form.Recordset = rs
[/tt]
Craig, mailto:sander@cogeco.ca
"Procrastination is the art of keeping up with yesterday."
I hope my post was helpful!!!