Query on multi-valued fields

[MajP]

The result looks like this

Select * from tblMain  WHERE 
ContractType IN ('CAC', 'Case Manager') AND 
County.value IN ('Bronx', 'Cayuga', 'Chemung') AND 
PriorityCategory.value IN ('Other/Unknown', 'Underserved Victims of Crime')

 

[mike60smart]

Hi

You said "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."

I would imagine that in each Financial Year you would want to record all the costs incurred for a specific year.

 

[lonerjohn1078]

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

This is amazing. I really appreciate it. It pulls all of the information, but when I pass it to the report, it gives me all of the records instead of the queried information. I must have the syntax wrong. My report name is "rptqueryresults"

I used the line of

DoCmd.OpenReport "rptqueryresults", acViewPreview, strFilter

 

[lonerjohn1078]

Hi

You said "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."

I would imagine that in each Financial Year you would want to record all the costs incurred for a specific year.

The FY1516 field would keep track of the contract value for that particular fiscal year. I would only need to keep track of the running total for each contract payee across all fiscal years. There does not appear to be (at least until they tell me otherwise) a need to track the total contract values by payee across all contract types.

 

[MajP]

This was designed to work your original way by setting the sql string. Notice in this line it adds WHERE. You do not have where in a openreport filter.
strFilter = " WHERE " & strFilter

You can comment out that line if using for a openreport or form method.

 

[lonerjohn1078]

This was designed to work your original way by setting the sql string. Notice in this line it adds WHERE. You do not have where in a openreport filter.
strFilter = " WHERE " & strFilter

You can comment out that line if using for a openreport or form method.

Are you saying my code should look like this? Cause I commented out the where line it's still pulling all records. Again I'm really sorry for my lack of knowledge and truly appreciate all of the assistance I've been given.

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
   
      
    DoCmd.OpenReport "frmreports2", acViewPreview, strFilter

 

[mike60smart]

Hi

If you ever decide to get rid of the Multi Value Fields then the attached relationships is one way to go.

 

[lonerjohn1078]

Hi

If you ever decide to get rid of the Multi Value Fields then the attached relationships is one way to go.

View attachment 73598

I understand. But my office would like to keep the MVFs.

 

[lonerjohn1078]

I think I got it. I was using strFilter as the WHERE instead of strSQL. It's pulling more accurate results now. Let me do some testing to make sure it's truly pulling all of the data. But thank you so much to all of you for your valuable input. You are all amazing!

 

[MajP]

The problem with MVF was that they were supposed to make things easier. That is true for very limited things. Everything else they make much harder and you really have to understand how they work. I do not think MS provides very good information on how to work with these for anything non trivial. In the future, you will really want to consider how you plan to use them and if it is worth it.

 

[lonerjohn1078]

I'd have to agree with that. I'm never using them again. I'm almost thinking it would've been better to start the whole thing from scratch rather than to continue what someone else started. It's working exactly how the office wants it to so far (thanks in part to all of you.) I'm still doing some testing to make sure it's pulling the right values, but so far so good.

 

[MajP]

I usually have on my filter forms an option group with an "And" "OR" option. If you select AND the filter Ands the choices if you select Or it Ors the choices.

You can easily add that feature to your form.

'You can put code here to make it an AND or OR
  AndOr = " AND "

You can put your option group on the form, and add something like

If me.OptionGrpAndOr = 1 then
  AndOr = " AND "
Else
  AndOr = " Or "
end if

Then you can do filters either way

Select * from tblMain  WHERE 
ContractType IN ('CAC', 'Case Manager') AND 
County.value IN ('Bronx', 'Cayuga', 'Chemung') AND 
PriorityCategory.value IN ('Other/Unknown', 'Underserved Victims of Crime')

Select * from tblMain  WHERE 
ContractType IN ('CAC', 'Case Manager') OR 
County.value IN ('Bronx', 'Cayuga', 'Chemung') OR 
PriorityCategory.value IN ('Other/Unknown', 'Underserved Victims of Crime')

Two very different results.

 

[lonerjohn1078]

That's a good option as well. I added it. Thanks for the suggestion.

 

[isladogs]

There's what I assume is an unintended typo in post #32.

Underserved victims of crime

Made me smile anyway

I also use the AND/OR option group approach. Its very useful.

 

[lonerjohn1078]

I'm not seeing the typo. Were you reading it as "undeserved" instead of "underserved"?

 

[isladogs]

I assumed it should be 'undeserved'. Am I wrong?

 

[lonerjohn1078]

It's underserved, as in provided with inadequate services. Though undeserved does take on a whole different meaning lol

 

[isladogs]

So what do you call it when crime victims are adequately or even well supported? Somehow doubt its 'overserved victims ….'

 

[lonerjohn1078]

lol touché. I'm not sure there is a specific term that we use for it. My guess is if they're adequately being provided services they'd fall into one of the categories like domestic violence. I've only been here less than 2 months, so that's a question I'm going to have to ask now lol