Iif statement showing all

[mbamber]

Hi all,

I need an iif statement in my query criteria that works as follows:

iif([Field] = "FirstPossible","str1",iif [Field] = "SecondPossible","str2",*)

The problem is, I'm not sure how to display all of the records if neither the first iif or second iif returns true.

Any ideas??

TIA

 

[jdraw]

Can you describe in plain English WHAT you are trying to accomplish? Tell us in non access terms WHAT your Query means.

 

[mbamber]

Ok. Its for a badminton club.

If the league format is mens, show only men. If the league format is ladies, show only ladies. If it is neither (i.e. league format is mixed or combo ...) show both men and ladies.

 

[mbamber]

It OK, I just needed a 'Like' at the beginning.

Thanks

 

[KyleB]

First I apologize if I'm not supposed to post on a <Solved> problem, but for me this does not solve my problem, but it does match my situation.

I've encountered this issue as well, but the statements above do not fix my problem. I'm attempting to write the query so that it's not hard coding conditions, but relying on the combo box entry. Here's a SQL that works correctly only when the combo box entry exists in the table of data, but does not work to select all records if the combo box "all" item is chosen from the list:

SELECT tblDep.txtDep, tblCat.txtCat, tblYrs.lngYr
FROM tblODat INNER JOIN (tblYrs RIGHT JOIN (tblMne RIGHT JOIN ((tblCat RIGHT JOIN tblDatHdr ON _
tblCat.CatID = tblDatHdr.FKCat) LEFT JOIN tblDep ON tblDatHdr.FKDep = tblDep.DepID) ON _
tblMne.MinID = tblDatHdr.FKMne) ON tblYrs.YrID = tblDatHdr.FKFcstCret) ON tblODat.[FKDatHdr] = tblDatHdr.PKDatHdr

WHERE (((tblDep.txtDep)=IIf([Forms]![frmRepCtrl]![txtDeposit]<>"All",[Forms]![frmRepCtrl]![txtDeposit],_
Like "*")) AND ((tblCat.txtCat)=[Forms]![frmRepCtrl]![txtCategory]));

The tables contain the data, and the combo box is linked to the tblDep, so that new entries can be added/removed and the list stays dynamic.

My problem is that one entry in the tblDep is "All" so that the query can select all data from the master table. This does not work however. There's something I'm not doing correctly with the wildcard functionality I think. Access doesn't have an "evaluate formula" function like Excel, which makes it a tad difficult to track down errors sometimes.

Some attempted clarification: tblDep contains only four lines currently -
Kensington
Raven
Jualin
All

The combo box picks these values fine, and I have a text box that captures the text value to use in the query criteria. If Kensington, Raven, or Jualin are picked in the combo box, the query works as designed. If All is chosen instead, I want the query to select all data for all three values, but I don't want to code the iif statement with the text strings, as the list could change in the future, and I want the iif statement to be dynamically able to allow for a new entry. However, I cannot get a wildcard select to work, with "*", Like "*", "", is null, Null....all of those give me a blank select list.

 

[mbamber]

I am probably wrong but you could try enclosing the Like in single quotes:

'Like "*"'

I seem to recall having similar problems such that the Like has to be included as part of the query criteria - not just the wildcard.

Like I say, I'm not exactly sure but hey, it's something right!

 

[KyleB]

Thanks, that's a variant I hadn't tried, but no dice. I still get a blank selection set.

 

[mbamber]

Well like I say, I'm not really sure.

I know very little about SQL, as I tend to just use Access' query designer (I know it's quite lame but hey, i'm lazy and can't be bothered to learn SQL in any depth!) so I'm probably not much help. But I have had many problems with wildcards before and it usually stemmed from poor placement of Quote marks.

I suppose you could try different ways of writing that bit, like

'Like *'

or even no quotes (perhaps it is being interpreted as a literal string?)

Like *

If that doesn't work I'm afraid I can't help, but trial and error is your friend - as are the people on here that are much more clever than myself

Good Luck!

 

[KyleB]

I resolved my issue for anyone else looking at this:

I put an afterupdate command on my combo box that set the value of a hidden text box to be equal to the associated column from the combo box unless it was "All" in which case it set it to "*" such as this:

Private Sub cboDeposit_AfterUpdate()
    If Me.cboDeposit.Column(1) = "All" Then
        Me.txtDeposit = "*"
        Else
            Me.txtDeposit.Value = Me.cboDeposit.Column(1)
    End If
End Sub

In the query criteria I used this:

Like "*" & [forms]![frmRepCtrl]![txtDeposit] & "*"

I'm now able to choose a specific deposit, or all deposits depending on what option is picked in the combo box, and if I add new deposits to the underlying table in the future, it's automatically taken care of, as long as the "All" option isn't removed from the list.