9 replies to this topic

[Sierra37]

I am structuring a query in Access 2010 where I want to pull all the widgets counted on a specific date. The field is a Date/Time field and it includes the time. Sample: 02/14/2011 12:59:57 PM. My query prompts the user to enter the date. If the user enters, say, 2-14-2011, no records are returned. It only works if the user enters a beginning date of 02-14-2011 00:00:00 AM and an ending date of 02-14-2011 11:59:00 PM (or Between A And . Is there any way around this? I'm trying to make it as easy as possible for users to run this query. Changing the field type is not an option, and adding a wildcard (*) after the date doesn't work. I hope there's a simpler input .... Thanks, oh great ones out there... Sierra

[appleoddity]

When no time is specified it automatically defaults to midnight (i.e. 00:00:00)

Its not likely you'll have any items at that exact time.

So, when performing the query you can't just look for fields that contain an exact date without specifying a time. To work around this you need to search for a range of times on certain dates. i.e. From midnight to 11:59:59PM on the same day in order to find all records on that date.

In the query designer you can change the criteria from something like this: =[Date]
to: Between [Date] And [Date] & " 23:59:59"

This prompts the user to enter a date, and then searches for records between a certain time range on that date by appending the time on to the second value. The first value already has a default time of midnight so we don't actually have to append 00:00:00 to that one.

If you are looking for a date range you can use: Between [StartDate] and [EndDate] & " 23:59:59"

Edited by appleoddity, 24 February 2011 - 09:17 PM.

[Sierra37]

Thank you. Yeah, I learned there is no way around this. And the users are adapting to entering the hours on the "ending date" (the query prompts them for a beginning and ending date) so all is well. Some just do not like the 24 hour clock, so 11:59 PM works just as well and they understand that better. Ah, life... Thanks again. Sierra

[appleoddity]

I just provided the solution... Didn't you get it?

[Sierra37]

Yes, I got your solution. It's the same solution I posed in my initial question: It only works if the user enters a beginning date of 02-14-2011 00:00:00 AM and an ending date of 02-14-2011 11:59:00 PM (or Between A And . I was hoping for a single date entry as the solution, but I can see that there is no way around this when a time stamp is involved.
Thanks anyway!
Sierra

[appleoddity]

No, you really didn't get it.

I spent a half hour researching your problem, and literally implementing local solutions until I was sure of the results.

I then came back here and posted exactly why you have the problem you have, how to resolve it, and gave you literal examples of exactly what to do.\

In the query designer you can change the criteria from something like this: =[Date]
to: Between [Date] And [Date] & " 23:59:59"

[inzanity]

Hi,

Not sure if the syntax works with access but you can do something like this in SQL Server:

WHERE CONVERT(VARCHAR(10), DATETIMEFIELD, 101) BETWEEN '03/01/2011' AND '03/01/2011'

No need for the timestamp here. Just change the DATETIMEFIELD, '03/01/2011', '03/01/2011' to your specification.

[Sierra37]

Thanks, i, I do need the time stamp, unfortunately. Don't need it on the front end, but the end date needs either "11:59 PM" or 23:59:59 to pull the data. I'm apparently not expressing myself well. We recently were required to change our transaction dates such that a time stamp is attached, so we can pull activity by the hour. Before, if we wanted daily activity, we simply entered the date, a single date, and the whole day's transactions would be detailed. Now we have to enter Between -- And -- , with the end date including the time. I tried to be clever and tested "Between [Enter beginning date] And [Enter beginning date]" -- but no go. We're resigned to the extra step now (entering the date twice). I was trying to avoid that, though.

[appleoddity]

Sierra, you've been very clear. Two working solutions have been provided. Have you considered this?

Edited by appleoddity, 04 March 2011 - 07:57 PM.

[inzanity]

Hi,

I do need the time stamp, unfortunately. Don't need it on the front end, but the end date needs either "11:59 PM" or 23:59:59 to pull the data.

What I meant by not needing the timestamp there is that it does not need to be added in the query or front end as you call it as it will pull all the necessary data pertaining to your query, including the timestamp part of your datetime field.

The use of BETWEEN keyword there is to extract the data from a specified date to the end date. If you only want to draw out the transaction for a single day then there is no need to use BETWEEN. You can do it like so:

WHERE CONVERT(VARCHAR(10), DATETIMEFIELD, 101) = 'mm/dd/yyyy'

That will extract the data for a single day. As I've said earlier there is no need to add the time stamp there as the query will only concentrate on the date specified, no matter what the time is as it will show the transactions made, including the timestamp part, in that particular date. Try it.