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.
[CODE=vb]IIf([Forms]![frmReports]![chkStatusAll]=False,[Forms]![frmReports]![cboStatus],Not Null)[/CODE]
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??

'AveAGoodWeeken d

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

[CODE=vb]Private Sub btnClaimByStatu s_Click()
Dim strSQL As String
Dim lngParenCounter As Long

' Dummy WHERE clause
strSQL = "((qryClaimMain Query.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.cboSt atus) Then
strSQL = strSQL & " AND ((qryClaimMainQ uery.Claim.Clai mStatusID) = " & Me.cboStatus.Co lumn(0) & ")"
lngParenCounter = lngParenCounter + 1
End If

If Not IsNull(Me.cboSp rayer) Then
strSQL = strSQL & " AND ((qryClaimMainQ uery.Machine.Ma chineTypeID) = " & Me.cboSprayer.C olumn(0) & ")"
lngParenCounter = lngParenCounter + 1
End If

If Not IsNull(Me.cboTa nkSize) Then
strSQL = strSQL & " AND ((qryClaimMainQ uery.TankSizeID ) = " & Me.cboTankSize. Column(0) & ")"
lngParenCounter = lngParenCounter + 1
End If

If Not IsNull(Me.cboBo omWidth) Then
strSQL = strSQL & " AND ((qryClaimMainQ uery.BoomWidthI D) = " & Me.cboBoomWidth .Column(0) & ")"
lngParenCounter = lngParenCounter + 1
End If

If Not IsNull(Me.cboSe ction) Then
strSQL = strSQL & " AND ((qryClaimMainQ uery.SectionID) = " & Me.cboSection.C olumn(0) & ")"
lngParenCounter = lngParenCounter + 1
End If

If Not IsNull(Me.cboOn us) Then
strSQL = strSQL & " AND ((qryClaimMainQ uery.ClaimOnusI D) = " & Me.cboOnus.Colu mn(0) & ")"
lngParenCounter = lngParenCounter + 1
End If

If Not IsNull(Me.cboOw ner) Then
strSQL = strSQL & " AND ((qryClaimMainQ uery.MachineOwn erName) = "" & Me.cboOwner.Col umn(0) & "")"
lngParenCounter = lngParenCounter + 1
End If

If Me.chkPurchaseD ateAll = False Then
strSQL = strSQL & " AND ((qryClaimMainQ uery.MachineDat ePurchased) Between " & Me.txtPurchaseS tartDate & " And " & Me.txtPurchaseE ndDate & ")"
lngParenCounter = lngParenCounter + 1
End If

If Me.chkBuildDate All = False Then
strSQL = strSQL & " AND ((qryClaimMainQ uery.MachineBui ldDate) Between " & Me.txtBuildStar tDate & " And " & Me.txtBuildEndD ate & ")"
lngParenCounter = lngParenCounter + 1
End If

If Me.chkSerialNum berAll = False Then
strSQL = strSQL & " AND ((qryClaimMainQ uery.MachineSer ialNumber) Between " & Me.txtSerialFir stNumber & " And " & Me.txtSerialLas tNumber & ")"
lngParenCounter = lngParenCounter + 1
End If

If Me.chkBuildSeqN umberAll = False Then
strSQL = strSQL & " AND ((qryClaimMainQ uery.MachineBui ldSequenceNumbe r) Between " & Me.txtBuildSeqF irstNumber & " And " & Me.txtBuildSeqL astNumber & ")"
lngParenCounter = lngParenCounter + 1
End If

' Wrap with the appropriate number of parentheses
strSQL = String(lngParen Counter, Chr(40)) & strSQL & String(lngParen Counter, Chr(41))
Debug.Print strSQL

' open the report
DoCmd.OpenRepor t "Claims", acViewPreview, , strSQL

[/CODE]

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