Solved

Use IIF and Dlookup together

Posted on 2013-11-04
16
329 Views
Last Modified: 2013-11-13
Can I use IIf and DLookup togehter in access query?  

Status1: IIf([FACaseNo]=-1,"N",IIf([TANFCaseNo]=-1,"N",IIf([FosterChild]=-1,"N",0)))
and
Status: NZ(DLookUp("[IncomeType]","IEG"," [IEG]![HHSize]=" & [IEChild]![HHSize] & " AND [Frequency]='" & [IEChild]![HHFrequency] & "' AND  [IncomeThreshold] >=" & [IEChild]![HHIncome] & ""),"N")

These work separately, but looking for one status field.  Status1 are all checkboxes.  If one is checked, "Yes", the [IncomeType] is "N", otherwise the DLookup.
0
Comment
Question by:softsupport
  • 7
  • 7
  • 2
16 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39622829
Try this:

StatusNew: IIf([FACaseNo] + [TANFCaseNo] + [FosterChild] <> 0 ,"N", NZ(DLookUp("[IncomeType]","IEG"," [IEG]![HHSize]=" & [IEChild]![HHSize] & " AND [Frequency]='" & [IEChild]![HHFrequency] & "' AND  [IncomeThreshold] >=" & [IEChild]![HHIncome] & ""),"N"))

Open in new window

0
 
LVL 30

Expert Comment

by:hnasr
ID: 39623243
mbizup's comment looks fine.

Another way using OR:

IIf[FACaseNo=-1 OR [TANFCaseNo]=-1 OR [FosterChild]=-1,"N", NZ(DLookUp("[IncomeType]","IEG"," [IEG]![HHSize]=" & [IEChild]![HHSize] & " AND [Frequency]='" & [IEChild]![HHFrequency] & "' AND  [IncomeThreshold] >=" & [IEChild]![HHIncome] & ""),"N")
)

Open in new window

or just:
IIf[FACaseNo OR [TANFCaseNo] OR [FosterChild],"N", NZ(DLookUp("[IncomeType]","IEG"," [IEG]![HHSize]=" & [IEChild]![HHSize] & " AND [Frequency]='" & [IEChild]![HHFrequency] & "' AND  [IncomeThreshold] >=" & [IEChild]![HHIncome] & ""),"N")
)

Open in new window

0
 

Author Comment

by:softsupport
ID: 39625735
Does not work in my form because I have a subform with code calculating the income.  I need to include that if the [Frequency] and [IEChild]![HHincome] is null.  When I combine, [Status] field is blank.

Status1: IIf([FACaseNo]=-1,"N",IIf([TANFCaseNo]=-1,"N",IIf([FosterChild]=-1,"N",0)))
and
Status: NZ(DLookUp("[IncomeType]","IEG"," [IEG]![HHSize]=" & [IEChild]![HHSize] & " AND [Frequency]='" & [IEChild]![HHFrequency] & "' AND  [IncomeThreshold] >=" & [IEChild]![HHIncome] & ""),"N")
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39625771
Which of our comments are you responding to?  It would help to describe actual output versus expected output.  Also, if possible post a sample database.
0
 

Author Comment

by:softsupport
ID: 39626168
All solutions work, but only if a [Frequency] and a [IEChild]![HHIncome] exist. I have a form which includes two subforms:
One subform calculates HHIncome, the other calculates the Status
Expected output is :
If [FACaseNo] or [TANFCaseNo] or [FosterChild] is checked, and if Frequency and HHIncome are blank, then [Status] is N, otherwise do the Dlookup below.  

NZ(DLookUp("[IncomeType]","IEG"," [IEG]![HHSize]=" & [IEChild]![HHSize] & " AND [Frequency]='" & [IEChild]![HHFrequency] & "' AND  [IncomeThreshold] >=" & [IEChild]![HHIncome] & ""),"N")

With your solutions above, if income or frequency is blank, no status is displayed.  If income and frequency are entered, status displays. I want check the box for either FACaseNo or TANFCaseNo or FosterChild without entering a frequency or HHincome to display status as N.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39626220
Try this - using NZ to handle the nulls:


StatusNew: IIf([FACaseNo] + [TANFCaseNo] + [FosterChild] <> 0 ,"N", NZ(DLookUp("[IncomeType]","IEG"," [IEG]![HHSize]=" & [IEChild]![HHSize] & " AND NZ([Frequency], 'Null') ='" & [IEChild]![HHFrequency] & "' AND  NZ([IncomeThreshold], -1) >=" & [IEChild]![HHIncome] & ""),"N"))

Open in new window

0
 

Author Comment

by:softsupport
ID: 39629949
Again... thank you for your assistance... This is what is happening with the above solution.

On my parent form... if I only check one of the checkboxes, my HHIncome is automatically calculated as 0.00 and my frequency is blank.  This is set in my vba code and may be causing the issue. (code included below)
Sorry if I repeat myself... trying to clarify.

The Above solution returns a blank [StatusNew] field.  Only if Income and Frequency is entered do I receive a result in [StatusNew].  I would like to check one of the fields [FACaseNo], [TANFCaseNo] or [FosterChild] and give a [StatusNew]  with HHIncome and Frequency being blandk resulting in a [StatusNew] of "N".  (This would occur because the HHINcome and Frequency is not required for a determination.)  Otherwise, the DLookup.

I want the [StatusNew] field to test for 5 items....
[FACaseNo] = 0, then N or
[TANFCaseNo] = 0, then N or
[FosterChild] =0, then N AND
[HHIncome] and [HHFrequency] are blank.  [StatusNew] = N Otherwise do the DLookup.


Here is the code I have for the subform: It calculates the HHIncome with the Frequency.  Can you tell me what in my code below is causing the issue?  Or should my [StatusNew] test be included in this code as well?  and if so, how?

Entire code
Option Compare Database
Option Explicit

Private Sub Form_AfterDelConfirm(Status As Integer)
CalculateHHITotal
End Sub

Private Sub Form_AfterUpdate()
CalculateHHITotal
End Sub

Private Sub CalculateHHITotal()
Dim rs As DAO.Recordset
Dim curIncome As Currency
Dim strFrequency As String
Dim fMixed As Boolean
  Set rs = Me.RecordsetClone
  If rs.RecordCount = 0 Then
    ' no records - set income to zero
    strFrequency = "A"
    curIncome = 0
  Else
    ' get values from first record
    rs.MoveFirst
    strFrequency = rs!Frequency
    curIncome = rs!HIncome
    ' loop through any other records
    rs.MoveNext
    Do Until rs.EOF
      If rs!Frequency = strFrequency Then
        ' same frequency - simply add income
        curIncome = curIncome + rs!HIncome
      Else
        If strFrequency <> "A" Then
          ' annualize what we have so far
          curIncome = AnnualizeIncome(curIncome, strFrequency)
          strFrequency = "A"
        End If
        ' add the annualized income for this record
        curIncome = curIncome + AnnualizeIncome(rs!HIncome, rs!Frequency)
      End If
      rs.MoveNext
    Loop
  End If
  ' set the values on the parent form
  Me.Parent!HHIncome = curIncome
  Me.Parent!HHFrequency = strFrequency
  Set rs = Nothing
End Sub

Private Function AnnualizeIncome(curIncome As Currency, strFrequency As String) As Currency
  AnnualizeIncome = curIncome * DLookup("frqAnnualPeriods", "Frequency", "frqCode='" & strFrequency & "'")
  ' need to handle case where invalid frequency is given
End Function

Open in new window


Thank you
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39629966
Are you able to post a sample copy of your database, with a few records to illustrate the problem, and any personal/sensitive data masked or removed?
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 30

Expert Comment

by:hnasr
ID: 39630114
logical comparison starts from left to right

true or false .... => true whatever comes after
true and false or true ...=> true
otherwise rearrange using brackets:

(true or false) AND (false or false) => false ---Anding true and false
(true or false) OR (false or false) => true    ----oring true and false

So try to rearrange your expressions to modify processing order.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39630219
Try this:


StatusNew: IIf(([FACaseNo] + [TANFCaseNo] + [FosterChild] <> 0) AND (NZ( [IEChild]![HHFrequency], "") = "" AND NZ([IEChild]![HHIncome],"") = "") ,"N", NZ(DLookUp("[IncomeType]","IEG"," [IEG]![HHSize]=" & [IEChild]![HHSize] & " AND [Frequency]='" & [IEChild]![HHFrequency] & "' AND  [IncomeThreshold] >=" & [IEChild]![HHIncome] & ""),"N"))

Open in new window


Double-check that you do indeed want [IEChild]![HHFrequency] and [IEChild]![HHIncome] (your form input) as criteria in the first part (before the DLookup), rather than the actual table fields Frequency and IncomeThreshold... and make the substitutions if needed.
0
 

Author Comment

by:softsupport
ID: 39630764
Not able to post online.  How else can I get to you?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39630895
Try my last post and let me know the results.  Per site rules, we cannot provide offline support, so let's see what we can do in this thread.
0
 

Author Comment

by:softsupport
ID: 39630909
Tried it and it does not work.  Same results... blank
0
 

Author Comment

by:softsupport
ID: 39639041
I thank and appreciate all of the assistance here.  

With your assistance, I found a work around by entering a (1)HHSize and a (0)HHIncome and any frequency.  This solution works, but really wanted just to check one of the boxes to establish a status without using HHSize, HHIncome or Frequency.

I need to import these tables, queries and forms into another database and receiving error messages in line 13 of the code I sent above.  How can I address those issues?  Do I continue asking in this question or do I create another question and refer to this one using the questions ID#?
0
 

Author Closing Comment

by:softsupport
ID: 39642025
I thank and appreciate all of the assistance here.  

With your assistance, I found a work around by entering a (1)HHSize and a (0)HHIncome and any frequency.  This solution works, but really wanted just to check one of the boxes to establish a status without using HHSize, HHIncome or Frequency.

I need to import these tables, queries and forms into another database and receiving error messages in line 13 of the code I sent above.  How can I address those issues?  Do I continue asking in this question or do I create another question and refer to this one using the questions ID#?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39644370
<<
but really wanted just to check one of the boxes to establish a status without using HHSize, HHIncome or Frequency.
>>

Post, in tabular format -- maybe in an Excel file -- some possible values/combiations for the checkboxes and HHIncomme and Frequency and your expected output for the status (N or the DLookup).


<<
I need to import these tables, queries and forms into another database and receiving error messages in line 13 of the code I sent above.
>>
This second issue is really a separate question, which should be posted in another thread.  I wouldn't even recommend including the link to this question, as I think that might confuse things.  Just post it clearly as a standalone question.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
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…

919 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

19 Experts available now in Live!

Get 1:1 Help Now