Prev: Print Order Sort - 3 sections per page - to be cut and stacked
Next: Sum and DateDiff
From: Marshall Barton on 6 Jun 2010 12:06
domaze via AccessMonster.com wrote:

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

--
Marsh
MVP [MS Access]
From: domaze via AccessMonster.com on 6 Jun 2010 18:35
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 - 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!

--
Message posted via http://www.accessmonster.com

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