IIF function as criteria in a query?

[johandenver]

Hello!

I use a value from a combobox (filled with a value list) as a criteria in a query. The values are basically names of american states.
The works fine but I'd like to make a "Misc" value in the list that catches all record that doesn't belong to any of the states in the list.

I thought of using some sort of IIF as a criteria in the query but I'm not good enough at this to know how to actually make that work.

Like this...

If Combobox=Misc then "exclude all records containing Alabama,Arkansas,Arizona,Colorado and New York
else
use the value of Combobox (the selected state) as the criteria

I hope I'm making any sort of sense here

 

[LPurvis]

So this field in question is entered via a combo sourced by a fixed states list, but the control is not limited to list?
Hence people can enter (and presumably have entered) an alternative free text entry?
You want to consider only those entries which do not include a value from the list?

In every way - it would make vastly more sense if this states list was sourced from a table.
From an application maintenance perspective to fuel that list control and to performing a query which will exclude those results.

The query still shouldn't be a trivial IIF based criteria, but none the less it'll be quantifiable.

Cheers.

 

[Galaxiom]

Once you impliment Leigh's suggestion you won't need to use Misc in the combo.
You will just want to find the invalid states in the table and fix them.

Create a query with a one sided join between the main table and the States table. Set a criteria Is Null on a field from the States table. The results will be all the records with invalid states in the main table.

 

[johandenver]

Well, yes the list is limited so people cannot enter anything, just pick a state from the list.

I'll try to explain a little better
The table looks like this:

Unit:
-----
1st Virginia Infantry Regiment
2nd Pennsylvania Infantry Regiment
1th Alabama Infantry Battalion

In the query I use this as a criteria:
Like "*" & " " & [Forms]![CS]![State_Combo] & "*"

I guess I could make a table with all selectable states but wouldn't that be the same as having them in the value list?
The unit field can never be null so I can't really use that either :-(
The misc value, in my vision at least, would include everything that doesn't have any of the states in the combo in their name.

 

[namliam]

A table is much more easily amended than combo's/value lists.

You want a "search form" this is much more flexible in handling if then this that where else over etc...

Try doing a search, find some samples and see if you can addapt it to your need.

 

[LPurvis]

So what exactly is in your list?
The values:
1st Virginia Infantry Regiment
2nd Pennsylvania Infantry Regiment
1th Alabama Infantry Battalion

or the names of the states themselves?
How is entry performed? It must be this wildcard solution?
(Is there not a predefined list of Regiments from which you could select and relate a state - this would be more efficient that the wildcard matching).

Cheers.

 

[johandenver]

No, the list contain only names of states.
It's predefined as a value list in the combobox.

It's my table (database) that contain a column with values like:
1st Virginia Infantry Regiment
2nd Pennsylvania Infantry Regiment
1th Alabama Infantry Battalion

To sort out only units from Virginia, I simply select Virginia in the combobox and then that value is used as criteria in query shown in a listbox the same form.

 

[LPurvis]

It's still easier with a table of state names.
The best part is surely that there must be a million examples out there with such a table already created for you or ready to import.
(I know I can rustle up a list of UK counties pretty quickly

Cheers.

 

[johandenver]

The problem is that the records I want to show with the "misc" menu item in my list are the ones that does not belong to any state at all.
Because of this I would think that some sort of wildcard is the only option.

For example,
1st Creek Cavalry
Williams' Infantry Regiment
Douglas' Cavalry Regiment

The above entries don't have anything in common expect that they don't contain any of the state names listed in my combobox.

I'm not sure what would be easier if I had all the state names in a table instead of a value list, I would still have the original problem above.
Or do you mean that it would be easier to make an IIF-statement selecting from a table instead of a value list?

 

[Galaxiom]

This is probably best done using VBA to change the recordsource of the subform.
Use the OnChange event of the combo to run code that tests the selection.

If Me!comboname = "Misc" Then
Me!subformname.RecordSource = queryname1
Else Me!subformname.RecordSource = queryname2
End If

Queryname2 is a variable with the name of your original Like query.

Queryname1 is built on the main table and the States table with NO join.
This creates a record for every record in the main table against every state in the States table.

The criteria is set for the main table field as:
Is Not Like "*" & State & "*"

For each main record, this will return a record for every record in the State table except the one that matches. Consequently those which have no state included will have one more record than those which have a state. Group By the field from the main table and set as HAVING Count = 50 (indicating they match no state).

This query will have to be run with only the searched field from the main table otherwise the Group By will return many more records based on the grouping of other fields. Join the query back to the main records to get the other fields back.

Note that this will be a non-updateable recordset. If you need to work with it, save the grouped query to a temporary table and join that to the main table.

 

[Galaxiom]

The technique I described above is a minimalist VBA approach.

Another alternative is to write a function that loops through the names of every state in an Instr function. This technique will avoid having to change Record Source queries like above so it is much more elegant.

Air Code Untested. You will need to add the Error handling.

Start by loading the state names in a Public Variable string array.
Declare these in the top of a Standard module.

Public StateArray() As String
Public StateArrayUBound As String

This Sub will pull them from the Row Source List of your combo. They can be pulled from a table instead if that is where you have them now but you seem to prefer the Row Source list.

Use the OnLoad event of the form. Be aware that you will need to rerun it after any code breaks.

Sub MakeStateArray()
 
Dim StateList As String
 
   StateList = Me.comboname.RowSource
   StateList = Replace(StateList, """", "")
   StateArray = Split(StateList, ";")
   StateArrayUBound = UBound(StateArray)
 
End Sub

Use the following function in the query like this.

StateFound: FindState([fieldname])

It returns the first state found in the field or "Misc" if it isn't found so you can search with your combo.

Enter it into the Standard Module.

Public Function FindState(InputString as String) As String
 
Dim n as Integer
Dim Found as Integer
 
   n = 0    ' not strictly essential but included as insurance
   Found = 0  ' ditto
 
   Do While Found = 0 AND n < (StateArrayUBound + 1)
 
      If StateArray(n) <> "Misc" Then
         Found = Instr(InputString,StateArray(n))
      End If
 
      If Found <> 0 Then
         StateFound = StateArray(n)
      Else: n = n + 1
      End If
 
   Loop
 
   If Found = 0 Then
      StateFound = "Misc"
   End If
 
End Function

I would be curious to know whether the function of the query is faster but I imagine you forget about the query after you see this.

 

[johandenver]

Thank you Galaxiom, you guys are worth your weight in gold
I'm deeply grateful that you all take the time to help me out here.

I'll try this as soon as I can. Probably in a few days.
I'll let you know how it works out but, as you hinted, I'll probably go with the looping function. I imagine I can adapt it later on to do different things as well.

/Johan

 

[Galaxiom]

No worries mate as we say in Au.

As I said, the code is untested so don't hesitate to post any glitches you experience with it so I can put them right.

 

[johandenver]

I couldn't quite get it to work.

I put FindState([Full Unitname]) as a criteria for my query but I get "Undefined function 'FindState' in expression" when I open the form.

Also, I tried adding the code below to the On Load event for the form.

Dim StateList As String

StateList = Me.comboname.RowSource
StateList = Replace(StateList, """", "")
StateArray = Split(StateList, ";")
StateArrayUBound = UBound(StateArray)

But I got this error:

 

[Galaxiom]

The first line of the FindState function should be:
Public Function FindState()

The function and Public variable declarations need to be in a Standard Module not the form's module. The varaibles are declared in the top (declarations section) of the module. I have fixed these in my post.

The error may be something to do with the Global variables needing to be declared in a standard module.

 

[johandenver]

Almost there I think

I put the declaration and the function in a normal module and got rid of most of the errors.
However, now I get an error that says "Compile error: Expected end function" when I open the form.

I shouldn't actually put "StateFound: FindState([Full Unitname])" as the criteria in the query, right?
I just have "Findstate ([Full Unitname])" at the moment.

Also, for the "On Load" event on the form I have:

Private Sub Form_Load()
Dim StateList As String

StateList = Me.State_Combo.RowSource
StateList = Replace(StateList, """", "")
StateArray = Split(StateList, ";")
StateArrayUBound = UBound(StateArray)
End Sub

I mean, I can't name the sub MakeStateArray like you wrote in your example cause it's already called Form_Load.

It might just be me being stupid so please correct me if I'm wrong

 

[Galaxiom]

I had End Sub instead of End Function as the last line of the FindState function. Fixed. VBA Editor actually inserts the End Function line by itself and changes End Sub to End Function if you change the code. Just check carefully what it has done in your case.

AWF code tags don't seem to be so clever.

StateFound: just names the new field. Otherwise Access defaults to naming it Expr1.

Originally I had MakeStateArray as a sub with the intent that the Form_Load procedure would call it. You have just given the commands to Form_Load directly. It will work either way.

It is better practice to do it with the call because that groups the related commands into a single procedure. You may later add more stuff to the Form_Load procedure and these are best dealt with in the same way like this.

Sub Form_Load()
   MakeStateArray
   SomeOtherSub
End Sub

In general it is a good idea to separate distinct functionality into stand alone subs and call a sequence of them from other procedures rather than having one big sub that performs many different groups of actions line by line.

BTW: Do Loops can be done many different ways and the following one is slightly tider than the Do While I used in the function. Largely a cosmetic choice.

Do
   etc etc etc
Loop Until StateFound <> 0 Or n = StateArrayUbound

 

[johandenver]

I got it to work with the looping function but it became so extremely slow that I'm now trying to get the query approach to work instead. My database has over 200'000 records, perhaps that's too big to run with the looping function.

I've made two queries now, one that I use to find units from states defined in my list and another query to use for misc. The queries themselves work fine now. The problem I'm having is I can't get the change of queries to work.
There are two things that need to change their source.
One subform (Child31) and another combobox (UnitComboFiltered).
My code is like this at the moment.

Private Sub State_Combo_Change()

If Me!State_Combo.Value = "Misc" Then
Me!UnitComboFiltered.RowSource = FilterByTypeCSMisc
Me!Child31.SourceObject = FilterByStateCSMisc

Else: Me!UnitComboFiltered.RowSource = FilterByTypeCS
Me!Child31.SourceObject = FilterByStateCS
End If

Me!UnitComboFiltered.Requery
Me!Child31.Requery

End Sub

I load the form and whenever I change the State_Combo both Child31 and UnitComboFiltered go completely blank. Wether selecting "Misc" of anything else. This makes me think there's something wrong with that way I try to change query sources.

Do you see any obvious problems in my code?

 

[Galaxiom]

The SourceObject is the subform. The query is the RecordSource of that form.
From the main form:

Me!subformcontrolname.Form.RecordSource

 

[johandenver]

Great, that did the trick.

Everything works fine now at about double the speed with the query vs loop function.

Thanks a lot for all your help.