SQL statement returns no values (and it should)!

[Mmmel]

Hello!

After a really looong time, I finally (I think) have my SQL statement working (line 28 below). I'm trying to select all records from a table (Student_Information) where a particular date field (Received_Date) lies within a particular date range (which the user specifies in a form). I would simply use a query and then load the query, but you cannot as the query includes parameters (from the form).

Here's my code:

Private Sub Command26_Click()
Dim strEMail As String
Dim oOutlook As Object
Dim oMail As Object
Dim strAddr As String
Dim strBody As String
Dim MyDB As DAO.Database
Dim rstEMail As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Parameter
 
Dim strAttach1 As String
Dim strAttach2 As String
 
Dim strSQL
Dim strStart As String
Dim strEnd As String
Dim strRecDate As String
 
Set oOutlook = CreateObject("Outlook.Application")
Set oMail = oOutlook.CreateItem(0)
 
strStart = [Forms]![Admin_Emails]![rec_startdate]
strEnd = [Forms]![Admin_Emails]![rec_enddate]
 
MsgBox strStart & " " & strEnd & " " & strRecDate
 
strSQL = "SELECT * FROM Student_Information WHERE (Received_Date) between " & strStart & " And " & strEnd
 
MsgBox Received_Date
 
 
strAttach1 = Me![path] & "\Course Selection Form.pdf"
strAttach2 = Me![path] & "\Term Timetables.pdf"
 
'Retrieve all E-Mail Addressess in qryCurrent1stContact
Set MyDB = CurrentDb
Set rstEMail = MyDB.OpenRecordset(strSQL)
 
MsgBox Received_Date
 
On Error GoTo localerror
 
    DoCmd.Save
 
With rstEMail
  Do While Not .EOF
    'Build the Recipients String
    strEMail = strEMail & ![E-mail] & ";"
     .MoveNext
  Loop
End With
 
'--------------------------------------------------
'Set what will be in the body of the email
 strBody = DLookup("EmailBody", "tblEmailBodies", "[ID] = 7")
 
With oMail
  .To = "[e-mail]"
  .Bcc = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  .Body = strBody
  .Importance = 2
  .Subject = "Important Information From us - Please Read!"
  .Attachments.Add strAttach1 And strAttach2
  .Display
End With
 
 
 
Set oMail = Nothing
Set oOutlook = Nothing
 
rstEMail.Close
Set rstEMail = Nothing
 
GoTo localexit
 
localerror:
MsgBox "There are no records in the date range you have selected.", vbCritical
 
localexit:
End Sub
 

After finally solving all of my syntax errors, it at least compiles. However, it does not load any of the records! The msgbox displays nothing for Received_Date (lines 30 and 40).

What am I doing wrong? Any insights would be spectacular. OR - if you can think of a better way to do this, I'm all ears. Thanks!

Melody

[NeoPa]

Assuming the controls referenced ([rec_startdate] & [rec_enddate]) are on the same form as the CommandButton which triggers this code then your references to these controls should be simplified to Me.rec_StartDate & Me.rec_EndDate respectively. Also, date literals are special in SQL. See Literal DateTimes and Their Delimiters (#).

You would need something like :

With Me
    strSQL = "SELECT * " & _
             "FROM   [Student_Information] " & _
             "WHERE ([Received_Date] Between #%S# And #%E#)"
    strSQL = Replace(strSQL, "%S", Format(CDate(.rec_StartDate), "m\/d\/yyyy"))
    strSQL = Replace(strSQL, "%E", Format(CDate(.rec_EndDate), "m\/d\/yyyy"))
End With

[NeoPa]

I just looked at your code more closely Melody. Clearly you don't have Option Explicit set if it's compiling without errors as both lines #30 & #40 should fail, as they refer to non-existent variables. If it's a field you're trying to display then it must be done as a property of a recordset. Line #30 is before any recordset is even opened. Line #40 is after that, but doesn't refer to the field.

[Mmmel]

Hi again! You take good care of me, NeoPa! : )

Nice call on the Option Explicit. Found that right away. I shall use it from now on.

I read about and implemented the code above. Using a debug.print I get:

SELECT * FROM [Student_Information] WHERE ([Received_Date] Between #2/26/2012# And #3/19/2012#)

for my SQL statement. Does that look alright? Still no results (but there are two dates in there).

I found the way you did the SQL much easier to understand and implement than mine - thank you for that. Now, any other tips to get it to actually work? Or, to debug?

Thanks again!

[NeoPa]

Post #3 deals with that.

Try for line #40 (Earlier references cannot work as rstEMail not opened at that stage) :

MsgBox rstEMail!Received_Date

I should have included the link to Before Posting (VBA or SQL) Code in post #3 too. It has a number of points that help to conform to requirements in the forums, as well as being generally beneficial for your development habits.

[Mmmel]

OK - I know I'm being really annoying, but I'm about to lose my mind.

I think you might have misunderstood my last post - the line I posted (which you put code tags on) is not actually code. I just copied what came up in the immediate window when I ran debug.print. I was hoping that there was something visibly wrong with the SQL string that you could see. It knows the dates, which is good.

I think I have a much bigger problem. When I put in the msgbox in your last post, I get the error message "Compile error: Variable not defined". Am I supposed to have defined this somewhere? It is exactly the field name from my table. I'm definitely missing something. Oh, and thanks again for reminding me of Option Explicit. Reaaaally important.

EDIT: after looking here: I added this line:

Dim Received_Date As Date

Now I get the error: "Compile error: Method or data member not found". Geez, thought I had it too! : (

I'm at a loss. I promise I'll stop bugging you very soon. : )

[NeoPa]

Mmmel:
I think you might have misunderstood my last post - the line I posted (which you put code tags on) is not actually code. I just copied what came up in the immediate window when I ran debug.print. I was hoping that there was something visibly wrong with the SQL string that you could see. It knows the dates, which is good.

Actually, it is an example of SQL code, which still requires the tags Melody. Tags help people to read and discern important items like exactly what quote characters are being used. These can be obfuscated by standard, proportional, web fonts, and are much clearer when displayed in a fixed-width font where these characters are unambiguous (such as that used within the [ CODE ] tags). I saw nothing wrong with the SQL code posted BTW. It was solid.

As for the code, the answer is complex. No. You don't need to create a new variable to hold this as the point here is simply to display what you already have in the Recordset rstEMail. That said, I may have confused matters by using a dot (.) instead of the shriek/bang (!). Try it this way and see what happens. If that still doesn't work then repost your exact current position with the code. It's easier to see things when you can read them in front of you than trying to build a picture from old code with various probable changes made. I have very little idea at this point exactly what you're working with at this point.

[Mmmel]

Hi again!

My apologies. I actually thought I would catch heck for using code tags on something I didn't think was code (in that I didn't copy it from my code). I didn't want to confuse the issue (further, that is!!). Now I understand that those code tags actually serve an additional purpose - to set apart important text and improve readability. Great!

And, I have solved my problem (well, mostly). Using debug.print and msgboxes, I figured out that what it doesn't like is the .attachments line! I was focussing all of my (our) energy in the wrong places.

I definitely learned a lot about debugging in this exercise, which is very valuable to me. I am extremely grateful that you not only help with the issue at hand, but make an effort to educate those you help (the whole "help a man to fish" thing). I feel much more confident to help myself.

Thanks again, NeoPa. Mad respect.

[NeoPa]

You're very welcome Melody :-) and for my part I can say that it's always pleasing to work with someone who's ready to learn rather than simply to use what one posts.

On that point, you may find the following articles illuminating - hopefully they can take you to the next step - Debugging in VBA & How to Debug SQL String. The concepts in the latter are ones you're already using as far as I can see, but there may be some things in the former that can expand your available tools.