Query with parameters from a form

[JustJim]

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.

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

[NeoPa]

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.

[JustJim]

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

[NeoPa]

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?

[JustJim]

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

[JustJim]

Posted just for the Wow! factor

Private Sub btnClaimByStatus_Click()
Dim strSQL As String
Dim lngParenCounter As Long
 
'   Dummy WHERE clause
strSQL = "((qryClaimMainQuery.ClaimID) Is Not Null)"
 
'   Parenthesis counter
lngParenCounter = 0
 
'   Check each control on the form and add to the WHERE clause and increment the parenthesis counter if necessary
If Not IsNull(Me.cboStatus) Then
    strSQL = strSQL & " AND ((qryClaimMainQuery.Claim.ClaimStatusID) = " & Me.cboStatus.Column(0) & ")"
    lngParenCounter = lngParenCounter + 1
End If
 
If Not IsNull(Me.cboSprayer) Then
    strSQL = strSQL & " AND ((qryClaimMainQuery.Machine.MachineTypeID) = " & Me.cboSprayer.Column(0) & ")"
    lngParenCounter = lngParenCounter + 1
End If
 
If Not IsNull(Me.cboTankSize) Then
    strSQL = strSQL & " AND ((qryClaimMainQuery.TankSizeID) = " & Me.cboTankSize.Column(0) & ")"
    lngParenCounter = lngParenCounter + 1
End If
 
If Not IsNull(Me.cboBoomWidth) Then
    strSQL = strSQL & " AND ((qryClaimMainQuery.BoomWidthID) = " & Me.cboBoomWidth.Column(0) & ")"
    lngParenCounter = lngParenCounter + 1
End If
 
If Not IsNull(Me.cboSection) Then
    strSQL = strSQL & " AND ((qryClaimMainQuery.SectionID) = " & Me.cboSection.Column(0) & ")"
    lngParenCounter = lngParenCounter + 1
End If
 
If Not IsNull(Me.cboOnus) Then
    strSQL = strSQL & " AND ((qryClaimMainQuery.ClaimOnusID) = " & Me.cboOnus.Column(0) & ")"
    lngParenCounter = lngParenCounter + 1
End If
 
If Not IsNull(Me.cboOwner) Then
    strSQL = strSQL & " AND ((qryClaimMainQuery.MachineOwnerName) = "" & Me.cboOwner.Column(0) & "")"
    lngParenCounter = lngParenCounter + 1
End If
 
If Me.chkPurchaseDateAll = False Then
    strSQL = strSQL & " AND ((qryClaimMainQuery.MachineDatePurchased) Between " & Me.txtPurchaseStartDate & " And " & Me.txtPurchaseEndDate & ")"
    lngParenCounter = lngParenCounter + 1
End If
 
If Me.chkBuildDateAll = False Then
    strSQL = strSQL & " AND ((qryClaimMainQuery.MachineBuildDate) Between " & Me.txtBuildStartDate & " And " & Me.txtBuildEndDate & ")"
    lngParenCounter = lngParenCounter + 1
End If
 
If Me.chkSerialNumberAll = False Then
    strSQL = strSQL & " AND ((qryClaimMainQuery.MachineSerialNumber) Between " & Me.txtSerialFirstNumber & " And " & Me.txtSerialLastNumber & ")"
    lngParenCounter = lngParenCounter + 1
End If
 
If Me.chkBuildSeqNumberAll = False Then
    strSQL = strSQL & " AND ((qryClaimMainQuery.MachineBuildSequenceNumber) Between " & Me.txtBuildSeqFirstNumber & " And " & Me.txtBuildSeqLastNumber & ")"
    lngParenCounter = lngParenCounter + 1
End If
 
'   Wrap with the appropriate number of parentheses
strSQL = String(lngParenCounter, Chr(40)) & strSQL & String(lngParenCounter, Chr(41))
Debug.Print strSQL
 
'   open the report
DoCmd.OpenReport "Claims", acViewPreview, , strSQL
 
 

[NeoPa]

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.

[JustJim]

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

[NeoPa]

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 ;)