Time confusion

All the data in the table seem to be fine.
But still there is the... "bug"

Only thing left to do is:
1) make a copy of your database
2) Delete any and all tables barring your problem table
3) Delete any and all records from the table and insert some dummy records that have the problem (or edit the existing to "saveguard" privacy)
4) Compact the database
5) Upload to the forum, so we can have a direct look ourselves.

I am pretty sure there is a logical explanation to this all.
 
Only thing left to do is:
1) make a copy of your database
2) Delete any and all tables barring your problem table
3) Delete any and all records from the table and insert some dummy records that have the problem (or edit the existing to "saveguard" privacy)
4) Compact the database
5) Upload to the forum, so we can have a direct look ourselves.

I am pretty sure there is a logical explanation to this all.

I belive that you are right. The only thing that concerns me is that the most logical is the explanation the more dumb i'll feel. Anyhow...
I uploaded the database. The records are the ones with the problem. I belive the problem evolves the fields start_time and end_time.
More specificly the records with id = 1268 and id = 1869 plus the "funny" result in the "Query 2".

I am really really thankfull for your help.
 

Attachments

Dont have 2007 you will have to downgrade/convert this database to 2002 if you want me to look at it.
 
it MIGHT be a problem with the representation of decinmal numbers in binary

a time is represented in access as a fractinoal part of day

now you say you dont have a problem with 12.00noon - now 12.00 noon is half a day

in binary terms 0.1 days, and therefore easily represented

----------------
now you want 13.00 hrs, which 1s 13/24 of a day

which looks to be 0.5416666666 recurring, as a decimal

------------
now i bet access cannot represent this value completely accurately in binary terms, and therefore will give you a value either a fraction of a second less than, or greater than the true value (in the same way that we cannot represent it precisely in decimal)

so for certain times a test for a specfic time will give you a possibility of a tiny error, which certainly complicates things.
 
Try this:

select * from lessons where
Format([date_math], "Short Date") = #29-May-2008# AND Format([end_time], "Short Time") > #13:00# AND Format([end_time], "Short Time") <= #14:30#)
 
No, no no no no, nnnnoooooooo Sheilasabado, bad bad bad idea

Format returns a STRING which you are then comparing to a date/time, this makes access think and eventually will screw you over.

If you want to make sure you only have date and only have time values, use the functions Datevalue and TimeValue, never ever ever do anything like this or risk serious issues .... I.e. 10-06-2010 will NOT be June 10, but Oct 6

A beautifull example of that is your own thread:
http://www.access-programmers.co.uk/forums/showthread.php?p=973435#post973435
 
Try this:

select * from lessons where
Format([date_math], "Short Date") = #29-May-2008# AND Format([end_time], "Short Time") > #13:00# AND Format([end_time], "Short Time") <= #14:30#)

Thanks for the reply
I must confess that I already tried this and really does NOT work.
Really screwed me up especially when i tried to comare dates like this with ">" OR "<" ...
I tottaly aggree with namliam.
 
You might already know this but I'm going to digress a little on date/time comparisons.

First, a date/time field is a "linear" time distance from a reference date, which for Access makes day 0, time 0 equal to 31-Dec-1899 at 00.00.000001 past midnight, thus making the exact moment of the following day's New Year's date change-over become day 1 - on 1-Jan-1900.

The formatting routines compute the date from the linear distance value. Right now, it is something on the order of 45,000 days (give or take a few, don't pin me down on it). This works because subtracting relative dates (time-lines) works equally well as subtracting absolute dates, assuming you had them.

Date/time variables are DOUBLE numbers. That is, the time-line is a REAL number that has an integer part of days and a fractional part of ... fractional days. Noon is 0.500000 whereas midnight is 0.000000 as fractions. If you store a time field, you store fractions. If you add time fields such that they exceed 1.000, you just implied a date. That happens because of the FORMAT routines, not because of the variables. You can keep it as a time in days by converting the total to DOUBLE (see CDbl, for example) or you can keep it as a date and use format "hhh:mm:ss" on it.

What you have to watch out for are these pitfalls. If you have a date/time field and store a date, it does not necessarily store a time of midnight. It might just give you the current time plus the explicit date. First time that happened to me I just about exploded. Of course, once you learn to expect it, you are OK again.

Next pitfall: If you compare "29/05/2008 11:30:00 am" to "29/05/2008 1:00:00 pm" you are CLEARLY BY SYNTAX ALONE doing a text compare. And in that case, you are looking at the sort order of the ASCII characters of the string, which differ at the first colon in the "pm" string. Up to that point, they are the same. The collating sequence of the characters and not the actual date will thus govern the result of this comparison.

If you convert a date/time field to text inadvertently or intentionally because you think you need to... don't. They compare better as a numeric internal value. If you must compare a literal time to a date/time variable, enclose the literal time in # signs rather than quotes. That forces the CDbl conversion.

Search this forum for articles regarding date and time to see how to manipulate time differences. I'm not the only one who posts regularly on this topic.
 
thanks a lot for your time to respont The_Doc_Man

You might already know this but I'm going to digress a little on date/time comparisons.
First, a date/time field is a "linear" time distance from a reference date, which for Access makes day 0, time 0 equal to 31-Dec-1899 at 00.00.000001 past midnight, thus making the exact moment of the following day's New Year's date change-over become day 1 - on 1-Jan-1900.
The formatting routines compute the date from the linear distance value. Right now, it is something on the order of 45,000 days (give or take a few, don't pin me down on it). This works because subtracting relative dates (time-lines) works equally well as subtracting absolute dates, assuming you had them.
Date/time variables are DOUBLE numbers. That is, the time-line is a REAL number that has an integer part of days and a fractional part of ... fractional days. Noon is 0.500000 whereas midnight is 0.000000 as fractions. If you store a time field, you store fractions. If you add time fields such that they exceed 1.000, you just implied a date. That happens because of the FORMAT routines, not because of the variables. You can keep it as a time in days by converting the total to DOUBLE (see CDbl, for example) or you can keep it as a date and use format "hhh:mm:ss" on it.
What you have to watch out for are these pitfalls. If you have a date/time field and store a date, it does not necessarily store a time of midnight. It might just give you the current time plus the explicit date. First time that happened to me I just about exploded. Of course, once you learn to expect it, you are OK again.

I understand fully what you say and i triple-checked that the fields carring the time values have all the access' day 0 as date (thus 1899/12/30 13:00:00).

Next pitfall: If you compare "29/05/2008 11:30:00 am" to "29/05/2008 1:00:00 pm" you are CLEARLY BY SYNTAX ALONE doing a text compare. And in that case, you are looking at the sort order of the ASCII characters of the string, which differ at the first colon in the "pm" string. Up to that point, they are the same. The collating sequence of the characters and not the actual date will thus govern the result of this comparison.
If you convert a date/time field to text inadvertently or intentionally because you think you need to... don't. They compare better as a numeric internal value. If you must compare a literal time to a date/time variable, enclose the literal time in # signs rather than quotes. That forces the CDbl conversion.
Search this forum for articles regarding date and time to see how to manipulate time differences. I'm not the only one who posts regularly on this topic.

I understand that comparing strings is useless in dates because as i mentioned in a previous topic it can really screw you up. So I think I passed through the second pitfall too.

The problem seems to be that the field start time has a value that has the following properties:
start_time <> #13:00:00# = true
start_time > #13:00:00# = true
start_time < #13:00:01# = true
start_time >= #13:00:01# = false
start_time < #13:00:00# = false

so start_time value is somewhere between #13:00:00# and #13:00:01# but not equal with either of them.

Please take a minute and take a look to the database I have uploaded (the first upload is for access 2007 and the second upload is for access 2002). This database contains a table with 6 or 7 records and a query (sql statement) that, I think, indicates the problem.
Thanks again for your time and responce.
 
Your findings seem to be correct :)

Since time is a actual decimal I did this:
CDbl([end_time])*10000000000000-5416666666666
To find the xth decimal point of this value to be: 0.6669921875

CDbl(#1:00:00 PM#)*10000000000000-5416666666666
To find the xth deciaml value of exactly 1pm: 0.666015625

13/24*10000000000000-5416666666666
The part representative of 13 hours on 24: 0.666015625

I cannot seem to get a reliable 1:00:01 PM time stamp for some reason but yes, it seems like your value is slightly larger than 1:00:00 PM

And yes it is a date/time field, with only dates and only times, however if you use TimeValue([end_time]), it seems to get truncated to actual seconds and the field "reverts" back to =1:00:00 PM

([End_time]-TimeValue([end_time]))*10000000000000000
This returns a difference of 1.11022302462516
There is another "end time" at id 1865, which has the exact same deviation but minus instead of positive? At a time of 8:45:00 PM, there has to be a cominality between the two?

This is a total first for me to ever see this, seems like (perhaps) A2007 is able to record milli seconds like SQL Server? Or some other "anomily" going on?
 
Last edited:
I have a vague memory, actually most of my memories are a bit vague these days, that some years ago a number of MVPs discussed time comparison problems on this site and the advice was to always use Date/Time functions as these truncate the values to seconds, and that the best way to look for time matches was with datediff < or = or > 0 depending on what you are looking for.

Brian
 
Namliam's decimal incompatability explanation makes sense and is important to understand. Using DateDiff neatly avoids the issue because it deals with both values in the comparison in a consistent way.

I have often used simple numeric comparisions on dates rather than datediff but it seems that this can easily bring one unstuck comparing times.
 
Your findings seem to be correct :)

Since time is a actual decimal I did this:
CDbl([end_time])*10000000000000-5416666666666
To find the xth decimal point of this value to be: 0.6669921875

CDbl(#1:00:00 PM#)*10000000000000-5416666666666
To find the xth deciaml value of exactly 1pm: 0.666015625

13/24*10000000000000-5416666666666
The part representative of 13 hours on 24: 0.666015625

I cannot seem to get a reliable 1:00:01 PM time stamp for some reason but yes, it seems like your value is slightly larger than 1:00:00 PM

And yes it is a date/time field, with only dates and only times, however if you use TimeValue([end_time]), it seems to get truncated to actual seconds and the field "reverts" back to =1:00:00 PM

([End_time]-TimeValue([end_time]))*10000000000000000
This returns a difference of 1.11022302462516
There is another "end time" at id 1865, which has the exact same deviation but minus instead of positive? At a time of 8:45:00 PM, there has to be a cominality between the two?

This is a total first for me to ever see this, seems like (perhaps) A2007 is able to record milli seconds like SQL Server? Or some other "anomily" going on?

The good side, I think, is that at least I am not crazy. I had begun to belive so. Anyhow, I am sure that the problem exists, but I'm not quite sure about what is the way to go around it.
The problem is that I have a Dlookup that will not work right and is the following:

Code:
CriteriaStr = andID & " AND [date_math] = " & "#" & Format(Datemath, "mm/dd/yyyy") & "#" & _
                 " AND NOT [canceled] AND NOT [unwritten] " & _
                 " AND [teacher] = " & Teach & _
                 " AND (([start_time] >= " & "#" & Format(starttime, "Short Time") & "#" & _
                 " AND [start_time] < " & "#" & Format(endtime, "Short Time") & "#" & " ) " & _
                 " OR ([end_time] > " & "#" & Format(starttime, "Short Time") & "#" & _
                 " AND [end_time]  <= " & "#" & Format(endtime, "Short Time") & "#" & "))"
 
Sympt = DLookup("[ID]", "[lessons]", CriteriaStr)


The second (and harder in my opinion) is the sql problem with grouping.
When I group by start_time there will be 2 groups with, lets say, start_time = #13:00#. I cannot think of any way to go around this.

Do you think that I should upload the forms and functions that inputs data in the table for you to take a look? Should I write a update query or function that has the (TIMEVALUE) funciton foreach start_time and end_time field and run it every time a row is added to the table? Should I ask Microsoft to look into this (just kidding)? Or, finnaly, should I thow the PC off my window?
Many many thanks for your help. I might had gone insane over this without it.
thank you again.
 
The problem is that I have a Dlookup that will not work right and is the following:
Most proabaly you shouldnt be using a DLookup anyways :P

But I told you your solution too, in the very post you quoted.

MEmyselfANDI said:
however if you use TimeValue([end_time]), it seems to get truncated to actual seconds and the field "reverts" back to =1:00:00 PM

The second (and harder in my opinion) is the sql problem with grouping.
When I group by start_time there will be 2 groups with, lets say, start_time = #13:00#. I cannot think of any way to go around this.
Well when your grouping by "only" time there will potentially be (atleast) 59 versions of 13:00.
again here, Timevalue will save your day I think or a format to even force the 59 (other) seconds into the same #13:00#

Do you think that I should upload the forms and functions that inputs data in the table for you to take a look? Should I write a update query or function that has the (TIMEVALUE) funciton foreach start_time and end_time field and run it every time a row is added to the table? Should I ask Microsoft to look into this (just kidding)? Or, finnaly, should I thow the PC off my window?
Many many thanks for your help. I might had gone insane over this without it.
thank you again.

All in all asking M$ what the hell is going on might not be a bad idea.
As far as I know the date field is only supposed to "know" seconds. Thus this shouldnt happen... I dont know how/why what is happening, are you perhaps using the "Timer" someplace to fill your data?

Timer is the only thing I know of that keeps milliseconds and
?Timer()/60/60/24 = time()
Will almost always return false eventhough they are "basicaly" the same

However
?(int(Timer())/60/60/24) = time()
Will have more success...
 
Most proabaly you shouldnt be using a DLookup anyways :P

I really think the same but unfortunately I could't find another way to do what I wanted. the dlookup helps me to locate wheather there is already any record in the table that looks like the record that is about to insert. I use it in the before update of the input form to prevent double entries or error entries (ie a student cannot attend two lessons at a time).
If there is another way to ensure this I am open to suggestions.

again here, Timevalue will save your day I think or a format to even force the 59 (other) seconds into the same #13:00#

TimeValue saved the day indeed. So did you. You saved my project and a really big amount of time. I am thankfull.

All in all asking M$ what the hell is going on might not be a bad idea

Since you too think that is not a bad idea I tried to contact M$ but I do not have any answers till now. It's more complicated than I thought to ask M$ for help with a "bug". If I have any answers you'll be the first to know.

I dont know how/why what is happening, are you perhaps using the "Timer" someplace to fill your data?

I do NOT use Timer anywhere. The input is done only with some Bound or Unbound forms and with a "INSERT INTO" query. I don't think that any of this has anything to do with milliseconds. The only thing that is not absolutely normal in the program is that I use Global variables (in a standard module) in the "INSERT INTO" query I mentioned. But still I don't think this counts in any way.

Anyway. I want to thank you again for your invaluable help. You literally saved me!
Thank you!
 
Last edited:
Well when your grouping by "only" time there will potentially be (atleast) 59 versions of 13:00.
again here, Timevalue will save your day I think or a format to even force the 59 (other) seconds into the same #13:00#.

Something i forgot to mention in my pprevious post:
I never using seconds. Just #hh:nn#. It worked perfectly with this.
 
If you just use HH:NN your set :)

The problem with DLookup is if you use it multiple times
Value1 = Dlookup(Field1, Table1, YourID=1)
Value2 = Dlookup(Field2, Table1, YourID=1)
Value3 = Dlookup(Field3, Table1, YourID=1)

Instead one should use a Select and recordset:
Dim rs as dao.recordset
set rs = currentdb.openrecordset (" Select Field1, Field2, Field3 from Table1 where YourID = 1)
Value1 = rs!Field1
Value2 = rs!Field2
Value3 = rs!Field3

set rs = nothing

Considering that each DLookup is basicaly a query in itself, I am sure you can see the efficiency here. Also 'real' queries can be so much more readable, usable, etc... depending on your need(s), i.e. Group by or sort or what ever.
 
If you just use HH:NN your set :)

The problem with DLookup is if you use it multiple times
Value1 = Dlookup(Field1, Table1, YourID=1)
Value2 = Dlookup(Field2, Table1, YourID=1)
Value3 = Dlookup(Field3, Table1, YourID=1)

Instead one should use a Select and recordset:
Dim rs as dao.recordset
set rs = currentdb.openrecordset (" Select Field1, Field2, Field3 from Table1 where YourID = 1)
Value1 = rs!Field1
Value2 = rs!Field2
Value3 = rs!Field3

set rs = nothing

Considering that each DLookup is basicaly a query in itself, I am sure you can see the efficiency here. Also 'real' queries can be so much more readable, usable, etc... depending on your need(s), i.e. Group by or sort or what ever.

Once again you are absolutely right. I can see the deference. Luckily I use the Dlookup function only to get one field each time and not multiple Dlookups for many fields. Till now works perfectly. Nevertheless I think I should try to use recordset instead of the Dlookup function. The truth is that I have never used the Dao recordset and I am far from familiar with it. If you have any links to articles like "dao recordset for dummies" I'd be more than happy to read. I've read the Access' help but wan't that helpfull.
 
Don't be afraid of recordsets. They are really quite simple.
The big picture:
http://msdn.microsoft.com/en-us/library/bb177501.aspx

Basics on how to open one:
http://msdn.microsoft.com/en-us/library/bb243019.aspx

I haven't looked at what you are tring to achieve but another way to retreive values for a bunch of textboxes is to impliment them as bound controls in an unlinked subform with a recordsource query that returns a single record. It is a very tidy fully self-contained construct especially good for showing summaries of the database.
 

Users who are viewing this thread

Back
Top Bottom