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

Query with parameters from a form

JustJim
Expert 100+
P: 407
Imagine if you will, a beautifully designed and normalised database application.

OK that's enough. On to my database application!

I envisage a form with multiple combo-boxes feeding a query so that the operator can activate the query (it feeds a report not that that matters) with the selections ANDed together and just see the required result. I also imagine a check box associated with each combo-box to indicate that any and all values are required in this field. I can get the combos to work fine but can't re-set to all. I am using the dreaded IIF statement in the Criteria box of the Query design view as follows.
Expand|Select|Wrap|Line Numbers
  1. IIf([Forms]![frmReports]![chkStatusAll]=False,[Forms]![frmReports]![cboStatus],Not Null)
frmReports is the form where the combos and checks are
chkStatusAll is one of the check boxes
cboStatus is one of the combo boxes.

It would be ideal if I could just not have a "FalsePart" for the IIF statement but I have to have both.

Any Ideas??

'AveAGoodWeekend

Jim
Sep 21 '07 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,186
Jim,

The first thing that springs to mind is that you're trying to assign a value of {Not Null} in your IIf statement. Null is a valid value but Not Null can only be used in a comparison.
Sep 21 '07 #2

JustJim
Expert 100+
P: 407
Jim,

The first thing that springs to mind is that you're trying to assign a value of {Not Null} in your IIf statement. Null is a valid value but Not Null can only be used in a comparison.
Good point. That was just an example though. The FalsePart of the IIF statement is the one that is causing me the problem. If there's no tick in the check-box, I want the query to put a WHERE clause using the value in the combo-box. If there is a tick in the "All" check-box I don't want a WHERE clause for that particular field.

There will be seven groups of check boxes and combo boxes as well as four pairs of text boxes for dates to make a BETWEEN clause. I think I'm just going to have to use VBA to write an SQL query rather than use the controls on the form as parameters in the query.

Oh well,

Jim
Sep 24 '07 #3

NeoPa
Expert Mod 15k+
P: 31,186
I'm sorry Jim. IIf() is a function that returns a value. It cannot be used to design the SQL clauses themselves unless via VBA where the IIf() function there (different function - just to complicate the idea) can be used to create the string that may later be passed as a SQL string. Does that make sense?
Sep 25 '07 #4

JustJim
Expert 100+
P: 407
I'm sorry Jim. IIf() is a function that returns a value. It cannot be used to design the SQL clauses themselves unless via VBA where the IIf() function there (different function - just to complicate the idea) can be used to create the string that may later be passed as a SQL string. Does that make sense?
I thought as much. Thanks for confirming it. I'm in the middle of coding a big SQL clause by looking at the value of each check box and combo box and either adding or not adding a bit to the WHERE clause.

I do get myself in deep, don't I?

Thanks for the helping hand... again!

Jim
Sep 25 '07 #5

JustJim
Expert 100+
P: 407
Posted just for the Wow! factor

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnClaimByStatus_Click()
  2. Dim strSQL As String
  3. Dim lngParenCounter As Long
  4.  
  5. '   Dummy WHERE clause
  6. strSQL = "((qryClaimMainQuery.ClaimID) Is Not Null)"
  7.  
  8. '   Parenthesis counter
  9. lngParenCounter = 0
  10.  
  11. '   Check each control on the form and add to the WHERE clause and increment the parenthesis counter if necessary
  12. If Not IsNull(Me.cboStatus) Then
  13.     strSQL = strSQL & " AND ((qryClaimMainQuery.Claim.ClaimStatusID) = " & Me.cboStatus.Column(0) & ")"
  14.     lngParenCounter = lngParenCounter + 1
  15. End If
  16.  
  17. If Not IsNull(Me.cboSprayer) Then
  18.     strSQL = strSQL & " AND ((qryClaimMainQuery.Machine.MachineTypeID) = " & Me.cboSprayer.Column(0) & ")"
  19.     lngParenCounter = lngParenCounter + 1
  20. End If
  21.  
  22. If Not IsNull(Me.cboTankSize) Then
  23.     strSQL = strSQL & " AND ((qryClaimMainQuery.TankSizeID) = " & Me.cboTankSize.Column(0) & ")"
  24.     lngParenCounter = lngParenCounter + 1
  25. End If
  26.  
  27. If Not IsNull(Me.cboBoomWidth) Then
  28.     strSQL = strSQL & " AND ((qryClaimMainQuery.BoomWidthID) = " & Me.cboBoomWidth.Column(0) & ")"
  29.     lngParenCounter = lngParenCounter + 1
  30. End If
  31.  
  32. If Not IsNull(Me.cboSection) Then
  33.     strSQL = strSQL & " AND ((qryClaimMainQuery.SectionID) = " & Me.cboSection.Column(0) & ")"
  34.     lngParenCounter = lngParenCounter + 1
  35. End If
  36.  
  37. If Not IsNull(Me.cboOnus) Then
  38.     strSQL = strSQL & " AND ((qryClaimMainQuery.ClaimOnusID) = " & Me.cboOnus.Column(0) & ")"
  39.     lngParenCounter = lngParenCounter + 1
  40. End If
  41.  
  42. If Not IsNull(Me.cboOwner) Then
  43.     strSQL = strSQL & " AND ((qryClaimMainQuery.MachineOwnerName) = "" & Me.cboOwner.Column(0) & "")"
  44.     lngParenCounter = lngParenCounter + 1
  45. End If
  46.  
  47. If Me.chkPurchaseDateAll = False Then
  48.     strSQL = strSQL & " AND ((qryClaimMainQuery.MachineDatePurchased) Between " & Me.txtPurchaseStartDate & " And " & Me.txtPurchaseEndDate & ")"
  49.     lngParenCounter = lngParenCounter + 1
  50. End If
  51.  
  52. If Me.chkBuildDateAll = False Then
  53.     strSQL = strSQL & " AND ((qryClaimMainQuery.MachineBuildDate) Between " & Me.txtBuildStartDate & " And " & Me.txtBuildEndDate & ")"
  54.     lngParenCounter = lngParenCounter + 1
  55. End If
  56.  
  57. If Me.chkSerialNumberAll = False Then
  58.     strSQL = strSQL & " AND ((qryClaimMainQuery.MachineSerialNumber) Between " & Me.txtSerialFirstNumber & " And " & Me.txtSerialLastNumber & ")"
  59.     lngParenCounter = lngParenCounter + 1
  60. End If
  61.  
  62. If Me.chkBuildSeqNumberAll = False Then
  63.     strSQL = strSQL & " AND ((qryClaimMainQuery.MachineBuildSequenceNumber) Between " & Me.txtBuildSeqFirstNumber & " And " & Me.txtBuildSeqLastNumber & ")"
  64.     lngParenCounter = lngParenCounter + 1
  65. End If
  66.  
  67. '   Wrap with the appropriate number of parentheses
  68. strSQL = String(lngParenCounter, Chr(40)) & strSQL & String(lngParenCounter, Chr(41))
  69. Debug.Print strSQL
  70.  
  71. '   open the report
  72. DoCmd.OpenReport "Claims", acViewPreview, , strSQL
  73.  
  74.  
Sep 25 '07 #6

NeoPa
Expert Mod 15k+
P: 31,186
I don't want to burst any bubbles Jim but I'd make a couple of comments (hopefully to simplify life for you).
  1. Rather than using double-double-quotes ("") to denote a string start or end in SQL it is actually easier (and more correct / portable) to use a single quote. This is for SQL strings only. VBA strings only use the double-quote (") character. See Quotes (') and Double-Quotes (") - Where and When to use them.
  2. Do you really need to manage and add all the extra parentheses? A single set would be required if, and only if, you want to AND or OR the whole set with some other criteria. I can't see any use for multiple ones that surround all. Losing this unnecessary complication should simplify and shorten your code somewhat.
Hope this helps.
Sep 25 '07 #7

JustJim
Expert 100+
P: 407
I don't want to burst any bubbles Jim but I'd make a couple of comments (hopefully to simplify life for you).
  1. Rather than using double-double-quotes ("") to denote a string start or end in SQL it is actually easier (and more correct / portable) to use a single quote. This is for SQL strings only. VBA strings only use the double-quote (") character. See Quotes (') and Double-Quotes (") - Where and When to use them.
  2. Do you really need to manage and add all the extra parentheses? A single set would be required if, and only if, you want to AND or OR the whole set with some other criteria. I can't see any use for multiple ones that surround all. Losing this unnecessary complication should simplify and shorten your code somewhat.
Hope this helps.
Pop!

No, really, thanks heaps for taking the time to even look through it. I've been doing this sort of thing since the days of the TRS-80 and I've learned more in the months that I've been checking this forum than in all that time.

Jim
Sep 25 '07 #8

NeoPa
Expert Mod 15k+
P: 31,186
Pop!
...
I'm very please the popping noises didn't disturb you too much :D
More so that you're finding TSDN such a good place to be. We do too ;)
Sep 25 '07 #9

Post your reply

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