Confounded by 2016 dates in query

[Calego]

I have a query that returns records with a date that fall within a date range. I have a parameter put into the query so that when the query is run, it asks for a date. The query then queries for dates with this criteria: ">=DateValue([End Date])-6 And <DateValue([End Date])+1". So "[End Date]" is the parameter value. The query has worked fine up until we've gone past 12/31/2015. I was able to enter "12/31" in the input box and it worked fine. Now, if I enter "1/2" or even "1/2/2016" the query does not return anything even though there are records that match the intended criteria. Can anyone help me with this? Thanks.

[June7]

I NEVER use dynamic parameterized query - and certainly not with a popup prompt because cannot validate user input. I prefer VBA to build filter criteria http://www.allenbrowne.com/ser-62.html.

I have never used DateValue() function. So just tested. See no reason for your criteria to not work - cannot replicate issue. If you want to provide db for analysis, follow instructions at bottom of my post.

[Calego]

I NEVER use dynamic parameterized query - and certainly not with a popup prompt because cannot validate user input. I prefer VBA to build filter criteria http://www.allenbrowne.com/ser-62.html.

I have never used DateValue() function. So just tested. See no reason for your criteria to not work - cannot replicate issue. If you want to provide db for analysis, follow instructions at bottom of my post.

Thanks for your reply. I'll defer to your opinion that I should build an interface and generate the results with VBA. I had just made a quick and dirty (and lazy) way of generating the report. It worked great for a while, until it didn't, LOL. I thought perhaps there was a simple solution, but you seem to know a heck of a lot more about Access than I do, so I am going to reasonably assume there isn't a simple solution.

[June7]

I tested a query using DateValue() function in the parameter and it works. I don't know why doesn't for you. I would have to analyze your db.

[Micron]

If you're inputting dates in the form 01/05/2016, why use DateValue? That's for returning a date when you supply something like "January 5, 2016". I've used parameterized queries MANY times and will continue to do so - especially if I have to pass a parameter from a form in VBA. My 'bias' probably comes from my work environment - everybody instinctively entered dates that worked, but if they made an error it was obvious and easily rectified by them.
Your issue is probably that 1/2/2016 (as you posted) is not recognized as a date, whereas 01/02/2016 would (should?) be. Certainly, I would not expect 1 divided by 2 (1/2) to be recognized as a date.
I'd be inclined to write >=DateValue([End Date])-6 And <DateValue([End Date])+1 as
Between ([Enter End Date As mm/dd/yyy]-6) And ([Enter End Date As mm/dd/yyyy]+1) if I wanted to supply instructions.
I'm not saying my way is better - just 'good enough' for my purpose, but some day I may make the switch!

[June7]

The input is a string and the function converts to a date value. The function supplies the current year if no year is specified.

DateValue("1/2") returns 1/2/2016.

[Micron]

The function supplies the current year if no year is specified.

That part I didn't know, but I knew it converted text values to dates (I provided a text date in my post).

[Calego]

If you're inputting dates in the form 01/05/2016, why use DateValue? That's for returning a date when you supply something like "January 5, 2016". I've used parameterized queries MANY times and will continue to do so - especially if I have to pass a parameter from a form in VBA. My 'bias' probably comes from my work environment - everybody instinctively entered dates that worked, but if they made an error it was obvious and easily rectified by them.
Your issue is probably that 1/2/2016 (as you posted) is not recognized as a date, whereas 01/02/2016 would (should?) be. Certainly, I would not expect 1 divided by 2 (1/2) to be recognized as a date.
I'd be inclined to write >=DateValue([End Date])-6 And <DateValue([End Date])+1 as
Between ([Enter End Date As mm/dd/yyy]-6) And ([Enter End Date As mm/dd/yyyy]+1) if I wanted to supply instructions.
I'm not saying my way is better - just 'good enough' for my purpose, but some day I may make the switch!

Thanks for your reply. I also thought maybe it was reading 1/2 as a mathematical value as "0.5" or whatever. But I think the problem was simpler. This makes me look kind of stupid, but I had entered in two entries in my table with 12/31 as the date, but I entered them in AFTER 1/1/2016, so Access defaulted the year to 2016. My entries became 12/31/2016. When I was trying to query for those dates, they of course didn't show up because they didn't fall within the range. I didn't notice that before. But as I was troubleshooting my query, I realized something maybe relevant (or maybe not). The date value I was querying for also includes the time. In this case the value was #12/31/2015 1:00:00 PM#. If I supply only a date parameter to the query, like #1/2/2016# then does Access convert that to #1/2/2016 12:00:00 AM#? It might affect the results because if I want to include records that fall on the end date, #1/2/2016 1:00:00 PM#, for example, then that record will not be returned in the query because midnight occurs before 1:00 PM.

But anyway I think the query works now with the dates in the data corrected. But was wondering what happens when a date & time field is queried with a date only criteria. Is the time portion defaulted to 12:00:00 AM?

[Micron]

Well, that supports June7's contention that a form for parameter input is a better way, or mine for saying you are using a function in a way that it probably wasn't really meant for. As for time values, I've posted many times that when using the BETWEEN operator, the latter date is trimmed to 00:00:00 (therefore, records that are time stamped, say one minute after midnight e.g. 01/01/2016 00:01:00, are not included). I can't remember if this applies when using >=Date AND <=Date, but if you'd test it for us and report back, that'd be great (but I think not). I learned a while ago that if the date field included time, I need to use the DateAdd function to add either enough seconds or minutes to take me to the end of that day.