Starting and Ending Dates in Query

[NewbyForAccess]

I have reports based off of date criteria that is inserted on my opening form. I used an expression in my query to pull the report information based off of the dates chosen. It works except one thing, whatever the ending date is that is part of the criteria does not view in my report. So, if I use 9/1/15 to 9/9/15, all of the information is shown in my report except anything dated 9/9/15. This is the expression I used:

>=[Forms]![Open Form]![txtStartDate] And <=[Forms]![Open Form]![txtEndDate]



What am I doing wrong?

[pbaldy]

Almost certainly your values include a time component. Either fix that if it's not desired, or use DateValue(), add 1 to the end date, etc.

[NewbyForAccess]

Thanks, Paul. I'll see what I can do to fix this. Appreciate the help!

[pbaldy]

No problem, post back if you get stuck. You may want/need the time component, which is fine, we can work around it. I didn't know which way you wanted to go.

[NewbyForAccess]

Okay, seeing as how I'm still so unfamiliar with many of the Access features and functions, I'm stuck. I checked my properties for the field in my table that I'm sorting and it is set for short dates and I'm not using the time component on it. But, as crazy as it is, I used the exact expression but added +1 to the end of it after txtenddate and it actually worked. What? I would have never thought. This is I'm sure very clunky so I know it's not what I want to use. I'm struggling to learn because I have to do most of my learning when I can find time at work which is seldom and also between constant interruptions. I signed up for a trial version of Access so I could get a crash course at home and fix the databases I use at work, but now my trial is over and I can't justify spending the money buying Access if I'm not setting up a database for my own personal use at home. Unfortunately, the company won't buy it for me. This forum has helped me so much already and I can't thank you enough!

[pbaldy]

Happy to help! Do you use Now() to populate that field?

[NewbyForAccess]

Ah, yes, I do. This is for a completion date and when the person who updates the record signs off, it automatically fills in the date field by using Now(). Is this what's causing the problem? The other queries I use with the same date expression work just fine. It's just this one that isn't. The other field that's used is manually keyed in by the creator of the record.

[pbaldy]

Yes, Now() includes the time. If you just want the date, use Date().

[NewbyForAccess]

I changed the Now() to Date() and it didn't work. What happened was Access kept Date but removed the (). I can pull the data correctly as long as I use the +1 to the end date.

[pbaldy]

It should still work. In some instances Access will drop the parentheses. It wouldn't affect existing data, but you could run an update to fix that.