Prev: Print Order Sort - 3 sections per page - to be cut and stacked
Next: Sum and DateDiff
From: domaze on 5 Jun 2010 18:04
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

From: Marshall Barton on 5 Jun 2010 18:21
domaze wrote:
>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.

--
Marsh
MVP [MS Access]
From: domaze on 5 Jun 2010 19:40
>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...

From: Marshall Barton on 5 Jun 2010 22:42
domaze wrote:

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


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

--
Marsh
MVP [MS Access]
From: domaze via AccessMonster.com on 6 Jun 2010 10:48
Marshall Barton wrote:
>>>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#)
>

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!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201006/1

 |  Next  |  Last
Pages: 1 2
Prev: Print Order Sort - 3 sections per page - to be cut and stacked
Next: Sum and DateDiff