Solved

iif statement in query

Posted on 2012-04-13
7
354 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now