Search Forms

[smalty]

I have a form with several text boxes which are used to build a search filter. I have used this many times and it works perfectly. However I have a need to allow the user to search for a value that is >= to an entered amount and I cant get the correct syntax in the code.

The code (apologies that I can't remember who posted it originally) is the following.
Private Function BuildFilter() As Variant
Dim varWhere As Variant
varWhere = Null

If Me.qExpID > "" Then
varWhere = varWhere & "[ExpID] LIKE """ & Me.qExpID & "*"" And "
End If

If Me.qDateF > "" Then
varWhere = varWhere & "[Date] >=#" & Format(Me.qDateF, "yyyy-mm-dd") & "# And "
End If

If Me.qDateT > "" Then
varWhere = varWhere & "[Date] <=#" & Format(Me.qDateT, "yyyy-mm-dd") & "# And "
End If

If Me.qVendor > "" Then
varWhere = varWhere & "[Vendor] LIKE """ & Me.qVendor & "*"" And "
End If

If Me.qExpCat > "" Then
varWhere = varWhere & "[Exp Category] LIKE """ & Me.qExpCat & "*"" And "
End If

If Me.qExpItem > "" Then
varWhere = varWhere & "[Item] LIKE """ & Me.qExpItem & "*"" And "
End If

If Me.qTransType > "" Then
varWhere = varWhere & "[Trans] LIKE """ & Me.qTransType & "*"" And "
End If

If Me.qTransRef > "" Then
varWhere = varWhere & "[Trans Ref No] LIKE """ & Me.qTransRef & "*"" And "
End If

If Me.qAmount > "" Then
varWhere = varWhere & "[Amount] LIKE """ & Me.qAmount & "*"" And "
End If


If Me.qDocRec > "" Then
varWhere = varWhere & "[Document Receipt] LIKE """ & Me.qDocRec & "*"" And "
End If


' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " And " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function

The field I need the >= search for is [Amount]

Could someone please advise if this is possible or do I need to start afresh with something completely different.

Thanks

Smalty

 

[Andrzejek]

If someone types 50 in the Amount, do you want any record with 50 (550, 3502, etc, that's what your LIKE does now) to be found, or you want to change your logic to find Amount >= 50?



Have fun.

---- Andy

 

[lionelhill]

No, you almost certainly do it not like you put in the code, but very much like you put in the explanation:
...."[Amount] <= " & Me.qAmount.....
Amount is a value, not a string. In a way, it will behave more like your dates than your strings.

 

[smalty]

OK Have solved it myself

 

[smalty]

It was

If Me.qAmount > "" Then
varWhere = varWhere & "[Amount] >= " & Me.qAmount & " And "
End If

Thanks anyway for the advice

Smalty

 

[Andrzejek]

Just a suggestion here:

To get rid of this part of your code:
[pre][green]
' strip off last "AND" in the filter[/green]
If Right(varWhere, 5) = " And " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
[/pre]

You may do this (put AND at the beginning):

Dim varWhere As [blue]String[/blue]
varWhere = " [blue]WHERE 1 = 1[/blue] "

If Me.qExpID > "" Then
    varWhere = varWhere & " [blue]AND[/blue] [ExpID] LIKE " & Me.qExpID & "*"
End If
...

Have fun.

---- Andy

 

[missinglinq]

Your post about what was wrong isn't really very clear, but it should be noted that if your statement

If Me.qAmount > "" Then

and all of the others like it is intended to determine if the Field has a Value in it, it's going to fail, most of the time, because you're testing for a Zero-Length String ("") and most of the time, in Access, when a Field is 'empty,' it contains a Null (which is what you need to test for) not a Zero-Length String!

Instead of

If Me.qAmount > "" Then

for these type statements, use something like

If Nz(Me.qAmount,"") <> "" Then

The Nz() function checks a Field to see if it is Null, and if it is, assigns another Value to it, in this case the aforementioned Zero-Length String.

So, if Me.qAmount is Null, the function assigns "" to it. And if Me.qAmount actually has a Zero-Length String in it, it has a Zero-Length String in it! So, in either case, the code immediately below

If Nz(Me.qAmount,"") <> "" Then

will only execute if there actually is data in Me.qAmount.

The Missinglinq

Richmond, Virginia

The Devil's in the Details!