Query Criteria Parameters: Running Out of Characters

[maytime]

I have some pretty length criteria entered into a query as IIf() statements and I am running out of room on the first Criteria line for some of my fields. I'd rather not use multiple criteria lines for query parameters so is there a way to tell the query to look up its criteria from a VBA source or maybe a .txt file?

I tried copying/pasting my long query criteria into VBA but I do not know the right format to put it in there and work like I need it to for my query. Here is my long query criteria for one of the fields in my query:

IIf([forms]![Query Builder Form]![Lab_Assay1]=-1,"Assay Lab #1") 
Or IIf([forms]![Query Builder Form]![Lab_Assay2]=-1,"Assay Lab #2") 
Or IIf([forms]![Query Builder Form]![Lab_PP-XRD]=-1,"Physical Properties/XRD") 
Or IIf([forms]![Query Builder Form]![Lab_XRF-MS]=-1,"XRF/MS") 
Or IIf([forms]![Query Builder Form]![Lab_XRF]=-1,"XRF") 
Or IIf([forms]![Query Builder Form]![Lab_TE]=-1,"Trace Elements") 
Or IIf([forms]![Query Builder Form]![Lab_SS]=-1,"Surface Science") 
Or IIf([forms]![Query Builder Form]![Lab_SamP]=-1,"Sample Prep") 
Or IIf([forms]![Query Builder Form]![Lab_SMMD]=-1,"Sample Management / Methods Development") 
Or IIf([forms]![Query Builder Form]![Lab_RDSM]=-1,"Residue Disposition/Sample Management") 
Or IIf([forms]![Query Builder Form]![Lab_RCNF]=-1,"Radiochemistry") 
Or IIf([forms]![Query Builder Form]![Lab_NDA]=-1,"NDA Laboratory") 
Or IIf([forms]![Query Builder Form]![Lab_MDCA]=-1,"Methods Development / Classical Analysis") 
Or Like IIf([forms]![Query Builder Form]![Lab_All]=-1,"*")

My issue is putting this into VBA and then having the query reference the VBA function to input the criteria. Any ideas or am I stuck using multiple query criteria parameter lines? Thanks.

 

[DCrake]

What is the object of the exercise? You seem to be trying to construct a nested IIF. I have never seen it used, or have never used it using an "Or" parameter.

From what it looks like it seems that you are checking if certain fields have a true value, and if found return a text string.

Please elaborate.

CodeMaster:

 

[maytime]

You basically guessed what I am doing.

I have an unbound form full of check boxes to let a user click whatever boxes he wants and then he runs this query from the form and depending on what check boxes where checked (equal to -1) the data the query shows is filtered. The function of all the IIf statements works great but with some control fields in the query I need more characters to write out all the IIf statements.

I figured I could write something in VBA that the query criteria could link to so I could workaround the character limit within the query design for the criteria boxes.

1) I don't know how to tell the query to look to the VBA code to get the criteria expression.

2) I don't know how to write the criteria expression above in VBA without it saying there is an error.

3) If I could tell my query to look up a .txt file that would be even better. I don't know if I can do that though.

Here is the VBA code I tried to use, which doesn't work. I tried putting "LabCriteria(LabCriteria)" in the query criteria box but that didn't work:

Function LabCriteria() As Boolean
Dim LabCriteria As String

LabCriteria = "IIf([Forms]![Query Builder Form]![Lab_Assay1] = -1, "Assay Lab #1") _
Or IIf([Forms]![Query Builder Form]![Lab_Assay2] = -1, "Assay Lab #2") _
Or IIf([Forms]![Query Builder Form]![Lab_PP-XRD] = -1, "Physical Properties/XRD") _
Or IIf([Forms]![Query Builder Form]![Lab_XRF-MS] = -1, "XRF/MS") _
Or IIf([Forms]![Query Builder Form]![Lab_XRF] = -1, "XRF") _
Or IIf([Forms]![Query Builder Form]![Lab_TE] = -1, "Trace Elements") _
Or IIf([Forms]![Query Builder Form]![Lab_SS] = -1, "Surface Science") _
Or IIf([Forms]![Query Builder Form]![Lab_SamP] = -1, "Sample Prep") _
Or IIf([Forms]![Query Builder Form]![Lab_SMMD] = -1, "Sample Management / Methods Development") _
Or IIf([Forms]![Query Builder Form]![Lab_RDSM] = -1, "Residue Disposition/Sample Management") _
Or IIf([forms]![Query Builder Form]![Lab_RCNF]=-1,"Radiochemistry") _
Or IIf([forms]![Query Builder Form]![Lab_NDA]=-1,"NDA Laboratory") _
Or IIf([forms]![Query Builder Form]![Lab_MDCA]=-1,"Methods Development / Classical Analysis") _
Or Like IIf([forms]![Query Builder Form]![Lab_All]=-1,"*")"
End Function

 

[dkinley]

I think I understand ... If the users are only allowed to check on box, you could cycle through all the controls on that page and 'find' the one that is checked ....

So to populate your lab criteria variable, when the user clicks the go button, this snippet (air code - untested) should run through all of the controls and load the control name into the sLabCriteria variable.

    Dim ctl As Control
    Dim sLabCriteria As String

    For Each ctl In Me
        If ctl.Value = -1 Then
           sLabCriteria = ctl.Name
        End If
    Next

Hope that helps.

-dK

 

[maytime]

That looks like it could work. What do I need to put into the query criteria line to get it to use that VBA code? "LabCriteria(sLabCriteria)"?

 

[dkinley]

I am not sure what you mean, I was under the impression that if a user checked a certain box on the form, then that was the query to use.

Or, does the user check 1 or more checkboxes and if these checkboxes are checked, then the query returns data for those fields?

-dK

 

[maytime]

Basically I have one huge Query with data from multiple tables and other queries as sources that covers pretty much all the data for the database. This is the query that I want to be able to let users filter information out of so they can find specific things if they need to.

This database has information for about 20 different laboratories and the equipment associated with each lab. I want to let a user be able to select a certain lab (or labs), equipment type, and then choices from some other types of controls via check boxes from one unbound form and then let the user run the query with those selections.

All the IIf statements I have above works great when I put them directly into the query criteria entry fields in query design. It would just be easier and cleaner to be able to do it via VBA if I could tell the query to goto the VBA code for each different criteria field.

 

[dkinley]

Unless you make those variables global variables, they won't be able to be passed to the query. A workaround is that you can store those variables on the form in a hidden text box and then reference those text boxes from the query.

It sounds as if you want something more dynamic, like a paramter query which requires the use of DAO or ADO. I am not that great at creating these from the air, I have to grind them out because I rarely use them.

In this manner you can dynamically build your criteria by creating your sql statements on the fly depending on the value of the control. This would probably be the cleanest option if you are wanting to do some VBA.

That being said here is another approach that might be a little cleaner, too. Let's say you have 5 labs, and on the LabName field in the query, you want to limit the records by that lab name. On your form, place a drop-down box and populate it with the lab names.

In the query criteria under LabName, you can place:

Like Forms!FormName!LabNameComboBox & "*"

And this will limit those records to those that meet the lab name. You can also extend this trick to the rest of the fields.

Alot of information and different ways you can do this with what I think it is you are looking for.

-dK

 

[gemma-the-husky]

Personally, I would not try and do this, as it could get too complex
Sometimes you cant do everything programmatically.

Provide a few standard options, but if users want to examine the data in other ways, then consider giving them everything, by exporting a spreadsheet, and then they can play with the spreadsheet to get whatever they want


-------
if you are desparate to give them full options, then instead of nesting ifs, write a function to examine all the fields. This is far easier to write than a single monolithic statement

in the query have a function

needthis(field1,field2,field3,field4, etc) with criteria of true


in a module have a function, and test for everything

function needthis(fld1 as string, fld2 as long, etc) as boolean
{pseudocode
if fld1 was required, and is in range, then needthis=true and exit
if fld2 was required, and is in range, then needthis=true and exit
if fld3 was required, and is in range, then needthis=true and exit
etc
needthis=false
end function

the only thing is that the parameter passing/testing will fail if the parameters are null

so the function call actually needs to say

needthis(nz(field1),nz(field2),nz(field3),nz(field4), etc) with criteria of true

 

[raskew]

Hi -

An interesting problem. Don't know if this will fit in with your purposes, however:

On a form I created an option group (grpLabs) and populated with the 13 possibilities you presented, e.g. LabAssay1, LabAssay2, etc., and All* as the 14th option (set as the default).

Then, in the after_update event of the group:

Private Sub grpLabs_AfterUpdate()
Dim strHold As String
   Select Case Me.grpLabs
      Case 14
         strHold = "*"
      Case Else
         strHold = Choose(Me.grpLabs, "Assay Lab #1", "Assay Lab #2", "Physical Properties/XRD", _
                          "XRF/MS", "XRF", "Trace Elements", "Surface Science", "Sample Prep", _
                          "Sample Management / Methods Development", "Residue Disposition/Sample Management", _
                          "Radiochemistry", "NDA Laboratory", "Methods Development / Classical Analysis")
   End Select
   strXHold = strHold
   Debug.Print strHold
End Sub

...where strXHold is a public string created in a standard module.

It seems to work as advertised, at least to the point that it returns the desired string. I don't have an applicable application where I can test it fully but you might want to see if it would fit in with your needs.

Best wishes -- Bob