Multiple field Querry issue

[Guest]

I have a query with 41 seperate fields 10 of those fields have criteria for
them. All of the fields are from a single table. The ten fields criteria
are based off a form where the user enters a value. My problem is that not
all the fields with criteria are used in every situation. For example I am
looking for a 10,000 square foot building in Ottawa County. Since I have a
Township field too it would return no records because it was left blank and
it will be looking for null values. I want to be able to have the querry
ignore a criteria if it is left blank in the form instead of it looking for
null values. The user cant just enter in data because in many cases the
values are unknown. Any help would be appreciated.

 

[Marshall Barton]

I have a query with 41 seperate fields 10 of those fields have criteria for
them. All of the fields are from a single table. The ten fields criteria
are based off a form where the user enters a value. My problem is that not
all the fields with criteria are used in every situation. For example I am
looking for a 10,000 square foot building in Ottawa County. Since I have a
Township field too it would return no records because it was left blank and
it will be looking for null values. I want to be able to have the querry
ignore a criteria if it is left blank in the form instead of it looking for
null values. The user cant just enter in data because in many cases the
values are unknown.

I suggest that you use code in the search button's click
event to construct the OpenForm method's WhereCondition
argument. This makes it easy to ignore unspecified search
form values. For example:

Dim strWhere As String
If Not IsNull(txtSqFt) Then
strWhere = strWhere & " And SqFt = " & txtSqFt
End If
If Not IsNull(txtCounty) Then
strWhere = strWhere & " And County = " & txtCounty
End If
. . .
DoCmd.OpenForm "displayform", , , Mid(strWhere, 6)

I'm sure that your real situation is not really that simple.
You probably want to use a minimum or range for the square
feet, price, etc. but this should give you the basic idea.

You may also want to read up on the BuildCriteria function
to allow the user to use the same kind of criteria
expression you ca use in the QBE Criteria row.

 

[Guest]

Thanks for the help its working great so far, I have one more quick question
though. I am trying to get the Like operator to work in a situation where a
street address is entered. I would like someone who enters 44th to be able
to pull up all records with 44th in the string such as 44th Street, 44th ST
SW, West 44th Avenue, ect. The code I have now just returns exact matches.
Heres what it looks like.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TblImproved"

If Not IsNull(Me![FilterStreet]) Then
stLinkCriteria = "[Street]=" & "'" & Me![FilterStreet] & "'"

End If

How exaclty would it look. Thanks for any help.

 

[Guest]

Problem solved, thank you.

 

[Marshall Barton]

Thanks for the help its working great so far, I have one more quick question
though. I am trying to get the Like operator to work in a situation where a
street address is entered. I would like someone who enters 44th to be able
to pull up all records with 44th in the string such as 44th Street, 44th ST
SW, West 44th Avenue, ect. The code I have now just returns exact matches.
Heres what it looks like.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TblImproved"

If Not IsNull(Me![FilterStreet]) Then
stLinkCriteria = "[Street]=" & "'" & Me![FilterStreet] & "'"

stLinkCriteria = "[Street]='*" & Me![FilterStreet] & "*'"
or
stLinkCriteria = "[Street]=""*" & Me![FilterStreet] & "*"""

The asterick (*) is the wildcard character that matches any
number of characters. If you want to match only at the
beginning, then remove the * on the left.

But, as I tried to imply earlier, it might be better (more
flexible) to allw the users to make that determination. You
can use the BuildCriteria function to allow the users to
enter their criteria in various ways. E.g.

to match on at the start:
44th*
or anywhere in the field:
*44th*
or for an exact match:
W. 44th Avenue
or, for the square feet field, they may want to use various
conditions such as:

=10000

Between 9000 And 11000

If the users are going to be constantly asking for various
modifications to allow for their whim of the day, it would
be better for both you and them to shift the burden to their
shoulders. Think about it.