Still unable to find an exactly matching date value

[Killer42]

Hi all.

After a long break from it, I have again hit the database where I am trying to find records based on a date field. And I have run into the same error again, as reported in this thread. Does anyone have any ideas?

This has nothing to do with chopping up dates and times, or anything fancy like that. All I want is to find a record which has a particular date in the date/time field which is the primary key. Doesn't sound that hard, does it?

[JustJim]

Hi all.

After a long break from it, I have again hit the database where I am trying to find records based on a date field. And I have run into the same error again, as reported in this thread. Does anyone have any ideas?

This has nothing to do with chopping up dates and times, or anything fancy like that. All I want is to find a record which has a particular date in the date/time field which is the primary key. Doesn't sound that hard, does it?

No it doesn't sound difficult at all. I read the other thread and can contribute that I have seen somewhere in the Access help files that the Date/Time datatype only "accurate" (their word but I assume they meant "precise") to one second. It's an eight byte number. How then can a value get lost between two allegedly quantum values?

Really only chucked this in to subscribe. Will be watching you smart guys with interest!

Good Luck

Jim

[Killer42]

Thanks for the input.

I've worked around it for the moment by using the Move method, but it is very odd. In fact I think I've got a more general problem with Find, but don't have time just yet to pin down the details. It seems to work intermittently, making it difficult to identify a pattern.

[MMcCarthy]

Try this ...

SELECT * FROM PerMinuteStats WHERE Format(TheTime, "mm/dd/yyyy hh:ss:nn") = Format(#06/13/2007 07:51:00#, "mm/dd/yyyy hh:ss:nn")
 

Format both the time and the criteria to "mm/dd/yyyy hh:ss:nn". This should drop any milliseconds being imposed by Jet.

[Killer42]

Try this ...

SELECT * FROM PerMinuteStats WHERE Format(TheTime, "mm/dd/yyyy hh:ss:nn") = Format(#06/13/2007 07:51:00#, "mm/dd/yyyy hh:ss:nn")
 

Format both the time and the criteria to "mm/dd/yyyy hh:ss:nn". This should drop any milliseconds being imposed by Jet.

Thanks Mary.

I may have a play with it. But with tens of millions of records, anything that has to format the data to select records isn't going to fly.

The weird thing is, the more I looked at this problem, the more it seemed as though the FIND was simply not working consistently. I even broke out the day, month and year into separate numeric fields (byte or Integer, I forget which) and tried searching on them, and the find failed.

I've worked around the problem for now, by using MOVE instead (which is probably faster anyway). By the way, I think (can't remember for sure now) that SELECT statements work alright. It's the ADO recordset Find method I have trouble with.

[MMcCarthy]

Thanks Mary.

I may have a play with it. But with tens of millions of records, anything that has to format the data to select records isn't going to fly.

The weird thing is, the more I looked at this problem, the more it seemed as though the FIND was simply not working consistently. I even broke out the day, month and year into separate numeric fields (byte or Integer, I forget which) and tried searching on them, and the find failed.

I've worked around the problem for now, by using MOVE instead (which is probably faster anyway). By the way, I think (can't remember for sure now) that SELECT statements work alright. It's the ADO recordset Find method I have trouble with.

Try switching to DAO

[FishVal]

Thanks Mary.

I may have a play with it. But with tens of millions of records, anything that has to format the data to select records isn't going to fly.

The weird thing is, the more I looked at this problem, the more it seemed as though the FIND was simply not working consistently. I even broke out the day, month and year into separate numeric fields (byte or Integer, I forget which) and tried searching on them, and the find failed.

I've worked around the problem for now, by using MOVE instead (which is probably faster anyway). By the way, I think (can't remember for sure now) that SELECT statements work alright. It's the ADO recordset Find method I have trouble with.

Hi!

Let me add my 0.2 cents to the discussion.

Using Format function, you know, consumes much time - converting date to formatted string followed by string comparisson. I suspect that Datediff function will work faster. Additionally, I think your first variant - searching value between somedate and somedate + 1s is rather good solution (in this case, I guess, field indexing will accelerate the query).

And the last. I think it would be useful to know a little more about your application. What is the front-end, what is the back-end, where does the query actually run?

P.S. I suppose date values are being entered to the table automatically with smthng like Now(). Do you have an opportunity to change this code so it will add date with milliseconds=0 and run one time an update query on the whole table?

[Killer42]

Try switching to DAO

I can't believe I just got that advice from Mary. :)

Seriously, it's not worth it. The whole application is written using ADO all over the place, except for at startup where it uses DAO to create and link some tables if they aren't there yet.

Since I was able to work around it, the FIND problem is likely to sit on a backburner for some time. (These particular records are read into an array at application startup, so when the time comes to find them and update them, I can just use MOVE and the array index to skip directly to them.)

[Killer42]

Let me add my 0.2 cents to the discussion.

A fifth of a cent? That's not much!

Using Format function, you know, consumes much time - converting date to formatted string followed by string comparisson. I suspect that Datediff function will work faster. Additionally, I think your first variant - searching value between somedate and somedate + 1s is rather good solution (in this case, I guess, field indexing will accelerate the query).

There's no way I can use a query that uses any function to format the data before restricting the selection. I'm dealing with tens of millions of records, so this sort of approach wastes vast amounts of time. I do wonder whether the database engine would be smart enough to calculate things up front so the DateDiff, for example, could be checked from the implied value.

In other words, assume I said (not bothering to put it into SQL syntax) "where datediff between <field> and #literal# < 5". In theory, it could calculate up front the 5-day offset from the literal, then just retrieve the records matching that criteria. But I expect it takes the more straightforward brute-force approach of reading every record (at least the index value), performing the DateDiff, then using the result to decide whether to include the record in the recordset.

And the last. I think it would be useful to know a little more about your application. What is the front-end, what is the back-end, where does the query actually run?

What I have is a VB6 program using ADO to open an MDB. The particular table in question is linked, from another MDB. Neither of them is being used by anyone else. At the moment, everything is just on my local drive.

P.S. I suppose date values are being entered to the table automatically with smthng like Now(). Do you have an opportunity to change this code so it will add date with milliseconds=0 and run one time an update query on the whole table?

Actually, the date values are being built by DateSerial() or DateValue() function in VB6, placed in a Date variable. I'll check later, but as far as I can recall I used transferred it via a string.