iif statement in query

I have an Access query with the following in the criteria for one of the fields


what I am trying to do is as follows.

If the form is open show the records for teh Section_Key on  the form
otherwise show all records

However the query will only the records for the form (if it is open) otherwise it shows no records.

The Section_Key is numeric so using * in the true part of the iif statement does not work.
I tried putting in >1 but this won't work either.  Although if you replace the whole statement with >1 this will show all records.

Any ideas

Thanks Lou
Who is Participating?
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPConnect With a Mentor Commented:
You can use the wildcard * from numeric values is you use Like

Try this as the criteria

Like IIf(Eval(Forms!frmMCS.Form!txtSection_KeyZ)="", "*", Eval(Forms!frmMCS.Form!txtSection_Key)):

Open in new window


Try to put the logical TRUE in the true part to show all records. Normally in a query having a where clause like 'WHERE Id=TRUE' shows all records.

You could use the IsLoaded function in your criteria to determine if the form is open.  Your criteria would be:
Like IIf(IsLoaded("frmMCS"),Forms!frmMCS.Form!txtSection_Key,"*")

If you don't have the IsLoaded function, here it is:
Function IsLoaded(ByVal strFormName As String) As Integer
 ' Returns True if the specified form is open in Form view or Datasheet view.
    Const conObjStateClosed = 0
    Const conDesignView = 0
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
        If Forms(strFormName).CurrentView <> conDesignView Then
            IsLoaded = True
        End If
    End If
End Function

Open in new window

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Rey Obrero (Capricorn1)Commented:
try this

=IIf(Forms!frmMCS.Form!txtSection_Key & ""="",[Section_Key],Forms!frmMCS.Form!txtSection_Key)

[Section_Key] is the name of the field we are filtering
LouverrilAuthor Commented:
Thanks to you all.

I used this in the end based mainly on TheHighTech coaches comments.

=IIf(fGetFormValue("Forms!frmMCS.Form!txtSection_Key") & ""="",[Section_Key],fGetFormValue("Forms!frmMCS.Form!txtSection_Key"))

The fGetFormValue is from Leigh Purvis originally and I fiddled with it to make this:

Public Function fGetFormValue(strFormExpression As String)
On Error Resume Next
    gstrReplaceVal = ""
    fGetFormValue = Eval(strFormExpression)
    If fGetFormValue = "" Then
       strFormExpression = gstrReplaceVal
       fGetFormValue = strFormExpression
    End If
End Function

It means the query will run whether or not the frmMCS form is open. Its the only way I could achieve this.

I couldn't get the True to work using the same priniple. The IsLoaded would only work if the form was loaded. And Capricorns idea needed the form to be loaded for teh[Section_Key] bit.
Rey Obrero (Capricorn1)Commented:

part of the solution did not get any points ?
 and, the solution that worked for you is the combination of the ideas presented.
LouverrilAuthor Commented:
MANY APOLOGIES to everyone. I pasted the wrong statement in my post describing what worked.

In my defence I had diligently tried them all out as you had all taken the time to help me - and I had bits of iif statement all over a notepad.

This is what I meant to post: -

Like IIf(fGetFormValue("Forms!frmMCS.Form!txtSection_Key")="","*",fGetFormValue("Forms!frmMCS.Form!txtSection_Key"))

Which is based on Hi Tech Coach's idea (plus the info I had found a few months ago on another forum) . So I think I gave the points fairly.

Thanks Capricorn for your suggestion and your help in the past.

Hope I am forgiven.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.