displaying query results problem

[brad78]

Hi all,
I have an issue with a report I'm working on. The report is to display all records between two dates. These dates are determined by the date() function and stored as variables. When my code runs, the dates populate fine and my SQL looks right but I don't know how to get the results to display. Any help here would be greatly appreciated.

Here's my code

Private Sub Report_Open(Cancel As Integer)
Dim endDate As Date
Dim startDay As Integer
Dim startMonth As Integer
Dim startYear As Integer
 
endDate = Date
startDay = day(Date)
startMonth = month(Date)
startYear = year(Date)
startDay = startDay - 4
 
Dim startDate As Date
startDate = startDay & "/" & startMonth & "/" & startYear
startDate = CDate(startDate)
 
Dim strSQLSel As String
 
strSQLSel = "SELECT date_due, page_file_name, section_name, primary_contact_name, description, completed FROM update_html WHERE ((date_due <= " & startDate & ") And (date_due >= " & endDate & ")) And (completed = Yes) ORDER BY update_html.date_due DESC"
'MsgBox (strSQLSel)
 
DoCmd.RunSQL (strSQLSel)
End Sub

 

[boblarson]

You likely need to change to:

strSQLSel = "SELECT date_due, page_file_name, section_name, primary_contact_name, description, completed FROM update_html WHERE ((date_due <= [color=red][b]#[/b][/color]" & startDate & "[color=red][b]#[/b][/color]) And (date_due >= [color=red][b]#[/b][/color]" & endDate & "[color=red][b]#[/b][/color]))

 

[brad78]

thanks Bob,
The SQL seems to be ok. when I display the query in a message box, it's right. I'm just not sure how to execute the sql in the VBA window of a report and have it display properly. I thought using the openQuery with the sql query would display properly but it doesn't seem to...

 

[boblarson]

Why are you wanting to display the query when the report opens? Are you trying to set the recordsource of the report?

 

[brad78]

Yes, Exactly, I just don't know how to do it. Sorry, I should have been more clear.

I am putting the sql into a message box to check that it's correct, which it is. The problem now is that I can't seem populate the report from the sql query.

 

[boblarson]

It should be just:

Me.Recordsource = strSQLSel

 

[brad78]

Hi Bob,
Thanks. That seems to bring up some results if I remove the where clause from the sql query. I have tried the query as you stated with
#" & endDate & "# format but it won't return my desired records. Is there a trick to compairing dates that I'm missing? Really appreciate the help!

Private Sub Report_Open(Cancel As Integer)
Dim endDate As Date
Dim startDay As Integer
Dim startMonth As Integer
Dim startYear As Integer
endDate = Date
startDay = day(Date)
startMonth = month(Date)
startYear = year(Date)
startDay = startDay - 4
Dim startDate As Date
startDate = startDay & "/" & startMonth & "/" & startYear
startDate = CDate(startDate)
Dim strSQLSel As String
strSQLSel = "SELECT date_due, page_file_name, section_name, primary_contact_name, description, completed FROM update_html WHERE ((date_due < #" & startDate & "#) And (date_due >= #" & endDate & "#)) And (completed = Yes) ORDER BY date_due DESC"
 
Me.RecordSource = strSQLSel
End Sub

 

[brad78]

ya, definitly something to do with the comparing date values. Is there a better way to compare dates than the format I am trying?

WHERE ((date_due < " & startDate & ") And (date_due > " & endDate & "))
Cheers
Brad

 

[boblarson]

Well, for one - it looks like your start date and end date are the same date - Date which is today. So, why is that?

 

[brad78]

This report is run every friday and the start date is actually today - 4 so is should display all records between monday and friday.

startDay = day(Date)
startMonth = month(Date)
startYear = year(Date)
startDay = startDay - 4
Dim startDate As Date
startDate = startDay & "/" & startMonth & "/" & startYear
startDate = CDate(startDate)

 

[boblarson]

Instead of going about it the hard way, try the easy way

[color=red]endDate = Date
startDate=DateAdd("d", -4, Date)
[/color]
strSQLSel = "SELECT date_due, page_file_name, section_name, primary_contact_name, description, completed FROM update_html WHERE ((date_due < #" & startDate & "#) And (date_due >= #" & endDate & "#)) And (completed = Yes) ORDER BY date_due DESC"
 
Me.RecordSource = strSQLSel

 

[brad78]

That does clean up a lot of unnecessary code, but it still doesn't retrive any records. If I remove the date comparisons, the report retrieves information so I'm wondering if there's a format problem with one of the date fields... I have also tried the BETWEEN clause and cannot retrieve the information I need. Do I need to reference the me.recordsource in front of date_due for comparison?

Private Sub Report_Open(Cancel As Integer)
Dim endDate As Date
endDate = Date
Dim startDate As Date
startDate = DateAdd("d", -4, Date)
Dim strSQLSel As String
strSQLSel = "SELECT date_due, page_file_name, section_name, primary_contact_name, description, completed FROM update_html WHERE ((date_due BETWEEN " & startDate & " And " & endDate & ") And (completed = Yes)) ORDER BY date_due DESC"
MsgBox (strSQLSel)
Me.RecordSource = strSQLSel
End Sub

 

[boblarson]

I don't see the # signs in your latest post (for date delimiters).

Also, is your field date_due really a date datatype? If so, is it short date or general (with time)?

 

[brad78]

OMG you're a saint!

I put the #'s back and it picked up the proper records using the BETWEEN clause.

Thank you soooo much for the help!
Brad