Entering between dates as parameters

[james_IT]

hi everyone,

i have a query that is used to generate a report. the report is based on date so i enter the following into the date criteria

Between [Please enter the date you wish to start the report] And [Please enter the date you wish to end the report]

it works fine however the problem is that my date is set up as medium date format which means 01-01-06. if i type 01.01.06 then it returns NO results.

How can it be avoided so that you can type ANY format of date and it will return correct results.

thanks

 

[boblarson]

Set up a FORM that lets you select your dates and you can set the AfterUpdate event of the form to format the input as you like. Or you can set an input mask so that you just have to type the numbers and don't have to type the dash or the dots. The form would have to remain open for you to reference it in your query, but it works well and I do that for most of my stuff.

 

[james_IT]

thanks for your reply. is there no other way? does that mean you have a form with the start date/end date boxes in and when you click a button it puts those values into the query which opens the report or something? is there not a criteria you can put into the query that would accept anything?

 

[boblarson]

If you want multiple ways to be able to enter it and have it recognize it as a date, then a form is likely your only way. The "[Enter Start Date]" type of parameter directly in a query requires you to type in EXACTLY the style that the data exists as and it won't find anything if you do it otherwise.

If you create a small form, you can define how you want it to act. In fact, in the AfterUpdate of each of the textboxes, you can have code that will format it as "mm/dd/yyyy" or "mm-dd-yyyy" if you type in mm.dd.yyyy but you can actually set an input mask on the text box so your user (or you) just have to type the numbers and don't have to worry about the characters in between.

There are also some nice little posts here (look in the samples forum near the top of the main page when you come into the forums - or do a search) for some easy to use NON-ActiveX calendar controls so the user can just SELECT the date instead of typing it.

 

[james_IT]

ok, but how does the form return results on a report? i know how to make search forms that use queries to search in txt boxes. i also know how to include a calendar to enter dates. but i return results to a list box. how do you return many results to a report.

 

[boblarson]

Attached a sample for you.

 

[james_IT]

haha, don't worry i did it just as you replied. thanks for the idea, just takes a bit of thinking....

 

[Dreamweaver]

The Open Report Method can except a WHERE statement which will filter the report for the data you want

I've taken this from one of my report systems

           StrWhere = int([DateEntered]) BETWEEN " & Format(Me![txtFrom], "\#mm/dd/yyyy\#") & " AND " & Format(Me![txtTo], "\#mm/dd/yyyy\#")
            DoCmd.OpenReport "Report Name", acViewPreview, , StrWhere

Hope it helps

 

[james_IT]

thanks dreamweaver, useful to know but boblarson's idea was more helpful...thank you both

 

[Dreamweaver]

No Problem