Query with mulitple optional criteria

[Guest]

I am trying to create a query that has one required parameter (date field)
and two optional parameters (Issue and SubIssue) and can't seem to get it to
work right. It starts with a form that allows a user to select a date range.

Criteria for Date field is >=[Forms]![HelpDeskLogView by
Criteria]![ReportStartDate] And <=[Forms]![HelpDeskLogView by
Criteria]![ReportEndDate]

Next I have two additional parameters that can either be left blank or a
value selected. Criteria for the Issue field is:
Like [Forms]![HelpDeskLogView by Criteria]![Issue] & "*"

and then for the SubIssue field:
Like [Forms]![HelpDeskLogView by Criteria]![SubIssue] & "*"

The problem comes in with the second SubIssue field. I can't figure out how
to set up the query to allow for the date range and then TWO optional fields.

I hope I explained this well. Please let me know if you need additonal
information.

Thank you,
Grace

 

[Guest]

Grace I think we are trying to do the same thing.

I'm working on a database. I want to run a query that only has addresses in
the data record. Do you know how to do this?

Thanks,

Don
(e-mail address removed)

 

[Guest]

Don, can you just type "Is not Null" in the criteria for that field so that
it does not include records that have not addresses?

Grace I think we are trying to do the same thing.

I'm working on a database. I want to run a query that only has addresses in
the data record. Do you know how to do this?

Thanks,

Don
(e-mail address removed)

I am trying to create a query that has one required parameter (date field)
and two optional parameters (Issue and SubIssue) and can't seem to get it to
work right. It starts with a form that allows a user to select a date range.

Criteria for Date field is >=[Forms]![HelpDeskLogView by
Criteria]![ReportStartDate] And <=[Forms]![HelpDeskLogView by
Criteria]![ReportEndDate]

Next I have two additional parameters that can either be left blank or a
value selected. Criteria for the Issue field is:
Like [Forms]![HelpDeskLogView by Criteria]![Issue] & "*"

and then for the SubIssue field:
Like [Forms]![HelpDeskLogView by Criteria]![SubIssue] & "*"

The problem comes in with the second SubIssue field. I can't figure out how
to set up the query to allow for the date range and then TWO optional fields.

I hope I explained this well. Please let me know if you need additonal
information.

Thank you,
Grace

 

[Allen Browne]

The basic idea here is to build up the WHERE clause of a query statement
dynamically. Depending what you want to do with it, you can then:
- use it as the Filter for a form,
- apply it as the WhereCondition of OpenReport,
- build the entire SQL statement to OpenRecordset in code, or
- assign the entire SQL statement to the SQL property of a QueryDef.

This example shows how to build the Where string from the non-blank boxes on
your form:

Dim strWhere As String
Dim lngLng As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

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

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

If Not IsNull(Me.ReportStartDate) Then
strWhere = strWhere & "[SomeDate] >= " & _
Format(Me.ReportStartDate, conJetDate) & ") AND "
End If

If Not IsNull(Me.ReportEndDate) Then
strWhere = strWhere & "[SomeDate] < " & _
Format(Me.ReportStartDate + 1, conJetDate) & ") AND "
End If

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


Notes:
1. Drop the extra quotes if the Issue field type is Number (not Text), i.e.:
strWhere = strWhere & "([Issue] = " & Me.Issue & ") AND "

2. For the ending date, the code calculates, "less than the next day". That
approach works even if the field has a time component.

 

[Steve Schapel]

Grace,

You can put the criteria into your query like this...

In the date field...
Between [Forms]![HelpDeskLogView by Criteria]![ReportStartDate] And
[Forms]![HelpDeskLogView by Criteria]![ReportEndDate]

In the Issue field...
Like [Forms]![HelpDeskLogView by Criteria]![Issue] & "*" Or
[Forms]![HelpDeskLogView by Criteria]![Issue] Is Null

In the SubIssue field...
Like [Forms]![HelpDeskLogView by Criteria]![SubIssue] & "*" Or
[Forms]![HelpDeskLogView by Criteria]![SubIssue] Is Null

After you close and save this query, and then look at design view again,
you will see that Access has moved the criteria around to suit its own
purposes. Don't worry about this, it is all good, but I reckon the
above is the easiest way to enter the criteria in the first place.