Live Chat & Podcast at 1:00PM Eastern on Sunday!
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod computer crash driver drivers error ethernet excel freeze gaming gpu hard drive hardware hdmi internet laptop mac malware memory monitor motherboard music network printer problem ram registry router server slow software sound trojan ubuntu 11.10 uninstall usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Solved: Access: Querying Dates

Reply  
Thread Tools
Slimboy Fat's Avatar
Member with 142 posts.
 
Join Date: Oct 2004
Experience: Intermediate
02-Nov-2005, 11:14 AM #1
Solved: Access: Querying Dates
Running Office XP

Our Database has a Date field in the following format dd/mm/yyyy hh:mm.

We are trying to run a query that will list all records from a certain date that we enter (as dd/mm/yyyy note no time).

Using the following criteria...

=[:TodaysDate] Brings up no records?

>=[:TodaysDate] And <[:TomorrowsDate] list all todays records which is what we want but don't want to have to type in 2 Dates???



TIA
Raw2K
cristobal03's Avatar
Senior Member with 3,019 posts.
 
Join Date: Aug 2005
Experience: Advanced
02-Nov-2005, 11:38 AM #2
In your code, TodaysDate is a calculated field, yes?

You'll want to format your Date field (hope it's not actually called Date) to get rid of the time when you query it--i.e., Format(YourDateField, "dd/mm/yyyy").

But I wonder...

Bear in mind that Access stores dates as referrential instances in time. If you populate your Date field using something like Now(), the number that's stored is a floating-point value. Instead of bothering with the format, since days are integers (not technically, but floating-points with .00000 decimal value), you might have better luck if you calculate your Date field to round it down--in other words, clear everything after the decimal (which represents special instances like hours and minutes, not general instances like days).

That should allow you to test date to date in a more unary fashion.

Did that make any sense at all?

chris.
Slimboy Fat's Avatar
Member with 142 posts.
 
Join Date: Oct 2004
Experience: Intermediate
02-Nov-2005, 11:54 AM #3
I have read it through once and understood everything upto "In Your Code"

I will read through it a few more times and see If my tiny brain can work it out.

The date is populated using a com object that gets the data from a website and inserts it into the field.
Slimboy Fat's Avatar
Member with 142 posts.
 
Join Date: Oct 2004
Experience: Intermediate
02-Nov-2005, 11:57 AM #4
Quote:
Originally Posted by cristobal03
In your code, TodaysDate is a calculated field, yes?
Todays Date is just entered into a "Enter Parameter Value" window.
cristobal03's Avatar
Senior Member with 3,019 posts.
 
Join Date: Aug 2005
Experience: Advanced
02-Nov-2005, 12:07 PM #5
Sorry, I'll try to explain this a little better.

In the table you're trying to query, you have a field that stores a Date/Time object (a date value, plus hours and minutes). Access doesn't store that value as, say,

02/11/2005 10:00

It stores the value as a floating-point (think: decimal) number, like so:

38658.4180555556

Now, if you're trying to compare that value against the current date, you have to drop everything after the period. Days are represented by the integer part of the floating-point value, and parts of days are represented by the decimal or precision part of the floating-point value. Basically what I think you're trying to do is something along the lines of

IF 02/11/2005 10:00 = 02/11/2005 00:00 THEN I want those records.

What Access sees, though, is something more like

IF 38658.4180555556 = 38658.000000000 THEN You'll never get your records.

That's why it works if you enter two dates. Because

38658.4180555556 is greater than 38657.00000000000

AND

38658.4180555556 is less than 38659.00000000000

So, I suggested that you use a conversion function to change your date field to an integer value, so you can do something like

IF 38658 = 38658 THEN You'll get all the records for 02/11/2005.

I hope that makes more sense.

chris.
Slimboy Fat's Avatar
Member with 142 posts.
 
Join Date: Oct 2004
Experience: Intermediate
02-Nov-2005, 12:27 PM #6
Sorry If I am sounding dumb here, but how did you get 38658 from 02/11/2005


Im assuming that the changes are going to need to be put into the SQL code which currently looks like this...

Code:
SELECT Data.RegNum, Data.StartDateTime, 
FROM Data
WHERE (((Data.StartDateTime)=[:TodaysDate]));
The (Data.StartDateTime) is in the format dd/mm/yyyy hh:mm
We want to enter [:TodaysDate] as dd/mm/yyyy

Thanks for you help so far..

Raw2K
cristobal03's Avatar
Senior Member with 3,019 posts.
 
Join Date: Aug 2005
Experience: Advanced
02-Nov-2005, 12:35 PM #7
Quote:
Originally posted by Raw2K:
Sorry If I am sounding dumb here, but how did you get 38658 from 02/11/2005
Magic

Actually, the simple version is, I typed ?CDbl(Now) in the VBA Editor's Immediate window. But I only knew to do that because I know that Access stores date/time values as floating-point numbers not dates (actually, most languages store dates/times as floating-point numbers). I'm not exactly sure what the reference point is, but it's something along the lines of January 1, 1900 (or maybe 1930?). Anyway, the point being, today is 38,658 days from that date.

Make a copy of your database file and try changing your SQL to something like this:

Code:
SELECT Data.RegNum, Data.StartDateTime
FROM Data
WHERE CLng(Data.StartDateTime) = CLng([:TodaysDate]);
I'm not sure that'll work, mind. I've never used a calculated field in a query. Or a parameter, either. If it throws errors, let me know what they are and I'll do some looking around.

chris.
cristobal03's Avatar
Senior Member with 3,019 posts.
 
Join Date: Aug 2005
Experience: Advanced
02-Nov-2005, 12:43 PM #8
[bump]

Hang on.

Testing that, guess you can't use conversion functions in queries. Let me find out what sort of functions the Jet engine supports, I'll post back.

chris.
cristobal03's Avatar
Senior Member with 3,019 posts.
 
Join Date: Aug 2005
Experience: Advanced
02-Nov-2005, 12:55 PM #9
[bump 2]

Tested, this works. (Had to use a SQL-supported scalar function.)

Code:
SELECT Data.RegNum, Data.StartDateTime
FROM Data
WHERE DATEDIFF("d", Data.StartDateTime, [:TodaysDate]) = 0;
The DATEDIFF function compares dates based on the criteria provided. The syntax (as far as I can find) is

DATEDIFF(criterion, FirstDate, SecondDate)

Though I know there's more to it than that. Using "d" in the SQL above tells DATEDIFF to compare by days. If the difference between the days is zero, the record is displayed.

Try this out, see if it works for you.

If you don't want to type in the date at all, and only want those records where a date matches the current day, you can use something like this:

Code:
SELECT Data.RegNum, Data.StartDateTime
FROM Data
WHERE DATEDIFF("d", Data.StartDateTime, Now) = 0;
HTH

chris.
Slimboy Fat's Avatar
Member with 142 posts.
 
Join Date: Oct 2004
Experience: Intermediate
02-Nov-2005, 01:04 PM #10
Works great,

Thanks A Lot
cristobal03's Avatar
Senior Member with 3,019 posts.
 
Join Date: Aug 2005
Experience: Advanced
02-Nov-2005, 01:06 PM #11
Glad I could help

chris.
Reply

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 06:03 PM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.