Filter Forms

[jgd12345]

Hi, I'm new to filter forms. I thought I had enough to get me along with Access but then I came across the problem of having to filter a field between a specific range. Here's an example:

Say I wish to create a filter form filtering the house price range (minimum house value < price < maximum house value). Leaving the price range blank defaults to any price range.

How would I go about doing this? If anyone could help i'd be very greatful. Thanks

 

[jgd12345]

Hi, forgot to put specific table details. Say the table name is "Houses" and the field is "Price". Thanks

 

[jgd12345]

Ah don't worry got it worked out now. Cheers for your help.

 

[jgd12345]

Hi how would I do it so that if they enter nothing as the minimum price range it defaults to 0 and if they enter nothing for the maximum price range it defaults to the maximum price.

Cheers

 

[jgd12345]

Hi, I found a different way of doing it using VBA. I had everything working until I tried to input the default value bit if the txtminimumPrice field is left empty. The code below returns an error. If anyone could tell me how I could fix it I'd be greatful. Thanks

Private Sub cmdFind_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim minimumPrice

Rem Declaring the Formname
stDocName = "frmHouses"

If Me![txtMinimumPrice] = "" Then
minimumPrice = 0
Else
minimumPrice = Me![txtMinimumPrice]
End If

Rem Filtering Maximum and Minimum Prices
stLinkCriteria = "[H_PRICE] >=" & [minimumPrice] & "and [H_PRICE] <=" & Me![txtMaximumPrice]

DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

 

[randy700]

Try removing the brackets [] from miminumPrice in the code where you set your stLinkCriteria. The brackets will make Access think you're referring to a control or field name when you actually want a variable.

Another solution would be to set the txtMinimumPrice value and use it in the stLinkCriteria...
If Me![txtMinimumPrice] = "" Then
txtMinimumPrice = 0
End If


Randy

 

[jgd12345]

Hi, na it didn't work. I also picked up that for some reason when I try to check if the field is empty by doing "If Me![txtMinimumPrice] = "" Then" it doesn't work when the field is actually empty.

 

[Kebabmeister]

Hi

You might want to try this:

If IsNumeric(YourField)=False Then
YourVariable = 0
Else
YourVariable = YourField
End If

Kebabmeister

 

[jgd12345]

Cheers it worked. Another problem is querying whether or not a record is found. ie if nothing is found it displays a message box saying so otherwise it loads DoCmd.OpenForm stDocName, , , stLinkCriteria.

I'd be greatful for your help oncemore. Thanks

 

[Kebabmeister]

Dim Result As Integer

If me.recordsetclone.recordcount = 0 Then
Your Null Recordset Code Here
Else
Process Records Here
End If


Kebabmeister

 

[jgd12345]

Got an error (run-time 7951). "You entered an expression that has an invalid reference to the RecordsetClone property.

 

[jgd12345]

So no idea?? Here's what I got:

Private Sub cmdFilter_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim Result As Integer

Rem Declaring the formname
stDocName = "frmHouses"

Rem Filtering Minimum Price
If IsNumeric(txtMinimumPrice) = True Then
stLinkCriteria = "[H_PRICE] >=" & Me![txtMinimumPrice]
Else
Rem Added to avoid a possible error
stLinkCriteria = "[H_PRICE] >= 0"
End If

Rem Filtering Maximum Price
If IsNumeric(txtMaximumPrice) = True Then
stLinkCriteria = stLinkCriteria & "and [H_PRICE] <=" & Me![txtMaximumPrice]
End If

Rem Filtering Region
If IsNull(cboRegion) = False Then
stLinkCriteria = stLinkCriteria & "and [H_REGION] =" & "'" & Me![cboRegion] & "'"
End If

Rem Filtering number of Rooms
If IsNumeric(txtRooms) = True Then
stLinkCriteria = stLinkCriteria & "and [H_BEDS] =" & Me![txtRooms]
End If

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox ("No such House")
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End Sub

 

[PHV]

Seems like lack of space before the "and"s when you build the stLinkCriteria string.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Kebabmeister]

What version Access?