Selecting records with a prompt for date

[Jim Stanicki]

I have looked but have been unable to find the answer in past posts and this must be a common problem. I wish to include all records up to and including a date passed in the prompt "Criteria: <=[enter end date]". I changes the date format in the back end to short and I am in the US. However I get only dates less then the the prompted date.
Thanks for any help
Jim

 

[pbaldy]

Does your date field include a time? If so, that would cause this problem.

 

[Jim Stanicki]

Paul
I went to the backend and changed the format to short and that seemed to remove the time. Is their a better way to remove the time?
Thanks
Jim

 

[pbaldy]

Changing the format does not change what is actually stored. If you populate the field with Now(), then it includes time. There are any number of ways around it, but try this as a criteria:

< DateAdd("d",1,[enter end date])

 

[Jim Stanicki]

Thank you. That did the trick.
Jim

 

[pbaldy]

No problem; same type of thing really messed with my head the first time I ran into it.

 

[Brianwarnock]

Hi Jim
Although Paul solved your problem I feel that he should have provided a bit of education for future reference, something he could do better than I , but here goes. If no time is stated in a date/time field then the time defaults to 00:00:00 so if the user had entered 11/11/07 the actual comparison is against 11/11/07 00:00:00 thus if you say <= you would only pickup midnight, which why Paul added a Day and change the comparitor to just <

Brian

 

[pbaldy]

Thank you Brian; I should have done that, but was hurrying out to an outlying office. Just went live with a pretty comprehensive new application, and they keep calling with questions or things they want to add (don't ask why they didn't ask during testing). In fact, they called again while I was typing this and I'm heading over there again!

 

[Brianwarnock]

Paul I owe you an apology, having reread my post it comes over as criticising you, that was not intended, I realise that some people have work to attend to , my only problem is , is my knowledge and explanation good enough these days.

Again apologies

Brian

 

[boblarson]

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.

 

[pbaldy]

No apology necessary Brian; I took no offense. We know you retired guys have enough time on your hands to clean up after us poor working stiffs.