How do I use operators with Ken Getz's Filter by Form

  • Thread starter Thread starter bastraker via AccessMonster.com
  • Start date Start date
B

bastraker via AccessMonster.com

Hello All

First just let me say this site is fantastic!!! Many helpful topics and some
truly generous people.

OK on to my issue. I'm a newbie thrown into the deep end of the pool. I
downloaded Ken's Filter by form "creator" and the thing works like a champ, I
even changed some of the text boxes to combo boxes propogated with distinct
values from my table, a big deal for me.

The trouble I'm having is what syntax am I supposed to be using for operators
i.e. NOT. From the read me that comes with the database I read "Finally, it
attempts to determine whether you've entered an operator (<, >, =, Like, Not,
In) and if so, it leaves the value untouched." I've tried every combination I
can think of with no luck. Can anyone throw me a life line?

Thanx in advance for any assistance
D
 
bump - Does anyone have any insight? Can the query by form not handle
operators even though the readme says it can?
 
Hi. You have the source code with the download, so you can study how
it's working. Its support for operators is very limited. The IsOperator
function in the basBuildSQL module attempts to determine if what it's
found in the field is in fact an operator by doing simple string
parsing (it's not very smart, in other words):

Private Function IsOperator(varValue As Variant) As Boolean

' Return a logical value indicating whether a
' value passed in is an operator or not.
' This is NOT infallible, and may need correcting.

Dim strTemp As String

strTemp = Trim$(UCase(varValue))
IsOperator = False

' Check first character for <,>, or =
If InStr(1, "<>=", Left$(strTemp, 1)) > 0 Then
IsOperator = True
' Check for IN (x,y,z)
ElseIf ((Left$(strTemp, 4) = "IN (") And (Right$(strTemp, 1) =
")")) Then
IsOperator = True
' Check for BETWEEN ... AND ...
ElseIf ((Left$(strTemp, 8) = "BETWEEN ") And (InStr(1, strTemp, "
AND ") > 0)) Then
IsOperator = True
' Check for NOT xxx
ElseIf (Left$(strTemp, 4) = "NOT ") Then
IsOperator = True
' Check for LIKE xxx
ElseIf (Left$(strTemp, 5) = "LIKE ") Then
IsOperator = True
End If
End Function

In the code that builds up the WHERE clause for your filter, the code
calls the IsOperator method like this:

If IsOperator(varFieldValue) Then
strTemp = strTemp & " " & varFieldValue
Else

If it finds that you've entered an operator, it simply concatenates the
field name and the field value that you've entered, including the
operator. If it doesn't think you've entered an operator, it attempts
to fix up quotes, # around dates, and so on:

' One could use the BuildCriteria method here,
' but it's not as flexible as I'd like to
' be. So, this code does all the work manually.

Select Case intFieldType
Case dbBoolean
' Convert to TRUE/FALSE
strTemp = strTemp & " = " & CInt(varFieldValue)
Case dbText, dbMemo
' Assume we're looking for anything that STARTS with
the text we got.
' This is probably a LOT slower. If you want direct
matches
' instead, use the commented-out line.
' strTemp = strTemp & " = " & QUOTE & varFieldValue &
QUOTE
strTemp = strTemp & " LIKE " & QUOTE & varFieldValue &
"*" & QUOTE
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle,
dbDouble
' Convert to straight numeric representation.
strTemp = strTemp & " = " & varFieldValue
Case dbDate
' Convert to #date# format.
strTemp = strTemp & " = " & "#" & varFieldValue & "#"
Case Else
' This function really can't handle any of the other
data types. You can
' add more types, if you care to handle them.
strTemp = vbNullString
End Select

So, if you want to include an operator, you must enter the text exactly
as it would appear in a WHERE clause, all by yourself. The code simply
passes whatever you type directly onto the Access query engine. For
example, using the sample form that comes with the download, if you
want all the customers whose names begin with a letter greater than or
equal to "M", you'd have to enter:
"M"
Click to expand...

in the company name field. If you just wanted companies whose name
begins with M, you could enter

M

into the field, and the code would add the quotes around the letter for
you.

Like the code says, it's not very smart about operators. If you're
finding that it's not working for you, and you're putting in the
correct WHERE clause syntax, then you may need to modify the code in
the BuildSQLString function to meet your needs. -- Ken
 
Thanx Ken, very nice of you to chime in.

As I said before I am a complete newbie to this so altering the code is gonna
take quite awhile, I can barely understand what I'm working with now let
alone adding to it. I got the greater than less than operators to work but
that's about it. Guess I'm gonna have to burn some midnight oil and learn the
code syntax. Thanx again Ken for the SWEET qbf creator and for taking the
time to answer my question.
 
Back
Top