Value lists with multiple choices 1

[EVE734]

I am trying to create a form (Start/End_date_form) with several unbound list boxes, from which I want to base a parameter query.

This is what the SQL looks like for the query:
SELECT Batch_Info_Table.BA, DOC_List.MD_Name, Batch_Info_Table.[#OFTKTS_IN_BATCH]
FROM Batch_Info_Table LEFT JOIN DOC_List ON (Batch_Info_Table.BA = DOC_List.BA_Mnemonic) AND (Batch_Info_Table.DOC = DOC_List.MD_Mnemonic)
WHERE (((Batch_Info_Table.BA)=[Forms]![Start/End_date_Form]![lbo53]) AND ((Batch_Info_Table.DATERECD) Between [Forms]![Start/End_date_Form]![cbodate1] And [Forms]![Start/End_date_Form]![cbodate2]));

In the "Where" section, the cbodate1 and cbodate2 controls work fine, but for the lbo53 control, I am trying to list values with these properties: Row Source - Value List, Row Source Type - cim;col or gmt;brm or ghn or gtl;pla

The query runs when I choose a value with just one choice (i.e. - cim), but when I want it to run for the choices with multiple values (i.e. - "col" or "gmt"), the query fails.

I hope this makes sense. I'd appreciate any help anyone can provide.

 

[Eupher]

Ah, but as you have set it up (if I understand you correctly), the value in your example is not "col" or "gmt" - it is "col or gmt". Does "col or gmt" correspond to a value in Batch_Info_Table.BA?

Ken S.

 

[EVE734]

Thanks for your response - you are correct. "col" and "gmt" are two separate values in the Batch_Info_Table.Ba. However, the row source doesn't seem to allow me to put in the values "col" or "gmt" for one value - it drops the "or" and the "gmt". I then tried to set up a table where "col" or "gmt" was listed as one value and referred to this table in the row source, but it still didn't work. Any thoughts?

 

[Eupher]

Well, rather directly using the values from your fields as parameters, you could build a SQL string in VBA and change the SQL of your query based on the values in your fields. That would give you more options with regard to parsing the selection from your listbox. Maybe something like this (replace the query name in blue with the name of YOUR query):

Dim CurDB As DAO.Database
Dim qryDef As DAO.QueryDef
Dim SQLStmt As String
Dim strWhere As String

Set CurDB = CurrentDb()
Set qryDef = CurDB.QueryDefs("[COLOR=blue][b]qryMyQuery[/b][/color]")

Select Case Me![lbo53]
    Case "col or gmt"
        strWhere = "(Batch_Info_Table.BA = 'col' Or Batch_Info_Table.BA = 'gmt')"
    Case "brm or ghn or gtl"
        strWhere = "(Batch_Info_Table.BA = 'brm' Or Batch_Info_Table.BA = 'ghn' Or " _
        & "Batch_Info_Table.BA = 'gtl')"
    Case Else
        strWhere = "(Batch_Info_Table.BA = '" & Me![lbo53] & "')"
End Select

SQLStmt = "SELECT Batch_Info_Table.BA, DOC_List.MD_Name,  Batch_Info_Table.[#OFTKTS_IN_BATCH] " _
& "FROM Batch_Info_Table LEFT JOIN DOC_List " _
& "ON (Batch_Info_Table.BA = DOC_List.BA_Mnemonic) " _
& "AND (Batch_Info_Table.DOC = DOC_List.MD_Mnemonic) " _
& "WHERE " & strWhere & " AND (Batch_Info_Table.DATERECD) " _
& "Between #" & Me![cbodate1] & "# And #" & Me![cbodate2] & "#;"

qryDef.SQL = SQLStmt
Set qryDef = Nothing
Set CurDB = Nothing

HTH,

Ken S.

 

[EVE734]

Thanks for your help. Unfortunately, I've been avoiding using VBA altogether. I can follow what you're doing in the code, but don't know how to set it up in my query. I tried a few things, but was unsuccessful.

One more thought: I tried to use the "MultiSelect" property for the list box and then used col and gmt as separate values in the row source. Then to run the query, I highlighted both col and gmt in the form - however, it;s still not working. (Is the query using this criteria as "col" and "gmt"?)

Thanks for bearing with this novice user. I've learned a lot, but still have a long way to go...

 

[Eupher]

That's sorta what I'm saying - you can't really set it up in your query by directly referencing the listbox's value when the selection contains a string that implies multiple values - without somehow building an expression to parse it. Maybe there's a way to do it (queries aren't my strongest suit) but for me it's much easier to get there via VBA.

Changing your listbox to MultiSelect adds a whole new dimension. The value of a MultiSelect listbox is always Null. To get the values, you have to reference the ItemData property of the listbox's ItemsSelected collection. And that means VBA code. I can understand your reluctance to use VBA if you haven't done it before. It certainly adds to the learning curve, but without it you are seriously limiting what you can do with your database. And once you get a handle on it, some things (this particular example comes to mind ) are actually *much* easier to accomplish through code.

Ken S.

 

[EVE734]

OK, OK - I surrender! I will try to play some more with the code you so kindly provided. Would I set it up in a module and then have a macro that refers to this when the query is run? (Last question - I promise!)

Thank you so much Ken,

Evelyn

 

[Eupher]

Evelyn,

How is the query normally invoked?

Ken S.

 

[EVE734]

On the Start/End_date_form, there is an "OK" command button set up to run a macro which runs the query. So, the user would select the values for the fields lbo53, cbodate1 and cbodate2, and then click on "OK" to provide the values for the query and run it.

 

[Eupher]

Okay, in your "OK" command button's property sheet, for the OnClick field, instead of a macro, set it to "Event Procedure", then click the button with 3 little dots immediately to the right of the field. That will open the VB editor and you can paste in this code. As before, change the query name in blue to the name of YOUR query:

Dim CurDB As DAO.Database
Dim qryDef As DAO.QueryDef
Dim SQLStmt As String
Dim strWhere As String

Set CurDB = CurrentDb()
Set qryDef = CurDB.QueryDefs("[COLOR=blue][b]qryMyQuery[/b][/color]")

Select Case Me![lbo53]
    Case "col or gmt"
        strWhere = "(Batch_Info_Table.BA = 'col' Or Batch_Info_Table.BA = 'gmt')"
    Case "brm or ghn or gtl"
        strWhere = "(Batch_Info_Table.BA = 'brm' Or Batch_Info_Table.BA = 'ghn' Or " _
        & "Batch_Info_Table.BA = 'gtl')"
    Case Else
        strWhere = "(Batch_Info_Table.BA = '" & Me![lbo53] & "')"
End Select

SQLStmt = "SELECT Batch_Info_Table.BA, DOC_List.MD_Name, Batch_Info_Table.[#OFTKTS_IN_BATCH] " _
& "FROM Batch_Info_Table LEFT JOIN DOC_List " _
& "ON (Batch_Info_Table.BA = DOC_List.BA_Mnemonic) " _
& "AND (Batch_Info_Table.DOC = DOC_List.MD_Mnemonic) " _
& "WHERE " & strWhere & " AND (Batch_Info_Table.DATERECD) " _
& "Between #" & Me![cbodate1] & "# And #" & Me![cbodate2] & "#;"

qryDef.SQL = SQLStmt
Set qryDef = Nothing
Set CurDB = Nothing

DoCmd.OpenQuery "[COLOR=blue][b]qryMyQuery[/b][/color]"

Once you get that sorted out, you might want to add some code to validate your data before it tries to open the query - i.e. make sure the user entered valid dates, made a selection in the listbox, etc.

HTH,

Ken S.

 

[EVE734]

It works!!!!!! Thank you, Thank you, Thank you!!!!! I don't want to push my luck, but if you have an example of some code to validate data, I'll take it!

With gratitude,
Evelyn

 

[Eupher]

From your naming scheme, I assume cbodate1 and cbodate2 are combo boxes, right? How are they populated?

Ken S.

 

[Eupher]

p.s. FWIW, many (most?) developers don't use a combination of macros and event procedures. Too hard to keep track of and debug. Also, macros don't offer error handling. What I'm saying is if you decide to implement VB procedures in your DB, I recommend going that way completely and converting all macros to VBA.

Ken S.

 

[EVE734]

Yes, they are combo boxes. They have a table as the row source that contains two fields - initial date and end date. Usually the query is run from the first of the month to the last of the month, so those are the values that are in the table. However, I also have the limit to List field property set to no, so other dates could be run if required.

I have one other problem that came up. I need to add another "Where" clause to your code that will always need to have Batch_Info_Table.LOC field = to 7 or 17. I tried inserting an additional Where clause as shown:

SQLStmt = "SELECT Batch_Info_Table.BA, DOC_List.MD_Name, Batch_Info_Table.[#OFTKTS_IN_BATCH] " _
& "FROM Batch_Info_Table LEFT JOIN DOC_List " _
& "ON (Batch_Info_Table.BA = DOC_List.BA_Mnemonic) " _
& "AND (Batch_Info_Table.DOC = DOC_List.MD_Mnemonic) " _
& "WHERE " & strWhere = (Batch_Info_Table.LOC = '7' Or Batch_Info_Table.LOC = '17') " _
& "WHERE " & strWhere & " AND (Batch_Info_Table.DATERECD) " _& "Between #" & Me![cbodate1] & "# And #" & Me![cbodate2] & "#;"

qryDef.SQL = SQLStmt

but it gives me a runtime error at the qryDef.SQL = SQLStmt immediately above. (Expects delete or insert...)

 

[PHV]

& "WHERE " & strWhere & " AND Batch_Info_Table.LOC In ('7', '17') AND Batch_Info_Table.DATERECD " _
& "Between #" & Me![cbodate1] & "# And #" & Me![cbodate2] & "#;"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EVE734]

Thank you PH. Problem resolved - I guess it helps when you know what you are doing!

And Thanks again Ken - I got your P.S. and I understand - I hope to get the hang of all this one day. In the meantime, I really appreciate your patience and help - this works great for what I need right now!

 

[Eupher]

Evelyn,

As far as vaidating your data goes, you can do something simple or you can get really elaborate, just depends on the nature of the data and how tightly you want to control the workflow (vs. how much freedom you want to give the user). A simple approach might be:

If (IsDate(Me!cbodate1) And IsDate(Me!cbodate2)) _
And (Me!cbodate1 <= Me!cbodate2) _
And (Not IsNull(Me!lbo53)) Then
    'all the code from before goes in here
    Else
        MsgBox "Oops!  Invalid date and/or listbox data." & vbCrLf
        & "Verify your entries and try again."
End If

Ken S.

 

[EVE734]

Thanks - I'll give it a shot. In this particular case, it is an administrator that will run the queries, so I am not overly concerned about the user.

I appreciate all your help - I will now leave you alone!