[Slimboy Fat]

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]

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]

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]

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]

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]

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

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]

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:

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]

[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]

[bump 2]

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

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:

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

HTH

chris.

[Slimboy Fat]

Works great,

Thanks A Lot

[cristobal03]

Glad I could help

chris.