Open report between two dates

[gadjet]

Hi,
This has been driving me mad all day, I have a form with a listbox, the listbox contains a list of records from one date to another date.

I have some code in the click event of a button to open a report and pass the start date (row 1 in listbox) and the end date (last row in listbox) to the report.

Private Sub Command21_Click()
Dim StartDate As Date
Dim EndDate As Date
Dim stDocName As String
Dim stLinkCriteria As String

StartDate = Me.List17.ItemData(1)
EndDate = Me.List17.ItemData(Me.List17.ListCount - 1)

stDocName = "prep details"
stLinkCriteria = "[dates] >=#" & StartDate & "#" & " AND [dates] <=#" & EndDate & "#"
'stLinkCriteria = "[dates] Between #" & StartDate & "#" & " And #" & EndDate & "#"
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

End Sub

The problem is that the report is blank! if I comment out the end date the report shows the records from the correct start date until the end of the records but whatever I do it will not limit the range to between start and end dates.

I've also tried using between without success.

Can anyone see what I'm doing wrong?

Cheers

 

[boblarson]

Your field in the data is actually named dates?

 

[DCrake]

What you need to do is to supply both the column number and the index number. It appears that you are using a query to underpin your list box, is that correct?

Why not simply get the min and max from the underlying query.

 

[gadjet]

Your field in the data is actually named dates?

Yes, Is "dates" not OK?

 

[boblarson]

Yes, Is "dates" not OK?

dates is okay as a name but I just wanted to verify that with you before going on. I'll wait for a moment as you digest David's question to you as well.

 

[gadjet]

What you need to do is to supply both the column number and the index number. It appears that you are using a query to underpin your list box, is that correct?

Why not simply get the min and max from the underlying query.

Yes I admit I'm doing it a step at a time, I will make it smoother later.

getting the date values seems to work OK, I can see this by stepping through the code both start and end date are correct, the problem is the WHERE statement, it doesn't work when I AND both together but works OK if I use just one.

 

[DCrake]

stLinkCriteria = "[dates] Between #" & StartDate & "# And #" & EndDate & "#"

Debug.print stLinkCriteria

What is returned in the immediate window?

 

[gadjet]

stLinkCriteria = "[dates] Between #" & StartDate & "# And #" & EndDate & "#"

Debug.print stLinkCriteria

What is returned in the immediate window?

[dates] >=#28/06/2010# AND [dates] <=#02/07/2010#

 

[DCrake]

What about using the Between ... And line?

 

[boblarson]

You'll need to format the dates to US format.

 

[boblarson]

In other words (like we did for PNGBill):

stLinkCriteria = "[dates] Between #" & Format(StartDate, "mm/dd/yyyy") & "# And #" & Format(EndDate, "mm/dd/yyyy") & "#"

 

[gadjet]

In other words (like we did for PNGBill):

stLinkCriteria = "[dates] Between #" & Format(StartDate, "mm/dd/yyyy") & "# And #" & Format(EndDate, "mm/dd/yyyy") & "#"

Thanks very much for the help, I'll give it a try but the date format from the WHERE statement matches that of the report and it works if I use only one date. Cheers

 

[DCrake]

Can you post a snippet from your db to look at?

 

[gadjet]

Can you post a snippet from your db to look at?

Thanks for all your help, I've made a work around to get it working.

The problem seems to be that the WHERE doesn't like multiple dates, it works fine with one date but not two with an AND statement.

I placed two text boxes on the form and populated these with the start and end dates on form load, then used the generated macro (2007) and used the text box controls in the WHERE statement, this works fine. I then hid the text boxes so the user doesn't see them.


Thanks for the help.

 

[boblarson]

Thanks for all your help, I've made a work around to get it working.

The problem seems to be that the WHERE doesn't like multiple dates,

It would if you do it right. And you do need to format it in US date format. That is a known issue from the start of Access.

 

[gadjet]

It would if you do it right. And you do need to format it in US date format. That is a known issue from the start of Access.

So even though the date format in the table and the report is english format I need to set it to US format in the query?

I've used dates many times and never had to do that, I not saying you are wrong but if that was the case surely it wouldn't work with one date in the WHERE statement!

 

[boblarson]

So even though the date format in the table and the report is english format I need to set it to US format in the query?

I've used dates many times and never had to do that, I not saying you are wrong but if that was the case surely it wouldn't work with one date in the WHERE statement!

Yes, believe it or not - Microsoft in its infinite wisdom has made it so you have to do that.

See this page by Access MVP Allen Browne for a better explanation.

And this is a quote from that page:

In VBA code, delimit dates with the "#" symbol. Regardless or your regional settings, Access expects these literal dates to be in the American format, e.g. #12/31/1999#.

 

[boblarson]

Of course 14 years of working with Access, I would HOPE I would know a little about it.

 

[gadjet]

Of course 14 years of working with Access, I would HOPE I would know a little about it.

Well I'll be..... You were spot on, as I thought you would be, when I used the format you suggested it worked fine.

Microsoft , it works with the UK format with one date but requires the US format when using two dates with an AND ???

Well I've learnt something new, thanks