Question

[zoup]

record server date time
158724 UnixWknd 5/8/2005 5:00
155932 rocu 5/1/2005 49:02

Why on a query set up to read this table and with criteria >=5:00 only return record 158724? when clearly 49:02 is bigger then 5:00.. any help is greatly appreciated..

 

[Jon K]

You were comparing text strings, not time.


You can't have a time greater than 23:59:59 on any date. Change 49:02 to a reasonable time in the table.

Then in table design, change the Time field from Text to Date/Time data type.

And put >=#5:00# in the query criteria.


Note Date and Time are the names of two built-in functions. It's better not to use them as field names.
.

 

[zoup]

correct.

But I am importing a text file into access. The txt file looks something like this:

client date date time
roc_bu 05/19/2005 05/19/2005 01:57:54

and when you import something like this access changes this time to like a 12:12:20 AM so I came up with a function to convert this time to minutes and hours. No seconds. Below is the code.


Public Function HoursAndMinutes(interval As Variant) As String
'***********************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'***********************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long

If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))

totalminutes = Int(CSng(interval * 1440)) ' 1440 = 24 hrs * 60 mins
minutes = totalminutes Mod 60

totalseconds = Int(CSng(interval * 86400)) ' 86400 = 1440 * 60 secs
seconds = totalseconds Mod 60

If seconds > 30 Then minutes = minutes + 1 ' round up the minutes and
If minutes > 59 Then hours = hours + 1: minutes = 0 ' adjust hours

HoursAndMinutes = hours & ":" & Format(minutes, "00")

End Function

But Its not letting me correctly search. All i want to do is search for these backup times. If I am being vague im sorry.. I just dont get why access wouldnt reconize these searchs

 

[The_Doc_Man]

You have asked this question twice, once in another thread under another topic. Please do not do that. It wastes our time.

When you do string comparisons, the rules are not the same as they would be for any other comparison, because string comparisons use something called "collating sequence" where as all numeric comparisons are "natural." Natural comparisons are intuitive. For a natural comparison, 5 < 10. For a STRING comparison, "5" > "10" because the first mis-matching character ends the comparison and defines the sense of the result. And in this case, "5" > "1" and the "0" never enters the picture.

To compound the problem, DATE is one of the worst possible data types to use for comparing intervals. In Access, a date field implies a number of days and fractions since a reference date. In techie terms, it introduces a natural bias to all date values. NEVER record time intervals using dates. Always convert them to some other numeric units like minutes, hours.fractions, or days.fractions

How you choose to DISPLAY a field is, of course, your call. But if you are going to do anything with time intervals, DON'T treat them as TEXT or as DATE - it just won't work right for you.

 

[Jon K]

correct.

But I am importing a text file into access. The txt file looks something like this:

client date date time
roc_bu 05/19/2005 05/19/2005 01:57:54

and when you import something like this access changes this time to like a 12:12:20 AM so I came up with a function to convert this time to minutes and hours. No seconds. Below is the code.

To remove the seconds from the imported Time field, you don't need to convert the times into text strings.


After importing the text file into a table, make sure the Time field is set as Date/Time data type. Then you can remove the seconds from the Time field with an Update Query like this:-

UPDATE [ImportedTable] SET [Time] = [Time]-CDate("0:0:" & DatePart("s",[Time]))

Now to show the Times as 1:57 and 0:12 in the table (instead of showing 1:57:00 AM and 12:12:00 AM), you can simply set the Format of the Time field as Short Time in table design.


In fact, you can search for something like >=#5:0# without using the Update Query to remove the seconds from the Time field (so long as the field is of Date/Time data type.) You can set the search criteria like this in query design view:-

Field: [Time]-CDate("0:0:" & DatePart("s",[Time]))

Show: uncheck

Criteria: >=#5:0#

Now only the hours and minutes in the Time field are searched as if the seconds were removed.

And since a Date/Time field is internally a numeric field, you can even directly search the Time field for >=#5:0# without using
-CDate("0:0:" & DatePart("s",[Time]))
.