If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
date and time query
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 |
#2
|
|||
|
|||
date and time query
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] |
#3
|
|||
|
|||
date and time query
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... |
#4
|
|||
|
|||
date and time query
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] |
#5
|
|||
|
|||
date and time query
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/For...eries/201006/1 |
#6
|
|||
|
|||
date and time query
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] |
#7
|
|||
|
|||
date and time query
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 |
Thread Tools | |
Display Modes | |
|
|