Jump to content

Build Theme!
  •  
  • Infected?

WE'RE SURE THAT YOU'LL LOVE US!

Hey there! :wub: Looks like you're enjoying the discussion, but you're not signed up for an account. When you create an account, we remember exactly what you've read, so you always come right back where you left off. You also get notifications, here and via email, whenever new posts are made. You can like posts to share the love. :D Join 92414 other members! Anybody can ask, anybody can answer. Consistently helpful members may be invited to become staff. Here's how it works. Virus cleanup? Start here -> Malware Removal Forum.

Try What the Tech -- It's free!


Photo

Access query structure not working


  • Please log in to reply
9 replies to this topic

#1 Sierra37

Sierra37

    New Member

  • Authentic Member
  • Pip
  • 12 posts

Posted 22 February 2011 - 06:26 PM

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 B). 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

    Advertisements

Register to Remove


#2 appleoddity

appleoddity

    SuperMember

  • Tech Team
  • 3,071 posts
  • Interests:Eating, Movies, Family, Church, Music, Volleyball, Softball, Poker, Computers, Electronics, Reading.

Posted 24 February 2011 - 09:16 PM

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.

The help you have been given is free. If you have been happy with our help please consider donating to support this forum.

If you would like to say thanks for the help I have given you please View My Profile and Leave a Comment.
Your encouragement is welcome.


#3 Sierra37

Sierra37

    New Member

  • Authentic Member
  • Pip
  • 12 posts

Posted 25 February 2011 - 09:00 AM

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

#4 appleoddity

appleoddity

    SuperMember

  • Tech Team
  • 3,071 posts
  • Interests:Eating, Movies, Family, Church, Music, Volleyball, Softball, Poker, Computers, Electronics, Reading.

Posted 25 February 2011 - 09:39 AM

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

The help you have been given is free. If you have been happy with our help please consider donating to support this forum.

If you would like to say thanks for the help I have given you please View My Profile and Leave a Comment.
Your encouragement is welcome.


#5 Sierra37

Sierra37

    New Member

  • Authentic Member
  • Pip
  • 12 posts

Posted 28 February 2011 - 07:09 PM

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 B). 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

#6 appleoddity

appleoddity

    SuperMember

  • Tech Team
  • 3,071 posts
  • Interests:Eating, Movies, Family, Church, Music, Volleyball, Softball, Poker, Computers, Electronics, Reading.

Posted 28 February 2011 - 08:17 PM

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"


The help you have been given is free. If you have been happy with our help please consider donating to support this forum.

If you would like to say thanks for the help I have given you please View My Profile and Leave a Comment.
Your encouragement is welcome.


#7 inzanity

inzanity

    ♠♠lost♠♠

  • Malware Team
  • 2,340 posts

Posted 04 March 2011 - 03:10 AM

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.

Proud graduate of WTT Classroom


The help we provide here is free, however, if you wish to donate, you can do so here: http://www.whatthetech.com/donate/

ASAP and UNITE member

________________________________________________


!


#8 Sierra37

Sierra37

    New Member

  • Authentic Member
  • Pip
  • 12 posts

Posted 04 March 2011 - 09:57 AM

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.

#9 appleoddity

appleoddity

    SuperMember

  • Tech Team
  • 3,071 posts
  • Interests:Eating, Movies, Family, Church, Music, Volleyball, Softball, Poker, Computers, Electronics, Reading.

Posted 04 March 2011 - 07:56 PM

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.

The help you have been given is free. If you have been happy with our help please consider donating to support this forum.

If you would like to say thanks for the help I have given you please View My Profile and Leave a Comment.
Your encouragement is welcome.


#10 inzanity

inzanity

    ♠♠lost♠♠

  • Malware Team
  • 2,340 posts

Posted 05 March 2011 - 01:59 AM

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.

Proud graduate of WTT Classroom


The help we provide here is free, however, if you wish to donate, you can do so here: http://www.whatthetech.com/donate/

ASAP and UNITE member

________________________________________________


!

Related Topics



0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users