Searching for records between a certain date

[James.90]

Lets say i have records that contain the following dates

09/06/07

14/06/07

20/06/07

31/06/07

14/07/07

And i wanted to know what records contain anything between 09/06/07 and 31/06/07 so it would show the records that have this in the date field

09/06/07

14/06/07

20/06/07

31/06/07

What would be the best way to do this

Thanks in advance

 

[KeithG]

use the between statement in your criteria

between #09/06/07# and #31/06/07#

 

[statsman]

Or you could enter in the criteria

Between [Start Date] And [End Date]

square brackets [ ] required.

The query will prompt you for the start date and end date and you can enter whichever 2 dates you wish.

 

[boblarson]

Or you could enter in the criteria

Between [Start Date] And [End Date]

square brackets [ ] required.

The query will prompt you for the start date and end date and you can enter whichever 2 dates you wish.

But if you are going to do anything, one thing I would suggest to the Original Poster is to use a form for user interaction instead of working in the query directly. The benefits are:

1. You have more control over what the user does. You can use events to do things that opening queries by themselves do not do. So, even displaying a query is good to do in a form. And, if users like the way the query looks, you can use the datasheet view of the form to emulate it, yet still have control.

2. You can pass the information, not just once, but should the user want to re-run the same query for the same dates they won't have to re-enter the information.

3. If they accidentally type one mistake when entering it, they have to enter the entire thing over instead of just editing a value in text box.

 

[Geordie2008]

boblarson,

do you have an example of this? It is exactly what I would like to do, but have no idea where to start...

I have a large query which contains audit trail type data for staff members...

I would like the user to be able to select which dates they want to be able to run a report on....

Ideally I would like to provide a form which has each field available in the query as a checkbox so the use can select which fields they would like as the "row" "column" "value" of their report.... and then select the dates they would like to view from a calendar style drop down box....

I think this may be unachievable however.... I like to think blue sky and then see what is actually possible!

Do you have any examples of user interactive reporting databases?

Regards
Mandy

 

[boblarson]

Check out this sample (from my website) to see if it helps:

http://downloads.btabdevelopment.com/Samples/dates/ReportAndForm.zip

 

[James.90]

Hey. Thanks for the replies I had a good look at that sample database and i was able to implement something similar into mine.

My only question is, Is there anyway to ignore a field if it is left blank. For example i have the ability of searching between 2 dates and to search for a customers name etc but i would like it to be able to ignore that field and not search anything if left blank.

Any ideas on how to overcome this

Thank you everyone for your replies so far!

 

[boblarson]

What is the code you are using?

 

[James.90]

That was quick.

I've got a form set up with input boxes and a query that assigns each input box to field

For example
[Forms]![Query Input]![Name]

And i have a button that opens up the report once you click it.

 

[boblarson]

The reason I ask is that I have another database sample that is able to let you select a beginning date an ending date, and selection from the combo box or ANY combination thereof. You can select an ending date and something from the combo and not a starting date, or you can select none of them and get all records.

http://downloads.btabdevelopment.com/Samples/combos/FormSampleFromMultipleCriteria.zip

 

[James.90]

Excellent. I'll have a look at that now. Thanks a ton!