Time confusion

[domaze]

Hello!

i think i have a major problem with time values.
i really cannot understand why running this code in sql

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

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

ps: i also had problems in other situations and other tables where a lesson started before 12:00 and ended after that time (ie 11:00 to 12:30). there was no problem when a time started AND ended both before or after 12:00.

i hope that what i wrote above is readable...

 

[PNGBill]

Re: Time comfusion

If you are having problems with time after 12 noon, maybe your pc's system time is causing a problem - is it set to 24hrs and not am/pm??

Can you confirm the sql in question works for times before noon??

 

[Galaxiom]

Re: Time comfusion

Shouldn't matter about the PC time settings as the query is a standard sql format.

Your time value may have a date component that is not showing because it is in time format which suppresses the date component.

A test for end_time > 1 should reveal such anomalies.
Otherwise change the format of end_time to General Date and you will be able to see the full value.

 

[namliam]

Re: Time comfusion

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

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

13:00:01 is indeed greater than 13:00:00 which #13:00# is...

Also #13:00# can be interperted as 13 mins instead of 13 hours, which may be the root cause of access "breaking" you are allowing it to think for you.

try using #13:00:00# and #14:30:00#

 

[domaze]

Re: Time comfusion

First of all i want to thank you all for your replies.
I appologize for the delay in my response but there were important things that kept me away from my pc.

If you are having problems with time after 12 noon, maybe your pc's system time is causing a problem - is it set to 24hrs and not am/pm??

Can you confirm the sql in question works for times before noon??

I didn't try to change the system's time beacause i want the proggram to work on any system no matter the settings, plus another user answered that this is a standard sql format.

Further more please help before i go crazy i testet the following:

SELECT *
FROM lessons
WHERE [date_math]=#29/05/2008# And [end_time]> #13:0:0# ;

This returns the record i mentioned

BUT this:

SELECT *
FROM lessons
WHERE [date_math]=#29/05/2008# And [end_time]>= #13:0:01# ;

returns NO RECORDS (!!!)
WHY(???)

 

[domaze]

Re: Time comfusion

13:00:01 is indeed greater than 13:00:00 which #13:00# is...

Also #13:00# can be interperted as 13 mins instead of 13 hours, which may be the root cause of access "breaking" you are allowing it to think for you.

try using #13:00:00# and #14:30:00#

many thanks for the response.
i tried #13:00:00# but failed again please read my previous post

 

[boblarson]

Re: Time comfusion

Your life would be much more simplified if you stored the date AND time TOGETHER.

 

[domaze]

Re: Time comfusion

Your life would be much more simplified if you stored the date AND time TOGETHER.

Thanks for the reply.

I couldn't aggree more with you. In order to test what you're saying I copied the table and in the new table I added new fields General date for start_time and end_time. using these the query works as exactly it should.
The only problem is that its very difficult to me to use GeneralDate fields in the forms where the users add data becase there are many parameters in the before/ after update of the fields. the forms i use are bound forms and turned out to be extremely difficult to me to use one field for date and times.
So you think my case is a dead end?

 

[PNGBill]

Re: Time comfusion

Can you have a query combine the two input fields ? so you have Your Input Fields, for data entry and you have your Combined Field, for your select query work. As we do for Full Name.

 

[domaze]

Re: Time comfusion

Can you have a query combine the two input fields ? so you have Your Input Fields, for data entry and you have your Combined Field, for your select query work. As we do for Full Name.

thaks again for the response.
i guess this is a good idea. so you say that i add 2 new fields in the table and i update them in the before or after update of the form. im i correct?

I realy would like to avoid this solution because it's going to be time consuming due to the fact that there are more than 5 different forms to input data in this table, but as long this is the only solution I should go with it.

 

[PNGBill]

Re: Time comfusion

I understood your problem was not Data Entry but in using the data later in a select query.

If so, what I suggest is leave your data entry as is but when you select your query will first concatenate / change the data into a form that can then be used by your other queries.

As in FullName. Data input is FirstName, LastName but when you do queries you often make a new field, FullName: [FirstName]&" "&[LastName]
and this new field can then be used in queries.
The field is not stored, just created and used.

I haven't tried this with dates but I am sure you can manipulate a date or DateTime field.

You can extract part of a datetime field if you want just the date or just the time and then use this.

Once you have the query done to do this then that query can be used by any number of other queries.

 

[PNGBill]

Re: Time comfusion

Just to be clear on this, the queries can all be done instantly on your current form.
If your form has a field that allows for date/time input and then say a button that brings up another form that uses this data, then the new form will be based on a query but this query will be based on another query that uses the data from your form. You may be able to build all this into one query but I suggest first try and make a query that will change your current table data into data that you know will work for you.
Then if you can't build this into your new form's query (sub query) then just have the new forms query refer to this new query.
Really makes no difference unless your query is processing data for millions of records.
We have such form activities that require up to 6 queries to be done and you don't notice any loss of speed when 60,000 records are processed.

 

[domaze]

Re: Time comfusion

Just to be clear on this, the queries can all be done instantly on your current form.
If your form has a field that allows for date/time input and then say a button that brings up another form that uses this data, then the new form will be based on a query but this query will be based on another query that uses the data from your form. You may be able to build all this into one query but I suggest first try and make a query that will change your current table data into data that you know will work for you.
Then if you can't build this into your new form's query (sub query) then just have the new forms query refer to this new query.
Really makes no difference unless your query is processing data for millions of records.
We have such form activities that require up to 6 queries to be done and you don't notice any loss of speed when 60,000 records are processed.

Thanks for your time.
I can see what you say. The time consumption I mentiond was for me to change the code for input in all these forms and not speed loss.
The select query in fact is a Dlookup function I call in the code to check some things. This is what complicates the situation. The fact is that the only way to go round this situation is to use the DateTime type variable
Your replies were very helpfull. I'll see what I can manage.
Thanks again

 

[domaze]

Re: Time comfusion

OK 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!

 

[Brianwarnock]

Re: Time comfusion

Did you use " " thus creating a string ie text or # # for a datetime field


Brian

 

[Brianwarnock]

Re: Time comfusion

Assuming that your fields are clean date and time fields and not Date/time formatted only to show the parts you want to see the you can do all of this on the fly in the query

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

becomes
Where [date_math]+[end_time] between #29/5/2008 13:00:00# AND #29/5/2008 14:30:00#

Brian

 

[domaze]

Re: Time comfusion

Assuming that your fields are clean date and time fields and not Date/time formatted only to show the parts you want to see the you can do all of this on the fly in the query

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

becomes
Where [date_math]+[end_time] between #29/5/2008 13:00:00# AND #29/5/2008 14:30:00#

Brian

OK i admit it. I'm a fool. i did it the wrong way and i created strings to compare and there was no luck of course. I understand what you're saying and you are right of course.
The thing that i think that is not right in the example with between is that between includes the edges (13:00 and 14:30) i want to be > 13:00 and <= 14:30.

Anyway. I think i am drawning with this problem because there must be major changes in my code. i use these queries in many functions of my code.

I will present the problem exactly as it is hoping that this will help you help me.

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.

Thank you for your help it is much appreciated!

 

[namliam]

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

We will thank you to use the code tags and formatted code when you post code to the forum.

Please be so kind to validate your data in your table, make a query and for each date and time field use this formatting:
Format(yourDateField, "YYYYMMDD HH:NN:SS")
Format(yourTimeField, "YYYYMMDD HH:NN:SS")

Double check and make sure that your date fields are date only (i.e. 201007 00:00:00) and your time fields are time only ( i.e. 18991230 104457 )

You can do this visually or using:
Right(Format(yourDateField, "YYYYMMDD HH:NN:SS"),8) <> "00:00:00"
Left(Format(yourTimeField, "YYYYMMDD HH:NN:SS"),8) <>
"18991230"
Make sure to set each where on an OR not an AND

 

[domaze]

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

We will thank you to use the code tags and formatted code when you post code to the forum.

Please be so kind to validate your data in your table, make a query and for each date and time field use this formatting:
Format(yourDateField, "YYYYMMDD HH:NN:SS")
Format(yourTimeField, "YYYYMMDD HH:NN:SS")

Double check and make sure that your date fields are date only (i.e. 201007 00:00:00) and your time fields are time only ( i.e. 18991230 104457 )

You can do this visually or using:
Right(Format(yourDateField, "YYYYMMDD HH:NN:SS"),8) <> "00:00:00"
Left(Format(yourTimeField, "YYYYMMDD HH:NN:SS"),8) <>
"18991230"
Make sure to set each where on an OR not an AND

I appologize for the way i presented the code.
I tryied what you're suggesting. All the data in the table seem to be fine.
But still there is the... "bug"

 

[domaze]

furthermore, i run some üseless code and i found the results really interesting...

in the following code the start time is a DATE var that has the value 1:00:00 pm and the dlookup hits again the same time (1:00:00 pm) from a record in the table (I checked with the debuger)

in the debug mode all values in all rows (Adat - Bdat, a - b, ect) seem equal, but yet...

a = DLookup("[end_time]", "[lessons]", "[id] = 1150")
b = starttime
Adat = CDate(a)
Bdat = CDate(starttime)
Adbl = CDbl(a)
Bdbl = CDbl(b)
Afrm = Format(a, "Short Time")
Bfrm = Format(b, "Short Time")
Afr1 = "#" & Format(a, "Short Time") & "#"
Bfr1 = "#" & Format(b, "Short Time") & "#"
If a = b Then z = 0 Else z = 1      ' z = 1 
If Adat = Bdat Then Zdat = 0 Else Zdat = 1       ' Ddat = 1 
If Adbl = Bdbl Then Zdbl = 0 Else Zdbl = 1       ' Ddbl = 1 
If Afrm = Bfrm Then Zfrm = 0 Else Zfrm = 1       ' Dfrm = 0
If Afr1 = Bfr1 Then Zfr1 = 0 Else Zfr1 = 1       ' Dfr1 = 0
If a = Bdat Then q1 = 0 Else q1 = 1       ' q1 = 1 
If a = Bdbl Then q2 = 0 Else q2 = 1       ' q2 = 1 
If a = Bfrm Then q3 = 0 Else q3 = 1       ' q3 = 1 
If a = Bfr1 Then q4 = 0 Else q4 = 1       ' q4 = 1
If Adat = Bdbl Then qd2 = 0 Else qd2 = 1       ' qd2 = 1 
If Adat = Bfrm Then qd3 = 0 Else qd3 = 1       ' qd3 = 1 
If Adat = Bfr1 Then qd4 = 0 Else qd4 = 1       ' qd4 = 1
If Adbl = Bfrm Then qb3 = 0 Else qb3 = 1       ' qb3 = 1 
If Adbl = Bfr1 Then qb4 = 0 Else qb4 = 1       ' qb4 = 1
If Afrm = Bfr1 Then qf1 = 0 Else qf1 = 1       ' qf1 = 1

I don't know if this is should be like that but the results look very strange to me...

Guess I am REALLY Time Confused....