Problem with query values from a form

[Eskil]

Hi
I have a form that supplies my query with information on two different
variables. The form uses a lookup to display a list of 5 different
customer types and 5 different types of meetings/activities in two
different combo boxes.

Now, the values of form variables go into a query upon which a report
is generated. By using those two variables my form can produce 25
different reports. But the report turns out empty on any combination
involving all types of meeting and/or all types of customers.
Obviously, these values are not part of my meeting and customer types,
and an attempt to make a Is Not Null data entry did not work.

How do I solve this without adding new reports and new queries?

Thanks in advance
Eskil

[Allen Browne]

Presumably your query refers to text boxes on your form, so the Criteria row
of the query contains something like:
[Forms].[Form1].[Textbox1]

There are ways to try to make the query try to return records anway, but
they are inefficient and messy and dependent on the data types. A better
solution might be to put a command button on the form to open the report.
You can then build a string to use as the WhereCondition of the OpenReport
action. In building the string, you just ignore any text boxes that are
null, so then all records are returned.

This example shows how to build up the string from the text boxes where the
user entered a value. If you are filtering a Text type field, it needs extra
quotes (as in the 2nd example).

-----------------------code starts------------------------
Private Sub cmdPreview_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.Textbox1) Then
strWhere = strWhere & "([SomeNumberField] = " & _
Me.Textbox1 & ") AND "
End If

If Not IsNull(Me.Textbox2) Then
strWhere = strWhere & "([SomeTextField] = """ & _
Me.Textbox2 & """) AND "
End If

'etc for other text boxes as needed.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

'Now open the report.
DoCmd.Openreport "MyReport", acViewPreview, , strWhere
End Sub
-----------------------code ends------------------------

If you are filtering dates, see:
http://members.iinet.net.au/~allenbrowne/casu-08.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Eskil" <es********@hotmail.com> wrote in message
news:3d**************************@posting.google.c om...

Hi
I have a form that supplies my query with information on two different
variables. The form uses a lookup to display a list of 5 different
customer types and 5 different types of meetings/activities in two
different combo boxes.

Now, the values of form variables go into a query upon which a report
is generated. By using those two variables my form can produce 25
different reports. But the report turns out empty on any combination
involving all types of meeting and/or all types of customers.
Obviously, these values are not part of my meeting and customer types,
and an attempt to make a Is Not Null data entry did not work.

How do I solve this without adding new reports and new queries?

Thanks in advance
Eskil