Passing "*" (all) to a query or optionally a value - from code (1 Viewer)

[cyberman55]

Hi again, I read somewhere that it's difficult to pass "null" to a parameterized query when basing the parameter on a form control. I'd like to do that if possible, but I have a different, almost opposite, question. I have a query I'd like to reuse as much as possible based on multiple "where" criteria which would be optionally "all" or some value based on a control.

Specifically, for example, I have a treeview control with a root and 4 nodes. The root, when selected means "all", the four other nodes represent detail levels in a space inventory (account, building, floor, room). When a user selects a node, I populate a text box on a form with the key field value. I also have some date fields on the form, and I had to set up parameters since the chart application squawks without them. Just one solution, would probably give them all, so in the following query sql, I'd like to control the [bldg cod] criteria to either be "all" or a selected building based on a control on a form. I know I could just build a sql string, but the issue is that this is just the first of 4 nested queries, so I would have an equally large mess if I had to build all the strings.

This is SQL view of the query:

PARAMETERS [Forms]![frmInspectionReports]![StartDate] DateTime, [Forms]![frmInspectionReports]![EndDate] DateTime;
SELECT DISTINCT [Building Inventory].[BLDG Code], [Building Inventory].[Building Name], IIf(IsNull([tblInspectionRecords].[DTimeStamp]),"Not Inspected","Inspected") AS Status, [Space Inventory].SF, [Space Inventory].SpaceID
FROM BidTable INNER JOIN ([Building Inventory] INNER JOIN ([Space Inventory] LEFT JOIN tblInspectionRecords ON [Space Inventory].SpaceID = tblInspectionRecords.SpaceID) ON [Building Inventory].[BLDG Code] = [Space Inventory].[BLDG Code]) ON BidTable.BidId = [Building Inventory].BidID
GROUP BY [Building Inventory].[BLDG Code], [Building Inventory].[Building Name], IIf(IsNull([tblInspectionRecords].[DTimeStamp]),"Not Inspected","Inspected"), [Space Inventory].SF, [Space Inventory].SpaceID, BidTable.CustomerCompanyName, tblInspectionRecords.DTimeStamp
HAVING (((tblInspectionRecords.DTimeStamp)>CDate([Forms]![frmInspectionReports]![StartDate])-1 And (tblInspectionRecords.DTimeStamp)<CDate([Forms]![frmInspectionReports]![EndDate])+1)) OR (((tblInspectionRecords.DTimeStamp) Is Null))
ORDER BY [Building Inventory].[Building Name];

This nomenclature, with spaces in the table and field names is poor, I know, but it's a legacy that traces back 20 years when I didn't know better coming out of a mechanical engineering background.

 

[Pat Hartman]

You are not aggregating any data so I'm not sure why you are using a Group By.

A WHERE clause is applied BEFORE any aggregation happens and a HAVING is applied AFTER aggregation. Having is usually used when your criteria is a sum or avg, etc. So for efficiency, you might want to move the Having to a Where assuming there is aggregation that I just can't see because I don't have the whole schema.

Use the following pattern when you have multiple optional conditions.

Where (fldA = Forms!frmYourForm!txtfldA or Forms!frmYourForm!txtfldA Is Null)
AND
(fldB = Forms!frmYourForm!txtfldB or Forms!frmYourForm!txtfldB Is Null)
AND
(fldC = Forms!frmYourForm!txtfldC or Forms!frmYourForm!txtfldC Is Null)
AND
...

The parentheses are critical in expressions that use AND, OR, and NOT. You want each expression inside a pair of parens to evaluate to true or false and then be AND'd with the next expression. Essentially each expression says were a table field = a form field or the form field is empty. So if the form field is empty, the expression will return true.

 

[cyberman55]

Thanks Pat - I'm playing around with this:

PARAMETERS [Forms]![frmInspectionReports]![StartDate] DateTime, [Forms]![frmInspectionReports]![EndDate] DateTime, [Forms]![frmInspectionReports]![txtNode1] Long, [Forms]![frmInspectionReports]![txtNode2] Long, [Forms]![frmInspectionReports]![txtNode3] Text ( 255 ), [Forms]![frmInspectionReports]![txtNode4] Text ( 255 );
SELECT DISTINCT [Building Inventory].[BLDG Code], [Level Inventory].[Level #], [Building Inventory].[Building Name], IIf(IsNull([tblInspectionRecords].[DTimeStamp]),"Not Inspected","Inspected") AS Status, [Space Inventory].SF, [Space Inventory].SpaceID
FROM ((BidTable INNER JOIN [Building Inventory] ON BidTable.BidId = [Building Inventory].BidID) INNER JOIN [Level Inventory] ON [Building Inventory].[BLDG Code] = [Level Inventory].[BLDG Code]) INNER JOIN ([Space Inventory] LEFT JOIN tblInspectionRecords ON [Space Inventory].SpaceID = tblInspectionRecords.SpaceID) ON ([Level Inventory].[Level #] = [Space Inventory].[Level #]) AND ([Level Inventory].[BLDG Code] = [Space Inventory].[BLDG Code])
GROUP BY [Building Inventory].[BLDG Code], [Level Inventory].[Level #], [Building Inventory].[Building Name], IIf(IsNull([tblInspectionRecords].[DTimeStamp]),"Not Inspected","Inspected"), [Space Inventory].SF, [Space Inventory].SpaceID, BidTable.CustomerCompanyName, tblInspectionRecords.DTimeStamp, BidTable.BidId
HAVING ((([Building Inventory].[BLDG Code])=[Forms]![frmInspectionReports]![txtNode2] Or [Forms]![frmInspectionReports]![txtNode2] Is Null) AND (([Level Inventory].[Level #])=[Forms]![frmInspectionReports]![txtNode3] Or [Forms]![frmInspectionReports]![txtNode3] Is Null) AND ((tblInspectionRecords.DTimeStamp)>CDate([Forms]![frmInspectionReports]![StartDate])-1 And (tblInspectionRecords.DTimeStamp)<CDate([Forms]![frmInspectionReports]![EndDate])+1) AND ((BidTable.BidId)=[Forms]![frmInspectionReports]![txtNode1] Or [Forms]![frmInspectionReports]![txtNode1] Is Null)) OR ((([Building Inventory].[BLDG Code])=[Forms]![frmInspectionReports]![txtNode2] Or [Forms]![frmInspectionReports]![txtNode2] Is Null) AND (([Level Inventory].[Level #])=[Forms]![frmInspectionReports]![txtNode3] Or [Forms]![frmInspectionReports]![txtNode3] Is Null) AND ((tblInspectionRecords.DTimeStamp) Is Null) AND ((BidTable.BidId)=[Forms]![frmInspectionReports]![txtNode1] Or [Forms]![frmInspectionReports]![txtNode1] Is Null))
ORDER BY [Building Inventory].[Building Name];

It was working perfectly down the chain until I got to the second from last fields "Level Inventory", which is a text field, rather than long integer.. It displays nothing when run.

 

[cyberman55]

Pat - okay, got your point and converted all possible group-by fields to where. Some of the group by's are needed. Now I get this, after Access magically reinterpreted the query grid display:

PARAMETERS [Forms]![frmInspectionReports]![StartDate] DateTime, [Forms]![frmInspectionReports]![EndDate] DateTime, [Forms]![frmInspectionReports]![txtNode1] Long, [Forms]![frmInspectionReports]![txtNode2] Long, [Forms]![frmInspectionReports]![txtNode3] Text ( 255 ), [Forms]![frmInspectionReports]![txtNode4] Text ( 255 );
SELECT DISTINCT BidTable.CustomerCompanyName, [Building Inventory].[BLDG Code], [Building Inventory].[Building Name], [Level Inventory].[Level #], IIf(IsNull([tblInspectionRecords].[DTimeStamp]),"Not Inspected","Inspected") AS Status, [Space Inventory].SF, [Space Inventory].SpaceID
FROM ((BidTable INNER JOIN [Building Inventory] ON BidTable.BidId = [Building Inventory].BidID) INNER JOIN [Level Inventory] ON [Building Inventory].[BLDG Code] = [Level Inventory].[BLDG Code]) INNER JOIN ([Space Inventory] LEFT JOIN tblInspectionRecords ON [Space Inventory].SpaceID = tblInspectionRecords.SpaceID) ON ([Level Inventory].[Level #] = [Space Inventory].[Level #]) AND ([Level Inventory].[BLDG Code] = [Space Inventory].[BLDG Code])
WHERE (((BidTable.BidId)=[Forms]![frmInspectionReports]![txtNode1]) AND ((tblInspectionRecords.DTimeStamp)>CDate([Forms]![frmInspectionReports]![StartDate])-1 And (tblInspectionRecords.DTimeStamp)<CDate([Forms]![frmInspectionReports]![EndDate])+1)) OR (((tblInspectionRecords.DTimeStamp)>CDate([Forms]![frmInspectionReports]![StartDate])-1 And (tblInspectionRecords.DTimeStamp)<CDate([Forms]![frmInspectionReports]![EndDate])+1) AND (([Forms]![frmInspectionReports]![txtNode1]) Is Null)) OR (((BidTable.BidId)=[Forms]![frmInspectionReports]![txtNode1]) AND ((tblInspectionRecords.DTimeStamp) Is Null)) OR (((tblInspectionRecords.DTimeStamp) Is Null) AND (([Forms]![frmInspectionReports]![txtNode1]) Is Null))
GROUP BY BidTable.CustomerCompanyName, [Building Inventory].[BLDG Code], [Building Inventory].[Building Name], [Level Inventory].[Level #], IIf(IsNull([tblInspectionRecords].[DTimeStamp]),"Not Inspected","Inspected"), [Space Inventory].SF, [Space Inventory].SpaceID
HAVING ((([Building Inventory].[BLDG Code])=[Forms]![frmInspectionReports]![txtNode2])) OR ((([Forms]![frmInspectionReports]![txtNode2]) Is Null)) OR ((([Building Inventory].[BLDG Code])=[Forms]![frmInspectionReports]![txtNode2])) OR ((([Forms]![frmInspectionReports]![txtNode2]) Is Null))
ORDER BY [Building Inventory].[Building Name];

But, why doesn't the technique work with text fields?

 

[Pat Hartman]

I forgot to mention that the QBE completely messes up complex where clauses. When you have a complex where clause, you can build it yourself in SQL view and from that point forward, you must remember to NEVER save the query in QBE view. ALWAYS save the query from SQL view.

Please rewrite the where clause again. This time remove all the parentheses except those I included in my example or those that you know you need to add. Access puts in so many extraneous parens, it makes them completely unreadable.

 

[cyberman55]

Thanks Pat, this stuff is my weak point, I have QBE dependency and when I do need to "write" SQLfor insertion in code, I use Allen Browne's handy little converter which takes QBE SQL pasted into a text box and outputs code-compatible SQL.

I've got some looming deadlines, so I'll probably just make compromises and limit some of the user choices. I do wish I could understand one thing, and perhaps its just too complex for a QBE approach, causing it to malfunction. The one thing I can't understand is that as I add parameters based on form text boxes, I start with BidID (customer) and it works, showing all if the associated text box is null or a specific customer if it's not null. Moving down the chain, the next parameter, a long in the [Bldg Code] field works similarly.

But, it displays nothing when I add the parameter for txtNode3 which is a text field "Floor" (field [level #]). This is the only thing that's got me stumped. The QBE view is ugly, but the thing works fine down through the building level.

 

[cyberman55]

Hi Pat: I uploaded the form with all supporting tables, queries and code - it's probably worth downloading, if just to see Jan's beautiful subform, a code-based treeview. Download (1MB) zip from here: https://app.box.com/s/amrekqjkwk5kg1jkb2n1 requires AC10, maybe AC07. I'll repost this on the "complexity" thread too.

 

[Pat Hartman]

If the criteria works to a point. There is something wrong with the criteria where it stops working. Remove all the other criteria and just work with the one that is giving you trouble. The string may not be what you think it is. Is there really a table level lookup that is masking the actual value of Floor?

 

[cyberman55]

It pulls the floor [level #] from the tree and it is correct since the same node value (put into control txtNode3) works for other queries.

 

[cyberman55]

Just in case someone stumbles on this, the answer to the text field problem with parameters was answered by Allen Browne, here: http://allenbrowne.com/bug-13.html under "Flaws In Access" in which he states that parameters of type text are evaluated wrongly.

Pat was correct in many regards, although I found the work-around, for me, was to avoid parameter queries and build a temp table to deal with the lack of normalization. Note that I needed just one field for the work assignment ID 20 years, and then made the mistake of adding columns for additional "shifts" and am living with that. Given my QBE-dependency, I used Allen Browne's little conversion utility (from QBE SQL to Code-compatible SQL) and then added variables to the SQL string to deal with optional field selection criteria. It works for me and runs quickly on an 8000 row record set, so I'm happy. Code used looks like this:

Dim strSQL As String
Dim i As Integer
Dim strField As String
Dim intTextNode As Integer

Select Case NodeLevel
Case 0
strField = "BidID"
intTextNode = 1
Case 1
strField = "BidID"
intTextNode = 1
Case 2
strField = "Bldg Code"
intTextNode = 2
Case 3
strField = "Level #"
intTextNode = 3
Case 4
strField = "SpaceID"
intTextNode = 4
End Select

strSQL = "SELECT tblTempZonesandWAa.ZoneID, tblTempZonesandWAa.Zone, tblTempZonesandWAa.Supervisor, tblInspectionRecords.ScoreTotal, tblInspectionRecords.UniqueID, tblInspectionRecords.DTimeStamp, tblInspectionRecords.OverallPassThreshold, [Space Inventory].SpaceID, [Space Inventory].FTE1, [Space Inventory].BidID INTO tbltempZones " & vbCrLf & _
"FROM [Space Inventory] INNER JOIN (([employee info] INNER JOIN tblTempZonesandWAa ON [employee info].[Employee ID Code] = tblTempZonesandWAa.WAID) INNER JOIN tblInspectionRecords ON [employee info].[Employee ID Code] = tblInspectionRecords.WAID1) ON [Space Inventory].SpaceID = tblInspectionRecords.SpaceID " & vbCrLf & _
"WHERE (((tblTempZonesandWAa.ZoneID)=[Forms]![frmInspectionReports]![ListSpecificallyFor]) AND (([Space Inventory]." & strField & ")=[Forms]![frmInspectionReports]![txtNode" & intTextNode & "])) OR (((tblTempZonesandWAa.ZoneID)=[Forms]![frmInspectionReports]![ListSpecificallyFor]) AND (([Forms]![frmInspectionReports]![txtNode" & intTextNode & "]) Is Null));"

DoCmd.RunSQL strSQL

For i = 2 To 7 'this deals with the lack of normalization of the work assignment columns ([WAID2-7])

strSQL = "INSERT INTO tbltempZones ( ZoneID, [Zone], Supervisor, ScoreTotal, UniqueID, DTimeStamp, OverallPassThreshold, FTE1, BidID ) " & vbCrLf & _
"SELECT tblTempZonesandWAa.ZoneID, tblTempZonesandWAa.Zone, tblTempZonesandWAa.Supervisor, tblInspectionRecords.ScoreTotal, tblInspectionRecords.UniqueID, tblInspectionRecords.DTimeStamp, tblInspectionRecords.OverallPassThreshold, [Space Inventory].FTE" & i & ", [Space Inventory].BidID " & vbCrLf & _
"FROM ([employee info] INNER JOIN tblTempZonesandWAa ON [employee info].[Employee ID Code] = tblTempZonesandWAa.WAID) INNER JOIN ([Space Inventory] INNER JOIN tblInspectionRecords ON [Space Inventory].SpaceID = tblInspectionRecords.SpaceID) ON [employee info].[Employee ID Code] = tblInspectionRecords.WAID" & i & vbCrLf & _
"WHERE (((tblTempZonesandWAa.ZoneID)=[Forms]![frmInspectionReports]![ListSpecificallyFor]) AND (([Space Inventory].FTE" & i & ")>0) AND (([Space Inventory]." & strField & ")=[Forms]![frmInspectionReports]![txtNode" & intTextNode & "] Or [Forms]![frmInspectionReports]![txtNode" & intTextNode & "] Is Null));"

DoCmd.RunSQL strSQL

Next i

Pat, please mark this as solved if you care to, and thanks for your help.