?
Solved

iif statement in query

Posted on 2012-04-13
7
Medium Priority
?
372 Views
Last Modified: 2012-04-20
I have an Access query with the following in the criteria for one of the fields

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

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
0
Comment
Question by:Louverril
7 Comments
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 2000 total points
ID: 37844197
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

0
 
LVL 2

Expert Comment

by:DSTECH_ADMIN_01
ID: 37844201
Lou,

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.

Danny
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 37844310
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

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37844344
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
0
 

Author Comment

by:Louverril
ID: 37846239
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37846261
Louverril,

part of the solution did not get any points ?
 and, the solution that worked for you is the combination of the ideas presented.
0
 

Author Comment

by:Louverril
ID: 37871597
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.

Lou
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question