By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

AND/OR query in Access 2007.

100+
P: 111
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
Apr 16 '10 #1

✓ answered by 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

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL_Filter As String
  2.  
  3. If  Not IsNull(Me.cboProjectID) And Not IsNull(Me.cboRsrcCategory) Then
  4.      strSQL_Filter = " WHERE tbl.ProjectID = " & Me.cboProjectID & " AND tbl.ResourceCategory = '" & Me.cboRsrcCategory & "'"
  5. Else
  6.      strSQL_Filter = " WHERE tbl.ProjectID = " & Me.cboProjectID
  7. End If
  8.  
  9. [ReportName].Recordsource = "SELECT tbl.[Field1], tbl.[Field2], ..." &_
  10.                             "FROM tbl" & strSQL_Filter
  11.  

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

Share this Question
Share on Google+
6 Replies


yarbrough40
100+
P: 320
In the onclick event of your button do a contitional statement....

Expand|Select|Wrap|Line Numbers
  1.  'first type out the two possible query string variables
  2.  
  3. Dim Qstring1, Qstring2 as String
  4.  
  5. Qstring1 = "SELECT [MyTable]].[ProjectId], [MyTable].[ResourceCategory] FROM [MyTable];"
  6.  
  7. Qstring2 = "SELECT [MyTable]].[ProjectId] FROM [MyTable];"
  8.  
  9. 'then do your condition and chose your data source for your report
  10.  
  11. if ResourceCategory.value = "" then
  12. Report1.RecordSource = Qstring2
  13. Else
  14. Report1.RecordSource = Qstring1
  15. End If
  16.  
Apr 16 '10 #2

patjones
Expert 100+
P: 931
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

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL_Filter As String
  2.  
  3. If  Not IsNull(Me.cboProjectID) And Not IsNull(Me.cboRsrcCategory) Then
  4.      strSQL_Filter = " WHERE tbl.ProjectID = " & Me.cboProjectID & " AND tbl.ResourceCategory = '" & Me.cboRsrcCategory & "'"
  5. Else
  6.      strSQL_Filter = " WHERE tbl.ProjectID = " & Me.cboProjectID
  7. End If
  8.  
  9. [ReportName].Recordsource = "SELECT tbl.[Field1], tbl.[Field2], ..." &_
  10.                             "FROM tbl" & strSQL_Filter
  11.  

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
Apr 19 '10 #3

100+
P: 111
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. !!
Apr 19 '10 #4

patjones
Expert 100+
P: 931
Well, there is a way that you could write it out in a single statement...

Expand|Select|Wrap|Line Numbers
  1. [ReportName].Recordsource = "SELECT tbl.[Field1], tbl.[Field2], ..." &_
  2.                             "FROM tbl " & _
  3.                             "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
Apr 19 '10 #5

100+
P: 111
I agree. Good to know that we can use "iif" like this in query.
Thanks.
Apr 19 '10 #6

NeoPa
Expert Mod 15k+
P: 31,273
Assuming ResourceCategory is a string value, then this could equally be done as :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM   tbl
  3. WHERE  [ProjectID]=Forms!FormName![cboProjectID]
  4.   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.
Apr 20 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.