Query Form

[dave_b]

I have exported some data from the local MLS system into an Access database
and want to run some statistics on the sales information, using a number of
different queries which will feed graphs in Excel, etc...

I have a form set up for choosing the data which include the city, the
development, what kind of property it is (condo, single family residence,
etc.)

The problem I have is why no selection is made. For example, the query
looks for a city selection from a drop-down box; if I choose a city, the
query runs perfectly. If I do not choose a city - leaving it blank - the
query produces no results. I want "no city selected" or "all cities" to
produce a query that has all cities in it.

I've tried various 'iif' statements in the criteria - and nothing has
worked. I've tried checking the drop-down for null and for "" I've created
a text box with a value equal to the dropdown and tried to query on that -
and that has not worked. I even tried doing that with all the cities typed
into it, and that didn't work either.

I have seven different feilds that might or might not hold criteria. (City,
development, property type, land type, sales date, size of house, size of
lot.) There must be a way to hand a situation where no selection is made:
it would be real pain to have to code SQL for every possible combination of
these seven inputs!

Ideas would be highly appreciated!

 

[Dirk Goldgar]

I have exported some data from the local MLS system into an Access database
and want to run some statistics on the sales information, using a number
of
different queries which will feed graphs in Excel, etc...

I have a form set up for choosing the data which include the city, the
development, what kind of property it is (condo, single family residence,
etc.)

The problem I have is why no selection is made. For example, the query
looks for a city selection from a drop-down box; if I choose a city, the
query runs perfectly. If I do not choose a city - leaving it blank - the
query produces no results. I want "no city selected" or "all cities" to
produce a query that has all cities in it.

I've tried various 'iif' statements in the criteria - and nothing has
worked. I've tried checking the drop-down for null and for "" I've
created
a text box with a value equal to the dropdown and tried to query on that -
and that has not worked. I even tried doing that with all the cities
typed
into it, and that didn't work either.

I have seven different feilds that might or might not hold criteria.
(City,
development, property type, land type, sales date, size of house, size of
lot.) There must be a way to hand a situation where no selection is made:
it would be real pain to have to code SQL for every possible combination
of
these seven inputs!

In principle, you can code your query SQL like this:

------ start of example SQL ------
SELECT ...
WHERE
((City = Forms!YourForm!cboCity)
OR (Forms!YourForm!cboCity Is Null))
AND
((PropertyType = Forms!YourForm!cboPropertyType)
OR (Forms!YourForm!cboPropertyType Is Null))
...
------ end of example SQL ------

It is often simpler, if you will be opening a form or report from the form
where the criteria controls reside, to build a filter string on the fly and
apply it as the Where-Condition argument for the DoCmd.OpenForm or
DoCmd.OpenReport call. When taking that approach, the code looks something
like this:

'------ start of example code ------

Dim strFilter As String

If Not IsNull(Me.cboCity) Then
strFilter = strFilter & " AND (" & _
"City = "" & Me.cboCity & """)"
End If

If Not IsNull(Me.cboPropertyType) Then
strFilter = strFilter & " AND (" & _
"PropertyType = "" & Me.cboPropertyType& """)"
End If

' ... other criteria appended as needed ...

If Len(strFilter) > 0 Then
' Trim off leading " AND ".
strFilter = Mid$(strFilter, 6)
End If

DoCmd.OpenReport "rptProperties", WhereCondition:=strFilter

'------ end of example code ------