Solved

Use IIF and Dlookup together

Posted on 2013-11-04
16
334 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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
 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

828 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