Possible Multiple Criteria Combinations (1 Viewer)

[o1110010]

Hello friends and strangers!

I am guessing this problem I have has been resolved already but the closest sounding post so far is this which still leaves me wondering.

In a form I have four unbound text boxes. Each of these textboxes I was hoping could be used to narrow a search if data is entered. If no data is entered, it shouldn't use the textbox to filter in the query. Under these textboxes is a subform that is the query. On the form is also a command button to Me.Refresh to start all the happy filtering fun. I have had no luck at getting it to work how I want. I have had luck to either filter for everything (which takes out too many records) or filter it out seperately (Which leaves too many records).

Example, pretend the four unbound boxes help filter these four fields in a table: numID, txtFirstName, txtLastName, dteDate

if I enter numID, it should only show 1 record since it is linked to the index numbers.

if I enter just "Frank" for txtFirstName, it should show the records of "Frank Johnson", "Frank Smith", and "Frank Peterson".

if I enter "Frank" for txtFirstName and "Smith" for txtLastName, it should only show "Frank Smith".

Any help please?

 

[pmcleod]

What about setting up a dynamic query that traps the values entered on a form...something like this....

Option Compare Database

Private Sub Command2_Click()
Dim DB As DAO.Database
Dim QD As QueryDef
Dim where As Variant

Set DB = CurrentDb()

'-- Delete the existing dynamic query; trap the error if the query does not exist.

On Error Resume Next
DB.QueryDefs.Delete ("qryLabsDynamic")
On Error GoTo 0

where = Null
'-- Trap the values entered in the form and pass to the dynamic query

'--Give user option to pull data by up to 5 prov

where = where & " AND [ProvState]= '" + Me![Prov1] + "'"
where = where & " Or [ProvState]= '" + Me![Prov2] + "'"
where = where & " Or [ProvState]= '" + Me![Prov3] + "'"
where = where & " Or [ProvState]= '" + Me![Prov4] + "'"
where = where & " Or [ProvState]= '" + Me![Prov5] + "'"

'--This cannot be combined with the above OR's

where = where & (" AND [LabsSubmittorName_ID]= " + Me![SubmittorName])
where = where & (" AND [LabsCityProv_ID]= " + Me![CityProv])
where = where & (" AND [LabsCertCat_ID]= " + Me![CertificationCat])

'--Give an option to further filter by a create date range

If Not IsNull(Me![CreatedEndDate]) Then
   where = where & " AND [DateCrtd] between #" + Me![CreatedStartDate] + "# AND #" & Me![CreatedEndDate] & "#"
Else
   where = where & " AND [DateCrtd] >= #" + Me![CreatedStartDate] + " #"
End If

'MsgBox "Select * from qryLabsSubmittorItem " & (" where " + Mid(where, 6) & ";")

'--Create and run the query

Set QD = DB.CreateQueryDef("qryLabsDynamic", "Select * from qryLabsSubmittorItem " & (" where " + Mid(where, 6) & ";"))
'--DoCmd.OpenQuery "qryLabsDynamic"
DoCmd.OpenReport "rptDynamicSubmittorList", acViewPreview
End Sub

 

[Mile-O]

Option Explicit
Option Compare Database

Private Sub Command2_Click()
    Dim DB As DAO.Database
    Dim QD As DAO.QueryDef
    Dim strWhere As String

   Set DB = CurrentDb()

    '-- Delete the existing dynamic query; trap the error if the query does not exist.

    On Error Resume Next
    DB.QueryDefs.Delete "qryLabsDynamic"
    On Error GoTo 0

    '-- Trap the values entered in the form and pass to the dynamic query

    '--Give user option to pull data by up to 5 prov

    strWhere = strWhere & " AND [ProvState]= """ & Me.Prov1 & """"
    strWhere = strWhere & " Or [ProvState]= """ & Me.Prov2 & """"
    strWhere = strWhere & " Or [ProvState]= """ & Me.Prov3 & """"
    strWhere = strWhere & " Or [ProvState]= """ & Me.Prov4 & """"
    strWhere = strWhere & " Or [ProvState]= """ & Me.Prov5 & """"

    '--This cannot be combined with the above ORs

    strWhere = strWhere & " AND [LabsSubmittorName_ID]= " & Me.SubmittorName
    strWhere = strWhere & " AND [LabsCityProv_ID]= " & Me.CityProv
    strWhere = strWhere & " AND [LabsCertCat_ID]= " & Me.CertificationCat

    '--Give an option to further filter by a create date range

    If Not IsNull(Me.CreatedEndDate) Then
       strWhere = strWhere & " AND [DateCrtd] Between #" & Me.CreatedStartDate & "# And #" & Me.CreatedEndDate & "#"
Else
       strWhere = strWhere & " AND [DateCrtd] >= #" & Me.CreatedStartDate & "#"
End If

    'MsgBox "SELECT * FROM qryLabsSubmittorItem WHERE " & Mid$(strWhere, 6) & ";")

    '--Create and run the query

    Set QD = DB.CreateQueryDef("qryLabsDynamic", "SELECT * FROM qryLabsSubmittorItem WHERE " & Mid$(strWhere, 6) & ";")
    '--DoCmd.OpenQuery "qryLabsDynamic"
    DoCmd.OpenReport "rptDynamicSubmittorList", acViewPreview

    Set QD = Nothing
    Set DB = Nothing

End Sub

A few changes, hope you don't mind. They won't change what the sub does (to be honest, I never really looked) but I did notice that the criteria doesn't have any bracketing to structure the conditions between the ANDs and the ORs.

Things changed:

• Added Option Explicit keyword to top of code;
• Correctly defined the QueryDef as a DAO object;
• Terminated the QueryDef and Database object at the end of the sub;
• Changed all + concatenations to & concatenations;
• Changed all ' delimiters to "" to make the code more robust;
• Changed the Mid() function to Mid$();
• Changed where from a Variant to a String;
• Renamed where to strWhere to denote its datatype;
• Eliminated unnecessary brackets from the sub;
• Removed an erroneous space in a date delimiter.

 

[EMP]

Things changed:

• Changed all + concatenations to & concatenations;

• Changed where from a Variant to a String;

These two are the basic elements of a dynamic query.

Changing them defeated the whole purpose of the exercise.

 

[Mile-O]

• Changed all + concatenations to & concatenations;
• Changed where from a Variant to a String.

These two are the basic elements of a dynamic query.

Changing them defeated the whole purpose of the exercise.

• Although + can be used, the & is the standard method of concatenation
• As the where variable is building a String and no other data type then it makes sense to dimension the variable as a String than a Variant as it increases efficiency

It doesn't matter if the query is built dynamically, these methods and both standard and more efficient respectively therefore no purpose was defeated; only improved.

 

[EMP]

After the changes, it's no longer a dynamic query.

Microsoft Knowledge Base Article - 210242
ACC2000: Query by Form (QBF) Using Dynamic QueryDef

http://support.microsoft.com/default.aspx?scid=kb;en-us;210242&Product=acc2000


If it is only for standard and efficiency, why use VBA at all? Why not simply build the query in query design?

.

 

[Mile-O]

After the changes, it's no longer a dynamic query.

Microsoft Knowledge Base Article - 210242
ACC2000: Query by Form (QBF) Using Dynamic QueryDef

http://support.microsoft.com/default.aspx?scid=kb;en-us;210242&Product=acc2000

That's the biggest piece of bad practice I've seen by Microsoft:

• Base a form on a table?
• Late binding?
• Bad naming conventions? (especially when they have another article on naming conventions )
• Wrong variable types?

I wouldn't take anything from the knowledge base as gospel or the way to do it

If it is only for standard and efficiency, why use VBA at all? Why not simply build the query in query design?

Because the query needs to be built first.

 

[EMP]

Because the query needs to be built first.

By that, do you mean the query can only be built in VBA? Not in query design?

 

[Mile-O]

By that, do you mean the query can only be built in VBA? Not in query design?

The query can be built in VBA or in the query design grid. In the design grid, however, the query will be explicit whereas VBA will create the explicit query but can rebuild it each time rather than asking the user to do it manually.

 

[Keith27]

I myself have used the dynamic query technique in some of my databases.

When reading this thread, a question flashed through my mind:-
Is Mile's standard query really more efficient than a dynamic query built using the technique published in the Microsoft Knowledge Base Article?


In order to compare the two methods, I created a database, built a table "qryLabsSubmittorItem", built a form with all the text boxes named in the code, copied the two pieces of code to two command buttons, modified the code to place the two SQL strings created in two additional text boxes on the form for viewing purpose.

I deliberately left all the text boxes on the form empty and ran the two pieces of code. These were the results I got:-

SQL string created by the dynamic query code:

Select * from qryLabsSubmittorItem ;​

which was able to be saved as query "qryLabsDynamic"


SQL string created by Mile's code:

SELECT * FROM qryLabsSubmittorItem WHERE [ProvState]= "" Or [ProvState]= "" Or [ProvState]= "" Or [ProvState]= "" Or [ProvState]= "" AND [LabsSubmittorName_ID]= AND [LabsCityProv_ID]= AND [LabsCertCat_ID]= AND [DateCrtd] >= ##;​

which, when being saved as a query, resulted in a syntax error.

Obviously, even when all the text boxes were left blank, Mile's code was still trying to build a Where Clause for the query.


So my conclusion is that Mile's method can't be more efficient than Microsoft's dynamic query technique. I will continue using the dynamic query technique.

 

[Mile-O]

So my conclusion is that Mile's method can't be more efficient than Microsoft's dynamic query technique.

As I said earlier, I didn't really look at the code, I just amended the bits I saw were wrong from a practice and efficiency point of view.

 

[Mile-O]

What's this do for you? I'm guessing that the form is supposed to have a default date in the Order Start Section?

Private Sub cmdRunQuery_Click()

    On Error GoTo Err_cmdRunQuery

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim strWhere As String
    
    Const cstrQuery = "Dynamic Query"
    Const cstrSelect = "SELECT * FROM ORDERS WHERE "
    
    Set db = CurrentDb()
    
    If Not IsNull(Me.Customer_ID) Then
        strWhere = "[CustomerID] = " & Me.Customer_ID & " AND "
    End If
    
    If Not IsNull(Me.Customer_ID) Then
        strWhere = "[EmployeeID] = " & Me.Employee_ID & " AND "
    End If
    
    If Not IsNull(Me.Ship_City) Then
        strWhere = strWhere & "[ShipCity] Like ""*" & Me.Ship_City & "*"" AND "
    End If
    
    If Not IsNull(Me.Ship_Country) Then
        strWhere = strWhere & "[ShipCountry] Like ""*" & Me.Ship_Country & "*"" AND "
    End If
    
    ' Note the number signs (#) surrounding the date field [Order Date].
    If IsDate(Me.Order_End_Date) Then
       strWhere = strWhere & "[OrderDate] Between #" & _
        Me.Order_Start_Date & "# AND #" & Me.Order_End_Date & "# AND "
    Else
       strWhere = strWhere & "[OrderDate] >= #" & Me.Order_Start_Date & "# AND "
    End If
    
    On Error Resume Next
    db.QueryDefs.Delete cstrQuery
    On Error GoTo Err_cmdRunQuery
    
    Set qd = db.CreateQueryDef(cstrQuery, cstrSelect & Left$(strWhere, Len(strWhere) - 5) & ";")
    DoCmd.OpenQuery cstrQuery
    
Exit_cmdRunQuery:
    Set qd = Nothing
    Set db = Nothing
    Exit Sub
    
Err_cmdRunQuery:
    MsgBox Err.Description, vbExclamation, "Example"
    Resume Exit_cmdRunQuery
    
End Sub

 

[EMP]

I am guessing this problem I have has been resolved already but the closest sounding post so far is this which still leaves me wondering.

The problem with putting
[Forms]![MyForm]![MyControl] OR [Forms]![MyForm]![MyControl] Is Null

in the criteria and letting Access fix the structure for you (as suggested by Mile-O-Phile in the linked thread) is that Access will split the criteria into two columns, making it very difficult for you to edit them or add other criteria after the query is saved.

Instead, you can combine the field name with the criteria and put it in a new column in the query grid like this:-
---------------------
Field: [FieldName]=[Forms]![MyForm]![MyControl] Or [Forms]![MyForm]![MyControl] Is Null

Show: uncheck

Criteria: True
---------------------
This way, Access will leave the structure intact and you can easily add the criteria for the other fields.

 

[DLB]

I am eager to learn.

Things changed:

• Changed all + concatenations to & concatenations;
• Changed where from a Variant to a String;

These two are the basic elements of a dynamic query.

Changing them defeated the whole purpose of the exercise.

EMP,

Keith's results showed that your statement is 100% correct.

I am eager to learn. Can you tell me why we must declare Where as a Variant and use + to concatenate strings here?

Thanks.

 

[EMP]

EMP,

Keith's results showed that your statement is 100% correct.

I am eager to learn. Can you tell me why we must declare Where as a Variant and use + to concatenate strings here?

Thanks.

To understand it, we have to understand how the + Operator works. The following is taken from Access's help:-
----------------------------------------
+ Operator

Syntax
result = expression1+expression2

IF
Either expression is Null, result is Null.
----------------------------------------

Microsoft's dynamic query just makes use of this behaviour and builds the criteria string for a field with the + operator e.g.

"[FieldName1] =" + Me!txtValue​

So when the text box is Null, the criteria string for that field becomes Null, too. Similarly for the other fields. Hence when all the text boxes for the criteria are empty, we just have a simple SQL string (e.g. Select * from TableName; ) without a Where Clause.

The Where variable in the code is declared as Variant so that it can hold the Null criteria string.


However, if we use & to concatenate the criteria string, e.g.

"[FieldName1] =" & Me!txtValue​

(as Mile-O-Phile did in what he/she arrogantly described as his/her efficient? and improved? code), when the text box is empty, the criteria string becomes [FieldName1] =, which of course will only result in a syntax error.

 

[raskew]

Just my two cents worth-

I think Mile-o is absolutely on track. Take for example

where = where & " AND [ProvState]= '" + Me![Prov1] + "'"
where = where & " Or [ProvState]= '" + Me![Prov2] + "'"
where = where & " Or [ProvState]= '" + Me![Prov3] + "'"
where = where & " Or [ProvState]= '" + Me![Prov4] + "'"
where = where & " Or [ProvState]= '" + Me![Prov5] + "'"

...that absolutely clunks!! What happens if, for example,
the options go from five to fifteen? Are you going to add
ten more lines of code?
How about:

....
   x = (you provide the number) 
   For i = 1 To x
       strOpt = "Prov" & Format(i)
       strHold = strHold & IIf(Me(strOpt) <> "", Chr(44) & Chr(34) & Me(strOpt) & Chr(34), "")
    Next i    
    strWhere = "In(" & Mid(strHold, 2) & ")"
    strSQL = "Select * from Orders WHERE ShipCountry " & strWhere & ";"
....

....the & versus + issue only comes into play when the developer fails to provide for the possibility that a control may = "".

Bob

 

[EMP]

Bob,

where = where & " AND [ProvState]= '" + Me![Prov1] + "'"
where = where & " Or [ProvState]= '" + Me![Prov2] + "'"
where = where & " Or [ProvState]= '" + Me![Prov3] + "'"
where = where & " Or [ProvState]= '" + Me![Prov4] + "'"
where = where & " Or [ProvState]= '" + Me![Prov5] + "'"

is from pmcleod. It is not in the example in the Microsoft Knowledge Base Article.

It would be unfair to reject the technique simply because pmcleod had those five lines in his/her code and didn't use a loop, wouldn't it?


For 15 ProvStates, you could simply use:-

  Dim i As Integer
  Dim vProv As Variant
  vProv = Null
  
  For i = 1 To 15
     vProv = vProv & """" + Me("Prov" & i) + ""","
  Next i
  
  Where = Where & " AND [ProvState] in (" + vProv + ")"

Since in pmcleod's code ProvState happened to be the first field in the criteria, you could even simplify it to:-

  Dim i As Integer
  
  For i = 1 To 15
     Where = Where & """" + Me("Prov" & i) + ""","
  Next i
 
  Where = " AND [ProvState] in (" + Where + ")"

So the problem is not in the technique itself. It is whether you really understand the technique and know how to apply it.

-

 

[DLB]

Thank you, EMP.

When I first came across the Microsoft KB Article, I was so confused. But after reading your explanation, I am now able to understand it completely.

With the dynamic query, we don't need to test whether the text boxes on a search form are left blank or not, because the + Operator will automatically take care of them. I like the technique.

Thanks again.

DLB

 

[Keith27]

When I first came across the Microsoft KB Article, I was so confused. But after reading your explanation, I am now able to understand it completely.

With the dynamic query, we don't need to test whether the text boxes on a search form are left blank or not, because the + Operator will automatically take care of them. I like the technique.

I agree. It will take some thinking before you can really understand the technique. I think that's why Microsoft have specified it as "Advanced" in the Article header.

But once you understand it, you'll like it.

Keith