AND/OR query in Access 2007.

[AccessBeetle]

I have a qury which takes two parameters ProjectId and ResourceCategory. I have created a form which has these two comboboxex for user to enter the parameters. When user hits preview report, this query runs and report bound to this query opens.
I want something like this:
1. If user enters both ProjectID and ResourceCategory both, the query should filter with "ProjectId and ResourceCategory"
2. If user only enters only ProjectID, it should not mind about the Resource Category.
Any hints??
Thanks

[yarbrough40]

In the onclick event of your button do a contitional statement....

 'first type out the two possible query string variables
 
Dim Qstring1, Qstring2 as String
 
Qstring1 = "SELECT [MyTable]].[ProjectId], [MyTable].[ResourceCategory] FROM [MyTable];"
 
Qstring2 = "SELECT [MyTable]].[ProjectId] FROM [MyTable];"
 
'then do your condition and chose your data source for your report
 
if ResourceCategory.value = "" then
Report1.RecordSource = Qstring2
Else
Report1.RecordSource = Qstring1
End If
 

[patjones]

You need to choose the appropriate structure for your WHERE clause depending on the circumstance in question. Let's say your two combo boxes are cboProjectID and cboRsrcCategory. You could do something like

Dim strSQL_Filter As String
 
If  Not IsNull(Me.cboProjectID) And Not IsNull(Me.cboRsrcCategory) Then
     strSQL_Filter = " WHERE tbl.ProjectID = " & Me.cboProjectID & " AND tbl.ResourceCategory = '" & Me.cboRsrcCategory & "'"
Else
     strSQL_Filter = " WHERE tbl.ProjectID = " & Me.cboProjectID
End If
 
[ReportName].Recordsource = "SELECT tbl.[Field1], tbl.[Field2], ..." &_
                            "FROM tbl" & strSQL_Filter
 

I've assumed that ProjectID is a number and ResourceCategory is text, so you might need to modify the filter with or without ' ' appropriately depending on the field types. I've also written the SQL statement very generically with table name "tbl", field names "Field1", "Field2", etc., so modify it to include whatever your table and field names are, what you want in your report, etc.

I would also set the "Limit To List" property on the combo boxes to Yes; this will prevent the user from typing whatever they want into the boxes, and therefore help you avoid SQL injection. Otherwise the query needs to be modified with parameters to avoid SQL injection attacks.

Pat

[AccessBeetle]

Thanks for the insight.
This means that there is no way we can trick "one" query to serve AND/OR purpose. We have to implement it in VBA.
Thanks for help. !!

[patjones]

Well, there is a way that you could write it out in a single statement...

[ReportName].Recordsource = "SELECT tbl.[Field1], tbl.[Field2], ..." &_
                            "FROM tbl " & _
                            "WHERE tbl.ProjectID = " & Me.cboProjectID & IIf(IsNull(Me.cboRsrcCategory),""," AND tbl.ResourceCategory = '" & Me.cboRsrcCategory & "'")

Here the IIf statement picks out either a zero-length string, or the AND part of the query depending on whether or not something was selected in the second combo box. Personally though, I think the way I laid it out before makes the process clearer, even if it does require a few more lines of code.

Pat

[AccessBeetle]

I agree. Good to know that we can use "iif" like this in query.
Thanks.

[NeoPa]

Assuming ResourceCategory is a string value, then this could equally be done as :

SELECT *
FROM   tbl
WHERE  [ProjectID]=Forms!FormName![cboProjectID]
  AND  Nz(Forms!FormName![cboRsrcCategory],'') In([ResourceCategory],'')

This would avoid the necessity of resaving the QueryDef or even supplying a different RecordSource to the Report. This is one area where SQL in VBA string variables is not a good fit. The SQL can be passed into the Report code itself instead, but the more standard way (if indeed the SQL in string variable root is preferred) is to pass it in the call as the WhereCriteria parameter.