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
Solved

iif statement in query

Posted on 2012-04-13
7
360 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

840 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