By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 896 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

SQL statement returns no values (and it should)!

P: 52
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command26_Click()
  2. Dim strEMail As String
  3. Dim oOutlook As Object
  4. Dim oMail As Object
  5. Dim strAddr As String
  6. Dim strBody As String
  7. Dim MyDB As DAO.Database
  8. Dim rstEMail As DAO.Recordset
  9. Dim qdf As DAO.QueryDef
  10. Dim prm As Parameter
  11.  
  12. Dim strAttach1 As String
  13. Dim strAttach2 As String
  14.  
  15. Dim strSQL
  16. Dim strStart As String
  17. Dim strEnd As String
  18. Dim strRecDate As String
  19.  
  20. Set oOutlook = CreateObject("Outlook.Application")
  21. Set oMail = oOutlook.CreateItem(0)
  22.  
  23. strStart = [Forms]![Admin_Emails]![rec_startdate]
  24. strEnd = [Forms]![Admin_Emails]![rec_enddate]
  25.  
  26. MsgBox strStart & " " & strEnd & " " & strRecDate
  27.  
  28. strSQL = "SELECT * FROM Student_Information WHERE (Received_Date) between " & strStart & " And " & strEnd
  29.  
  30. MsgBox Received_Date
  31.  
  32.  
  33. strAttach1 = Me![path] & "\Course Selection Form.pdf"
  34. strAttach2 = Me![path] & "\Term Timetables.pdf"
  35.  
  36. 'Retrieve all E-Mail Addressess in qryCurrent1stContact
  37. Set MyDB = CurrentDb
  38. Set rstEMail = MyDB.OpenRecordset(strSQL)
  39.  
  40. MsgBox Received_Date
  41.  
  42. On Error GoTo localerror
  43.  
  44.     DoCmd.Save
  45.  
  46. With rstEMail
  47.   Do While Not .EOF
  48.     'Build the Recipients String
  49.     strEMail = strEMail & ![E-mail] & ";"
  50.      .MoveNext
  51.   Loop
  52. End With
  53.  
  54. '--------------------------------------------------
  55. 'Set what will be in the body of the email
  56.  strBody = DLookup("EmailBody", "tblEmailBodies", "[ID] = 7")
  57.  
  58. With oMail
  59.   .To = "email@email.fake"
  60.   .Bcc = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  61.   .Body = strBody
  62.   .Importance = 2
  63.   .Subject = "Important Information From us - Please Read!"
  64.   .Attachments.Add strAttach1 And strAttach2
  65.   .Display
  66. End With
  67.  
  68.  
  69.  
  70. Set oMail = Nothing
  71. Set oOutlook = Nothing
  72.  
  73. rstEMail.Close
  74. Set rstEMail = Nothing
  75.  
  76. GoTo localexit
  77.  
  78. localerror:
  79. MsgBox "There are no records in the date range you have selected.", vbCritical
  80.  
  81. localexit:
  82. End Sub
  83.  
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
Mar 20 '12 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. With Me
  2.     strSQL = "SELECT * " & _
  3.              "FROM   [Student_Information] " & _
  4.              "WHERE ([Received_Date] Between #%S# And #%E#)"
  5.     strSQL = Replace(strSQL, "%S", Format(CDate(.rec_StartDate), "m\/d\/yyyy"))
  6.     strSQL = Replace(strSQL, "%E", Format(CDate(.rec_EndDate), "m\/d\/yyyy"))
  7. End With

Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. With Me
  2.     strSQL = "SELECT * " & _
  3.              "FROM   [Student_Information] " & _
  4.              "WHERE ([Received_Date] Between #%S# And #%E#)"
  5.     strSQL = Replace(strSQL, "%S", Format(CDate(.rec_StartDate), "m\/d\/yyyy"))
  6.     strSQL = Replace(strSQL, "%E", Format(CDate(.rec_EndDate), "m\/d\/yyyy"))
  7. End With
Mar 20 '12 #2

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 20 '12 #3

P: 52
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:
Expand|Select|Wrap|Line Numbers
  1. 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!
Mar 20 '12 #4

NeoPa
Expert Mod 15k+
P: 31,186
Post #3 deals with that.

Try for line #40 (Earlier references cannot work as rstEMail not opened at that stage) :
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 20 '12 #5

P: 52
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:

Expand|Select|Wrap|Line Numbers
  1. 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. : )
Mar 20 '12 #6

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 20 '12 #7

P: 52
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.
Mar 21 '12 #8

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 21 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.