[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Convert Text to Time and Filter

Posted on 2007-10-05
Medium Priority
Last Modified: 2011-09-20
I have a fieldin a table with a datatype of Text that I can use the Format([CollectTime],"hh:nn:ss") function to successfully text to time.  However I need to filter this data in the same query.

For example >=#15:15:00#

When I do I do not get results even though I can see some would qualify.

Help this is urgent!
Question by:gpotenza
  • 2
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20024407
gpotenza said:
>>datatype of Text

If the data type is text, then why are you using a data type of datetime for the filter comparison?

Compare text with text and datetime with datetime; when you try to mix them up you can get
unexpected results.

Author Comment

ID: 20024434
The data in the field represents a time that I need to filter by a timespan. (for example from 15:00 to 20:00)  How would I do that if the field datatype is text?
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20024481
If the text is stored as hh:mm, then try:

[TimeField] Between "15:00" And "20:00"

or if that does not work:

[TimeField] >= "15:00" And [TimeField] <= "20:00"

Why are you storing this as text and not as datetime?
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 20025028

cdate([collectTime]) between #15:15# and #20:00#

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question