Dynamic query with multiple criteria

[Archie999]

Hi All,

I have a dynamic query (below). I would like to add one more criteria to the where clause... but it is a combination of 2 different criteria in one and I'm drawing a blank as to how to do it.

How can I add the 2 criteria so that they are not taken individually

I want:

If StatusID = 2 AND DateField > SomeDate then include in query result set

I do not want

If StatusID = 2 the include in query result set
AND if DateField > SomeDate then include in query result set

I hope this makes sense and further that this is an easy question and I'm just not seeing it.

Thanks, as always, in advance.



' Build a dynamic query based on the forms input
If Not IsNull(Me![SiteID]) Then
Xwhere = Xwhere & " AND [tblIssueIncident.SiteID]= " & Me![SiteID]
End If

If Me.PriorityID.ItemsSelected.Count > 0 Then
For Each varItem In Me.PriorityID.ItemsSelected
strListPriority = strListPriority & Me.PriorityID.ItemData(varItem) & " , "
Next varItem
Xwhere = Xwhere & " AND [tblIssueIncident.PriorityID] IN(" & strListPriority & "99) "
End If

If Me.StatusID.ItemsSelected.Count > 0 Then
For Each varItem In Me.StatusID.ItemsSelected
strListStatus = strListStatus & Me.StatusID.ItemData(varItem) & " , "
Next varItem
Xwhere = Xwhere & " AND [tblIssueIncident.StatusID] IN(" & strListStatus & "99) "


If Not IsNull(Me![IncidentMIGDeptID]) Then
Xwhere = Xwhere & " AND [tblIssueIncident.IncidentMIGDeptID]= " _
& Me![IncidentMIGDeptID]
End If

If Not IsNull(Me![IncidentMIGDeptContactID]) Then
Xwhere = Xwhere & " AND [tblIssueIncident.IncidentMIGDeptContactID]= " _
& Me![IncidentMIGDeptContactID]
End If

If Not IsNull(Me![IncidentSiteContactID]) Then
Xwhere = Xwhere & " AND [tblIssueIncident.IncidentSiteContactID]= " _
& Me![IncidentSiteContactID]
End If

Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from tblIssueIncident INNER JOIN tblIssueUpdate ON " _
& "tblIssueIncident.MyIssueIncidentID=tblIssueUpdate.IssueIncidentID" _
& (" where " + Mid(Xwhere, 6) & ";"))

 

[Jon K]

I want:
If StatusID = 2 AND DateField > SomeDate then include in query result set

I do not want:
If StatusID = 2 then include in query result set
AND if DateField > SomeDate then include in query result set

Since both criteria use the operator AND, I think the results are the same.

----------------------------
On systems with US date format, you can use something like this:-
Xwhere = Xwhere & " AND [StatusID] = 2 AND [DateField] > #" & "3/15/2004" & "#"


On systems with other date formats, you can use this:-
Xwhere = Xwhere & " AND [StatusID] = 2 AND Format([DateField],'yyyymmdd') > '20040315'"

or this:-
Xwhere = Xwhere & " AND [StatusID] = 2 AND Format([DateField],'yyyymmdd') > '" & _
Format(CDate("15/3/2004"), "yyyymmdd") & "'"

In fact the latter two are also good for US date format.

 

[Archie999]

Thanks Jon,

What you say makes sense, if that was the entire query. The confusion would come when other part of the query potentially conflict with this part of the where clause.

For example, in the dynamic query search form the user can select status(es) from a list box. (This is an Issue Tracking DB). Let's say they choose 1 and 3 (Open and Pending). The idea for the part I am having trouble is that I want to give users the ability to include in their report, issues with a status of 2 (Closed) from the last week. In other words show issues that were recently closed regardless of what was selected from the status list box.

So you may have:

Xwhere = Xwhere & " AND [tblIssueIncident.StatusID] IN (1,3,99) "
AND
Xwhere = Xwhere & " AND [StatusID] = 2 AND Format([DateField],'yyyymmdd') > '20040315'"

Correct me if I am wrong but I don't think this will work. Won't the date restriction apply to the entire resulting record set and not only to records where the status is Closed (2).

Thanks again for your reply, let me know if you can shed anymore light

 

[Jon K]

Sorry, I overlooked possible selections in the StatusID list box.

If what you want is to add records where (StatusID=2 and DateField>SomeDate) to whatever records the user has selected, you can add them to the where clause with OR like this:-

Xwhere = Xwhere & " OR ([StatusID] = 2 AND Format([DateField],'yyyymmdd') > '20040315')"


To add them dynamically, you can use something like this:-

If Me.IncludeClosedLastWeek then
Xwhere = Xwhere & " OR ([StatusID] = 2 AND Format([DateField],'yyyymmdd') > '20040315')"
End If

Note there needs to be two spaces before OR so that you can still use Mid(Xwhere, 6) in the SQL string.