Report will not filter for dates

[Numpty]

Hi Guys,

Got a report problem bugging now which I can't sort and hoping someone here could point me in the right direction.

I have a report which is based upon a query. When I open the report from the Database window it runs fine - showing all the records held that fulfill the query criteria.

I have a simple form to allow the user to select dates to filter the results to be between the given dates. Now basically it will not filter for the dates - when putting in some dates it removes all the results (blank report) and other dates it displays all records (including those outside of the dates entered).

This is the code I'm using in the OnClick event....

DoCmd.OpenReport "rptSupplyCover", acViewPreview, , "[CommencementDate] Between #" & _
[Forms]![SupplyReportSearch]![FromDate] & "# And # " & [Forms]![SupplyReportSearch]![ToDate] & "#"

Hopefully someone can see where I'm going wrong or point me in the right direction.

Your my only hope

 

[dcx693]

OK, this might be totally off, but is there a space after the second # in this portion of your code? "# And # "

 

[Numpty]

Cheers for the reply dcx

There was a space there and I've removed it but it still will not filter the report correctly.

 

[dcx693]

Still giving you the same results?

What is the recordsource for the report? I assume it's a query with all possible dates?

 

[Numpty]

Yeah same results.....

The report is based on a query, the date part of which comes from a table holding details of courses, including Commencement Date. There are also some other conditions in the query - where the person has completed the course and if they requested supply cover while they attended the course.
When I run the query itself it correctly displays 8 results of students who have completed courses and also requested supply cover. If I put date parameters into the query that works - but I'd rather use the form the built in parameter input box.

Just this form will not return the correct results for me.

This ones had me frustrated since yesterday morning

 

[dcx693]

How about this, just send the exact dates you want to the report to see if there's some problem with the expression. I mean, if the expression with the actual values like this:
DoCmd.OpenReport "rptSupplyCover", acViewPreview, , "[CommencementDate] Between #1/1/2003# And #6/1/2003#"
doesn't work, then we can at least narrow down where the problem is coming in.

 

[Numpty]

Hmm very strange now.....

I entered the following code

DoCmd.OpenReport "rptSupplyCover", acViewPreview, , "[CommencementDate] Between #01/04/03# And #30/04/03#"

And it displayed nothing so I changed the dates to

#01/04/2003# And #30/04/2003#

Now it filtered out the results after 30/04/03 but still gave a result from before 01/04/03 (the 10/01/03).

I tested it by changing the dates to #01/04/03# And #21/05/03# and it gave me one extra result and filtered out any after the last date - quite correct, although still leaving the 10/01/03 result there.

Confusing

 

[dcx693]

Could this be a date format issue? In the states, we usually specify mm/dd/yyyy, but you used dd/mm/yyyy. Does Access follow the conventions set in the Regional Settings control panel applet?

 

[Rich]

Format([Forms]![SupplyReportSearch]![FromDate],"mm/dd/yyyy") etc

 

[Numpty]

Been away for a few days guys so did'nt see the replies previously.

I've decided to just use the Parameter prompts when the query executes for this one, guess I'm accepting defeat!

Thanks for looking at it though.

 

[gorellb]

Just a thought here... Could your form not be saving the dates you have selected as your parameters before running the query for the report and therby finding null data for one of the parameters? May need to do a save before opening report...

 

[Chumfatty]

I've been having date selection problems too! Try setting the parameters for your the data type by setting the format for date/time. Click the "Query" drop down and enter the following kinda thing;

[Forms]![YourDateSelecterFormNameHere]![StartDateOrTheTextBoxNameHere]

chose date/time from the drop down.

Ive done this in the past and it's helped. Maybe you should give it one last roll of the dice lol