Use IIF and Dlookup together

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

Select allOpen in new window

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

Select allOpen 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")
)

Select allOpen in new window

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

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.

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.

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

Select allOpen in new window

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

Select allOpen in new window

Thank you

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?

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.

Not able to post online.  How else can I get to you?

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.

Tried it and it does not work.  Same results... blank

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

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

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