Hi, I'm using the code below to create an email and then populate it with the results of the query - this works fine.
Note the WHERE part of the query - we'll be revisiting it soon....
Currently the query selects all where the date is within the last five days:
Instead of this users would like to choose the dates used in the query. I've created a form with two text boxes, txtStartDate and txtEndDate and a Submit button (which now fires the email code) so the user selects the dates which are passed to the query.
The WHERE clause now looks like this:
This works fine when I run the query on its own, I get the results expected. However, when I add the new WHERE clause to the email code the email is generated but not populated with the results.
There's no error - just no results of the query.
Please help, I've spent a couple of days on this now.
Thanks
Note the WHERE part of the query - we'll be revisiting it soon....
Code:
Private Sub CmdEmail_Click()
Dim olApp As Object
Dim olItem As Variant
Dim olatt As String
Dim olMailTem As Variant
Dim strSendTo As String
Dim strMsg As String
Dim strTo As String
Dim strcc As String
Dim rst As DAO.Recordset
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim fld As Field
Dim varItem As Variant
Dim strtable As String
Dim rec As DAO.Recordset
Dim strqry As String
strqry = "SELECT tblRTBApps.[CHS No], tblRTBApps.[RTB1], chs_table.ADDRESS, chs_table.PostCode " & _
"FROM chs_table INNER JOIN tblRTBApps ON chs_table.CHS_NO = tblRTBApps.[CHS No] " & _
"WHERE (((tblRTBApps.[RTB1]) Between (Date()-5) And Date())) " & _
"ORDER BY tblRTBApps.[RTB1] DESC;"
strSendTo = ""
strTo = "[EMAIL="xxx@xxx.co.uk"]some email address[/EMAIL]"
strcc = ""
Set olApp = CreateObject("Outlook.application")
Set olItem = olApp.createitem(olMailTem)
olItem.Display
olItem.To = strTo
olItem.cc = strcc
olItem.Body = "Some body text here"
olItem.Subject = "subject text here"
Set db = CurrentDb
Set rec = CurrentDb.OpenRecordset(strqry)
If Not (rec.BOF And rec.EOF) Then
rec.MoveLast
rec.MoveFirst
intCount = rec.RecordCount
For intLoop = 1 To intCount
olItem.Body = olItem.Body & rec("[CHS No]") & " " & rec("[Address]") & " " & rec("PostCode")
rec.MoveNext
Next intLoop
EndCurrently the query selects all where the date is within the last five days:
Code:
WHERE (((tblRTBApps.[RTB1]) Between (Date()-5) And Date()))Instead of this users would like to choose the dates used in the query. I've created a form with two text boxes, txtStartDate and txtEndDate and a Submit button (which now fires the email code) so the user selects the dates which are passed to the query.
The WHERE clause now looks like this:
Code:
WHERE (((tblRTBApps.[RTB1]) Between " & Me.[txtStartDate] & " And " & Me.[txtEndDate] & "This works fine when I run the query on its own, I get the results expected. However, when I add the new WHERE clause to the email code the email is generated but not populated with the results.
There's no error - just no results of the query.
Please help, I've spent a couple of days on this now.
Thanks
