Query on multi-valued fields

[lonerjohn1078]

I have a query form, frmReports2, that I'd like the users to select criteria from to run a report on. The form contains 3 multi-valued fields: ContractType, County, and PriorityCategory.

County and PriorityCategory are stored as multi-valued fields in the table, tblMain. These fields sometimes contain multiple values, as sometimes a contract can cover more than one county and more than one priority category.

If I run the module below with just pform1, pcontrol1, and pfield1, it runs fine without issues.

However, once I add the second (or third) field from which to query from, I get an error message of "The multi-valued field 'County' cannot be used in a WHERE or HAVING clause."

The query criteria is populating properly in the immediate window. But the error pops up on the line calling the report.

I've gotten this module online and adjusted it accordingly to my needs, but I can't seem to figure out how to get around the error.

In the module, the "1" variables are for the ContractType, the "2" variables are for Counties, and the "3" variables are for the PriorityCategory. pform1, pform2, and pform3 are the same value of frmReports2.

Can anyone help me with this? I'm not very strong at Access.

Function MultipleContractsCriteria(pform1 As Form, pcontrol1 As ListBox, pfield1 As String, pform2 As Form, pcontrol2 As ListBox, pfield2 As String, pform3 As Form, pcontrol3 As ListBox, pfield3 As String)

    Dim var1 As Variant
    Dim var2 As Variant
    Dim var3 As Variant
    Dim strcriteria As String
    Dim strcriteria1 As String
    Dim strcriteria2 As String
    Dim strcriteria3 As String
        
    For Each var1 In pcontrol1.ItemsSelected
        strcriteria1 = strcriteria1 & pfield1 & " = '" & pcontrol1.ItemData(var1) & "' or "
    Next var1
            
    For Each var2 In pcontrol2.ItemsSelected
        strcriteria2 = strcriteria2 & pfield2 & " = '" & pcontrol2.ItemData(var2) & "' or "
    Next var2
    
    For Each var3 In pcontrol3.ItemsSelected
        strcriteria3 = strcriteria3 & pfield3 & " = '" & pcontrol3.ItemData(var3) & "' or "
    Next var3
                
    strcriteria = Left(strcriteria1, Len(strcriteria1) - 4) & " and " & Left(strcriteria2, Len(strcriteria2) - 4) & " and " & Left(strcriteria3, Len(strcriteria3) - 4)
        
    
    Debug.Print strcriteria
    
    DoCmd.OpenReport pform1.Name, acViewPreview, , strcriteria
            
    Set pform = Nothing
    Set pcontrol = Nothing
        

End Function

 

[theDBguy]

Hi. Try doing it manually first, if you can, just to see how you might be able to filter the MVFs. If you succeed, then you could try coding it.

 

[lonerjohn1078]

I tried using frmReports1, which has 5 dropdown menus that users can select the criteria from for each of the 3 fields. (5 for county, 5 for contract type and 5 for Priority Category. That works, but is limited to the 5 selections. I also had to manually code each possibility. (i.e. if there are no selections across any of the fields, that's one report/query. If there's no sections in the first, but at least one selection in the second, that's another report/query, etc. I'm trying to avoid having to specifically program each possibility.

(I'm probably not explaining this properly, so I apologize.)

 

[theDBguy]

Hi. It's okay. I was just suggesting since you're having issues with coding the criteria for the MVFs, then try doing it manually first. If it works, then you know it's possible. If so, then we can figure out how to code it. For example, try adjusting the record source of the report to add a criteria for one of the MVFs. Does it work?

 

[isladogs]

As DBG hasn't mentioned it, I will.
Working with MVFs is a total PITA. Easy to get data in ...but much harder to get it out.
Most experienced developers avoid the use of MVFs - much better to create a separate table with a 1 to many join

See this article for more info on why they aren't a good idea http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763

 

[lonerjohn1078]

That does work if I put the criteria under the "County.Value" and "PriorityCategory.Value" columns. I have tried calling those in the module, but I must be doing something wrong.

 

[theDBguy]

That does work if I put the criteria under the "County.Value" and "PriorityCategory.Value" columns. I have tried calling those in the module, but I must be doing something wrong.

Review the SQL statement of the working example and try to duplicate it in your code.

 

[lonerjohn1078]

When I add ".value" to the Call MultipleContractsCriteria line, I get an error message about object required. I used

Call MultipleContractsCriteria(Me, Me!lstcontracttype, "contracttype", me, me!lstcounties.value, "county", me, me!lstprioritycategory.value, "PriorityCategory")

 

[theDBguy]

When I add ".value" to the Call MultipleContractsCriteria line, I get an error message about object required. I used

Call MultipleContractsCriteria(Me, Me!lstcontracttype, "contracttype", me, me!lstcounties.value, "county", me, me!lstprioritycategory.value, "PriorityCategory")

Yes, the error makes sense to me because the "Value" part of a MVF is only accessible through a Recordset object. Rather than simply "calling" it, you may have to modify your function MultipleContractsCriteria() to handle MVFs.

Please remember my point, try to duplicate the working SQL statement (from your little experiment earlier) into the end product of your function or process.

 

[lonerjohn1078]

How would I do that?

 

[theDBguy]

How would I do that?

For example, let's say your report has the following SQL statement:

SELECT TextField, MVFname FROM TableName

Now, let's say your code produces something like:

strCriteria = "[TextField='something'"

And when you open the report using DoCmd, you pass strCriteria in the WhereCondition argument. If so, in essence, you have practically modified the report's SQL statement to something like:

SELECT TextField, MVFname FROM TableName WHERE TextField='something'"

Correct? If so, then I am saying if the working SQL statement looks like this:

SELECT TextField, MVFname FROM TableName WHERE MVFname.Value In(1,2)

Then, try to modify your code so the variable ends up assigning exactly that. For example:

strCriteria = "MVFname.Value In(1,2)"

Hope it makes sense...

 

[lonerjohn1078]

What you're saying makes sense, but I'm just not able to replicate it in the code. I even tried creating a brand new button on frmReports2 and have the code below, but despite making a selection in lstCounties, the selection is not being recognized:

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Set db = CurrentDb
    Set qdf = db.QueryDefs("testing_query")


    strSQL = "SELECT tblmain.* " & _
            "FROM tblmain " & _
            "WHERE tblmain.county = '" & Me.lstCounties.Value & "'"
            
    qdf.SQL = strSQL
    
    DoCmd.OpenQuery "testing_query"
    
    Set qdf = Nothing
    Set db = Nothing

 

[theDBguy]

Hi. Are you able to post a sample copy of your db with test data?

 

[lonerjohn1078]

Here is a copy. It's in the very beginning stages, so please excuse how ugly/messy it is.

Again, I really apologize for my lack of knowledge and truly appreciate any assistance possible.

Module1 contains the module. You can ignore Module2. The qryrptby... queries are my manual attempts to get it to work using frmReports3.

Testing_query has all the fields and can be used as the query to display the results. Let me know if you need any additional information.

 

[mike60smart]

Hi

I would agree with Colin and get rid of the MVF Fields and store the values in a separate table.

Much easier then to extract data.

 

[lonerjohn1078]

I'm inheriting this database from someone else. As I indicated earlier, I'm not very strong in Access. I taught myself what little I do know by looking stuff up in Access books and on the internet. Most of what I've learned is trial and error, so I may know how to do what you're referring to, but I'm not completely sure. Again, I apologize for my lack of knowledge.

 

[mike60smart]

Hi

What do the fields FY1415, FY1516 etc represent??

 

[lonerjohn1078]

Contracts run from October of one year to September of the next. The FY number represents the starting and ending years of the contract. The field will be keeping track of the value of the contract for that particular fiscal year and will keep a running total in the Total field.

 

[mike60smart]

Hi

Again these vales should be in a separate table

 

[MajP]

Here is a pretty complete solution. You just have to put the sql into a qdf or pass it to the report. Personally I usually do not do the whole sql, but only the where clause. Then pass in the where clause as a filter to the report.

Private Sub cmdRunReports_Click()
  Dim filterContract As String
  Dim filterCounty As String
  Dim filterPriority As String
  Dim strFilter As String
  Dim strSql As String
  Dim i As Integer
  Dim idx As Variant
  Dim lst As Access.ListBox
  Dim AndOr As String
  
  'You can put code here to make it an AND or OR
  AndOr = " AND "
  
  '------------ Do contract
  Set lst = Me.lstContractType
  For Each idx In lst.ItemsSelected
    If filterContract = "" Then
      filterContract = "'" & lst.ItemData(idx) & "'"
      'Debug.Print filterContract
    Else
      filterContract = filterContract & ", '" & lst.ItemData(idx) & "'"
      
    End If
  Next idx
  If filterContract <> "" Then
      filterContract = "ContractType IN (" & filterContract & ")" & AndOr
  End If
  'Debug.Print filterContract
  '----------------Do Counties
  Set lst = Me.lstCounties
  For Each idx In lst.ItemsSelected
    If filterCounty = "" Then
      filterCounty = "'" & lst.ItemData(idx) & "'"
    Else
      filterCounty = filterCounty & ", '" & lst.ItemData(idx) & "'"
    End If
  Next idx
  
  If filterCounty <> "" Then
      filterCounty = "County.value IN (" & filterCounty & ")" & AndOr
  End If
  '  Debug.Print filterCounty
  '-------------- Do Priority
  Set lst = Me.lstPriorityCategory
  For Each idx In lst.ItemsSelected
    If filterPriority = "" Then
      filterPriority = "'" & lst.ItemData(idx) & "'"
      'Debug.Print filterPriority
    Else
      filterPriority = filterPriority & ", '" & lst.ItemData(idx) & "'"
   End If
  Next idx
  
  If filterPriority <> "" Then
      Debug.Print filterPriority
      filterPriority = "PriorityCategory.value IN (" & filterPriority & ")" & AndOr
  End If
 '   Debug.Print filterPriority
  
 '------------------- need to add more code to check for which strings have a value
  strSql = "Select * from tblMain "
  strFilter = filterContract & filterCounty & filterPriority
  If strFilter <> "" Then
    strFilter = " WHERE " & strFilter
    strFilter = Left(strFilter, Len(strFilter) - Len(AndOr))
    strSql = strSql & strFilter
  End If
    Debug.Print strSql
End Sub