Filter Problem

[waylander32]

Please help

I want my DB so that 1 report can be filtered easily by any user with as little interaction as possible so I have a form that has combo box's linked to differant fields the idea being that an operator can select for exmple a project code and on the press of a button the report is filtered by that project code.( The report is linked to a query just to bring all the field together )

No problem with the set up and operation of this untill I did the daily report
The combo box looks at a field called run date if they select for example the 25 oct 10 no problem if they select 2 nov 10 the report is blank ( The date must exist to be in combo list)

I have checked the report without any filters no problem also tried entering in as a crietera in the query no problem.

The only pattern I can see is if the day is less then 12 so I thought may be due to locational settings.

I have checked the field properties in the combo box, report and the query all set to the same ie medium date. I'm now stuck.

The answer is probably looking at me laughing can some one help please?????

 

[boblarson]

You have to pass in the dates to the query in U.S. format (mm/dd/yyyy) or else you have a problem. See here for more about that.

 

[waylander32]

thanks fro the help can you expand a little please

Below is the code I use to send the date to the report, how do I then tell it to change the fromat to us


DoCmd.OpenReport "rpt1_detail", acViewPreview, , "[run date]= #" & Me.txt_report_date & "#"

thanks again

as a side question why is it that when all teh setting on my laptop are set as uk but the db still gets confused as to the date format.

 

[waylander32]

found the answer to the side question it's microsoft being ever helpfull when using VB

if someone can show me how to send the information to VB in us format I'll be gratefull

 

[boblarson]

found the answer to the side question it's microsoft being ever helpfull when using VB

if someone can show me how to send the information to VB in us format I'll be gratefull

The link I gave showed you how to format it. But since we need to spell it out explicitly with your specific code:

DoCmd.OpenReport "rpt1_detail", acViewPreview, , "[run date]= #" & Format(Me.txt_report_date, "mm\/dd\/yyyy") & "#"

strSQL = "SELECT * FROM tblDonation WHERE DonationDate > #" & Format(Me.StartDate, "mm\/dd\/yyyy") & "#;"
The third example demonstrates how to concatenate a date into a SQL string. The Format() function is essential to force the date into American format. Unfortunately, Format() replaces the slashes with the date separator character defined in Control Panel | Regional Settings, so you must specify literal slashes in the format string by preceding the slash with backslashes.

The WHERE clause in the DoCmd.OpenReport is basically the same structure as a where clause in a SQL string.

 

[waylander32]

thanks for the help

I did read the inforamtion in the link you posted but didn't pick up the format issue my bag

thanks again