Solved

Use IIF and Dlookup together

Posted on 2013-11-04
16
327 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 30

Expert Comment

by:hnasr
Comment Utility
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
Comment Utility
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
Comment Utility
Not able to post online.  How else can I get to you?
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
Comment Utility
Tried it and it does not work.  Same results... blank
0
 

Author Comment

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

763 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

15 Experts available now in Live!

Get 1:1 Help Now