Search by Time - Search Form 2.0 in Microsoft Access


	Back to Search Form 2.0 Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Search by Time

Teeka Leone

4 years ago

Hi Guys, so Adam resolved the IsActive Checkbox issue, but there's one more that was not addressed in this video...

How do you get a filter by time to work?  I have to be able to search tickets in the form by time range.  Example: I need to search say between 9 AM and 1 PM.

I tried without time value then with time value (but syntax may be wrong).  I tried datepart(h,n).  I usually get the syntax error red text, beep and debug error.

With the code below (and without using TimeValue), It doesn't error but shows NO results for those times when there clearly is hundreds.

If SchedTimeMinSearch <> "" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "SchedTime >=  #" & TimeValue(SchedTimeMinSearch) & "#"
    End If

    If SchedTimeMaxSearch <> "" Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & "SchedTime = > #" & TimeValue(SchedTimeMaxSearch) & "#"

not finding much on forums.

	Kevin Robertson @Reply 4 years ago
Your inequality is wrong in the last line of the second If block. It should be: `WhereStr = WhereStr & "SchedTime <= #" & TimeValue(SchedTimeMaxSearch) & "#"`

	Teeka Leone OP @Reply 4 years ago
Good catch Kevin, but that's not it. That was me being lazy and copying and pasting from the line above it, (SchedTimeMinSearch) and only changing name to max but not changing the operational sign along with it.

	Alex Hedley @Reply 4 years ago
What data type is `SchedTime` Can we get a screenshot of the Table in design view.

	Teeka Leone OP @Reply 4 years ago
Hi Alex. It is a SQL Server Table and data type is datetime. On the form, the control is formatted as medium time

	Teeka Leone OP @Reply 4 years ago

	Kevin Robertson @Reply 4 years ago
In SQL Server, use single quotes instead of '#'. `WhereStr = WhereStr & "SchedTime <= '" & TimeValue(SchedTimeMaxSearch) & "'"`

	Teeka Leone OP @Reply 4 years ago
Nope. Tried that before. Get datatype mismatch error

	Alex Hedley @Reply 4 years ago
Have you tried a hard coded value first directly in SSMS?

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Search Form 2.0.

Next Unseen

New Feature: Comment Live View