Allowing to search using < and > (greater than and less than)

[Veus]

Hi,

Im trying to allow it so users can search records using < and > for the
date fields.

The search form works fine with = values however when i enter < or >
followed by a date
( < 28/04/06 for example ) it comes up with a syntax error. Ive printed
the debug for the string strWhere:

([name] Like "**") AND ([lastBookDate] = <25/04/06) AND

My code for the processing of this is:

Dim strWhere As String 'The criteria string.
Dim lngLen As Long
Dim strCapture As String
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.lastBookDate) Then

strCapture = Left(Me![lastBookDate], 2)

If strCapture = "> " Then
strWhere = strWhere & "([lastBookDate] > " &
Format(Me.Controls("lastBookDate"), conJetDate) & ") AND "
ElseIf strCapture = "<" Then
strWhere = strWhere & "([lastBookDate] < " &
Format(Me.Controls("lastBookDate"), conJetDate) & ") AND "
ElseIf strCapture = ">=" Then
strWhere = strWhere & "([lastBookDate] >= " &
Format(Me.Controls("lastBookDate"), conJetDate) & ") AND "
ElseIf strCapture = "<=" Then
strWhere = strWhere & "([lastBookDate] <= " &
Format(Me.Controls("lastBookDate"), conJetDate) & ") AND "
Else
strWhere = strWhere & "([lastBookDate] = " &
Format(Me.Controls("lastBookDate"), conJetDate) & ") AND "
End If
Debug.Print strWhere
End If

As you can see it automatically puts in an equals sign when I dont want
it, so this results in the syntax error ( = <28/04/06 isnt correct)
Are my if else statements setup correctly and also have i missed
something extremely obvious?

Thanks in advance.

 

[Albert D. Kallal]

Why not use:


If Not IsNull(Me.lastBookDate) Then

strDate = me!lastBookdate

if (instr(strDate,">") > 0) or (instr(strDate,"<") > 0) then
strCapture = Left(Me![lastBookDate], 2)
strDate = mid(strDate,3)
else
strCapture = " = "
end if

strDate = "#" & format(strDate ,conJetDate) & "#"

strWhere = strWhere & "([lastBookDate] " & _
strCapture & " " & stDate
Debug.Print strWhere
End If

The assumes you define a var called strDate....
and put in the orignal string....

 

[Veus]

Thanks, I made a few changes but its working.

Except that it returns the wrong dates, Access seems to think:

24/07/2006 < 25/04/2006

I think i saw some other people on here with similar problems.

Another issue is that it needs to be displayed < [DATE] however some of
my users may be lazy and just put <[DATE] is there a way to catch this
as at the moment it comes up with a syntax error if there is no space.

 

[Albert D. Kallal]

Except that it returns the wrong dates, Access seems to think:

24/07/2006 < 25/04/2006

Put back in your debug.print of the strwhere clause...and take a look....

(something is wrong) - make sure your users enter 4 digits for the year.....

in the debug window --
? format("25/04/06","\#mm\/dd\/yyyy\#")

#04/06/2025#

Note the above year....it is incorrect....

? format("25/04/2006","\#mm\/dd\/yyyy\#")
#04/25/2006#

You have to either train users to enter mm/dd/yy, OR THEY must enter 4
digits for the year....

I think i saw some other people on here with similar problems.

Another issue is that it needs to be displayed < [DATE] however some of
my users may be lazy and just put <[DATE] is there a way to catch this
as at the moment it comes up with a syntax error if there is no space.

Well, just put in some "if" code to check for ">[".
If you been able to code this much this far I would be at a loss as to why
this would be a difficult problem to check for?

However, before you start writing even more snippets of code here, why not
build a prompt form that takes away the typing part?
You will find your application MUCH more user friendly if you build a prompt
form.

Here is some example screen shots
http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The above is just somes ideas...

By presenting a user with calendar selections, you take away the ZILLION
things that you have to test for.

For example, you are worried about a tiny little space being entered by the
user. Actually, in your case the OMISSION of tiny space is our problem.
Further...what happens if the user puts in a space before the >
symbol?...you going to test for that too? Just how many zillions of things
do you want to test for?

So, do a debug-print for the date problem, make sure all of those text dates
are correctly formatted.
Put in a if test for the missing space....(and, also tests for extra
spaces....for each of the conditaions).
You could also pull all charaters up to the "[". However, you not said what
you plan to do if the [ is missing!

I would just build a nice prompt form, and even use a listbox for the =, >=,
<= (that way, the user selects this on the form...not types it). The list
box would have two columns....perhaps 3...

col1 col2
" > " " > greater then"
" < " " < less then"

etc....

I would hide the first column of the above list box (or comb box). Thus, the
user would select the condition on the form in addition to the date, or date
range. note that col2 in the above is a nice user friendly prompt text. Thus
new users would not have to be trained in Boolean logic, or even know what
syntax to use. And, as those example screen shots show, they are not only
much easier for the end user to use, but also takes away the ability of the
end user to type syntax errors (so, easier to use...and less code!!...that
don't happen much in the software developers world).