date and time query

[domaze]

hello,
this is my first post in this forum.

I have the following problem and i really need some help because i am about
to go crazy.

I cannot understand why running this code in sql

select * from lessons where
[date_math] = #29/5/2008# AND ([end_time] > #13:00:00# AND [end_time] <= #14:
30:00#)

results in a row like this:

id:1150
date_math: 29/5/2008
start_time: 11:30
end_time: 13:00 (!!!)
less_name: my_name

The most crazy thing is that the following query returns no results
select * from lessons where
[date_math] = #29/5/2008# AND ([end_time] > #13:00:01(!!!!!!!)# AND [end_time]
<= #14:30:00#)

and of course the explanation marks in the parenthesis are not really in my
statement

 

[Marshall Barton]

I have the following problem and i really need some help because i am about
to go crazy.

I cannot understand why running this code in sql

select * from lessons where
[date_math] = #29/5/2008# AND ([end_time] > #13:00:00# AND [end_time] <= #14:
30:00#)

results in a row like this:

id:1150
date_math: 29/5/2008
start_time: 11:30
end_time: 13:00 (!!!)
less_name: my_name

The most crazy thing is that the following query returns no results
select * from lessons where
[date_math] = #29/5/2008# AND ([end_time] > #13:00:01(!!!!!!!)# AND [end_time]
<= #14:30:00#)

Date/Time values are internally represented as Doubles with
the fractional part being the part of a day. It appears
that your end time value is a very small fraction (eg.
..00000000000001) greater than #13:00:00#. I don't think I
have ever seen that happen with a pure time value, but it is
not unusual if you ever do any arithmetic on it or the time
value comes from some other program or database.

FYI, your date_math value is not correct. In this case
Access corrected it for you, but if you used a date like
#9/5/2008# the date would be 5 Sept 2008. When you use #
around a date, it must be in an unambiguous style or in USA
style #mm/dd/yyyy#. To avoid that kind of confusion, I
usually use #yyyy-mm-dd#

This confusion is compounded by Access using your Windows
date style when it converts a string to a date or when it
auto formats a date for display.

 

[domaze]

Date/Time values are internally represented as Doubles with

the fractional part being the part of a day. It appears
that your end time value is a very small fraction (eg.
.00000000000001) greater than #13:00:00#. I don't think I
have ever seen that happen with a pure time value, but it is
not unusual if you ever do any arithmetic on it or the time
value comes from some other program or database.

FYI, your date_math value is not correct. In this case
Access corrected it for you, but if you used a date like
#9/5/2008# the date would be 5 Sept 2008. When you use #
around a date, it must be in an unambiguous style or in USA
style #mm/dd/yyyy#. To avoid that kind of confusion, I
usually use #yyyy-mm-dd#

Thank you very much for the reply.
I can see what you are suggesting with date but the date is not the problem.
although you are right about the date strings I usually overcome situations
like this by using format function. My painfull problem is Time. Time is
added ONLY in the form with a bound form and I certainly never do any
arithmetic in the time value. That's the "bug" I guess... That is why i'm
going crazy...

 

[Marshall Barton]

Thank you very much for the reply.
I can see what you are suggesting with date but the date is not the problem.
although you are right about the date strings I usually overcome situations
like this by using format function. My painfull problem is Time. Time is
added ONLY in the form with a bound form and I certainly never do any
arithmetic in the time value. That's the "bug" I guess... That is why i'm
going crazy...

I tried every way I can think of to reproduce (A2003) it and
could not get that effect. I don't know of anything that I
can add to what I said earlier.

maybe you can use the Immediate window to triple check the
value in the form text box. See if you get the same ouput
from these two lines:

?CDbl(Forms!yourform.thetextbox)
and
?CDbl(#13:00:00#)

 

[domaze via AccessMonster.com]

[quoted text clipped - 18 lines]

arithmetic in the time value. That's the "bug" I guess... That is why i'm
going crazy...

I tried every way I can think of to reproduce (A2003) it and
could not get that effect. I don't know of anything that I
can add to what I said earlier.

maybe you can use the Immediate window to triple check the
value in the form text box. See if you get the same ouput
from these two lines:

?CDbl(Forms!yourform.thetextbox)
and
?CDbl(#13:00:00#)

Thanks again for the reply
i am using Access 2007
i tried this an the result is on both 0.666666666666666666667
maybe there's a problem with rounding numbers....
But...
Now this is really hard for me to understand.
i was trying to fix the time comfusion by assing General date types in the
program and i fell onto this problem
i get the values i present here in the debug mode.

starttime = "29/05/2008 11:30:00 am"
endtime = "29/05/2008 1:00:00 pm"

in the code there is a line like this
if endtime <= starttime then msgbox("Wrong Parameters")

the funny thing is that i see the message box...
Now this i cannot explain.

Please help!

 

[Marshall Barton]

Date/Time values are internally represented as Doubles with
the fractional part being the part of a day. It appears

[quoted text clipped - 18 lines]

arithmetic in the time value. That's the "bug" I guess... That is why i'm
going crazy...

I tried every way I can think of to reproduce (A2003) it and
could not get that effect. I don't know of anything that I
can add to what I said earlier.

maybe you can use the Immediate window to triple check the
value in the form text box. See if you get the same ouput
from these two lines:

?CDbl(Forms!yourform.thetextbox)
and
?CDbl(#13:00:00#)

i am using Access 2007
i tried this an the result is on both 0.666666666666666666667
maybe there's a problem with rounding numbers....

That's the same value I got when I did that.

Any rounding here is only done as part of displaying the
value and should not have any effect on the value itself.

But...
Now this is really hard for me to understand.
i was trying to fix the time comfusion by assing General date types in the
program and i fell onto this problem

General Date is a display Format and has no effect on the
type or value. OTOH, a date style format will influence
Access if it thinks it has to convert a text string to a
date value. Is it possible that the start/end time fields
in the table are Text fields?

i get the values i present here in the debug mode.

starttime = "29/05/2008 11:30:00 am"
endtime = "29/05/2008 1:00:00 pm"

in the code there is a line like this
if endtime <= starttime then msgbox("Wrong Parameters")

the funny thing is that i see the message box...

Now it is really starting to sound like your fields are Text
fields so the comparison is a text comparison. It is
imperative that date/time fields in the table are Date data
type.

I have no idea how the date came into the picture. I
thought these values are were only times. If they were only
times, without a date part, they would display as just the
time or have the date 30 Dec 1899. I guess another
important point is the question of how you are entering the
start/end times into the fields?

 

[domaze via AccessMonster.com]

[quoted text clipped - 17 lines]

i tried this an the result is on both 0.666666666666666666667
maybe there's a problem with rounding numbers....

That's the same value I got when I did that.

Any rounding here is only done as part of displaying the
value and should not have any effect on the value itself.

But...
Now this is really hard for me to understand.
i was trying to fix the time comfusion by assing General date types in the
program and i fell onto this problem

General Date is a display Format and has no effect on the
type or value. OTOH, a date style format will influence
Access if it thinks it has to convert a text string to a
date value. Is it possible that the start/end time fields
in the table are Text fields?

i get the values i present here in the debug mode.

[quoted text clipped - 5 lines]

the funny thing is that i see the message box...

Now it is really starting to sound like your fields are Text
fields so the comparison is a text comparison. It is
imperative that date/time fields in the table are Date data
type.

I have no idea how the date came into the picture. I
thought these values are were only times. If they were only
times, without a date part, they would display as just the
time or have the date 30 Dec 1899. I guess another
important point is the question of how you are entering the
start/end times into the fields?

You have enlinghten my foolishness and I appoligize for being fool. you are
absoluteley right about the conversion. I did it with a realy wrong way and i
was comparing text the LAST time.

The values are entered in the table with a bound form.
In the after update of the form I call a procedure that has a dlookup that
checks if the values are valid (checks if there is a overlap with another
lesson).
this dlookup returned a value that it shouldn't that's why i did the queries
i mentioned.
the code goes like this:

Private Sub Form_AfterUpdate()
My_Red = Find_Symptosis(0, Me.aa, Me.Theory, Me.date_math, Format(Me.
start_time, "Short Time"), Format(Me.end_time, "Short Time"), Me.hours, Me.
BARCODE, Me.teacher, GLOBAL_Vehicle_Barcode, Me.Canceled, Me.unwritten)

if My_Red > 0 then msgbox("Overlap!!!")
end sub

Public Function Find_Symptosis(ByVal idr, aa1, Theor, Datemath, starttime,
endtime, Hour, BARC, Teach, veh, Canc, Unwrit) As Integer

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")
& "#" & "))"

If Not IsNull(DLookup("[ID]", "[lessons]", CriteriaStr)) Then Sympt =
DLookup("[ID]", "[lessons]", CriteriaStr)

if Sympt > 0 then Find_Symptosis = sympt
end function

There is a record in the table that has date_math: 29/05/2008 start_time: 11:
30 and end_time: 13:00
when i enter a record with the same date and start_time: 13:00 and end_date
14:30 the dlookup finds a record and tells that there is an overlap. This
problem occurs only if the lesson that already exist in the table has
start_time before midday and end_time after miday. if BOTH start_time and
end_time is before OR after midday there is no problem. For example if there
is a record in the table with start_date 08:30 and end time 10:00 and then i
add a record with start_time: 10:00 and end time 11:30 the Dlookup will
result null (that's good).

These are the parts of my code. in the table the the date_math is declared
date/time with short date format and both start_time and end_time are
date/time with short time format.

in the form the fields are bound and i have an input mask to enter time and
date.

I wrote all these because they may help you help me.

Thank you a lot for your help. It is very highly appreciated!