Solved

iif statement in query

Posted on 2012-04-13
7
365 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 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
Industry Leaders: 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!

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

724 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