Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Formula not working 1

Status
Not open for further replies.

istone

IS-IT--Management
Jan 24, 2007
139
US
can you please tell me why this formula is not working:

IIf([NAMECUST]="Carbro" And [Client Type]=2,Null,[Assests],IIf([NAMECUST]="CWR" AND [Client Type]=2, null,[Assests]))

Thanks in advance
 
An IIf() expression should have only two commas:
IIf(true/false expression [red],[/red] True result [red],[/red] False result)
Yours has three:
IIf([NAMECUST]="Carbro" And [Client Type]=2 [red],[/red] Null [red],[/red] [Assests] [red],[/red] IIf([NAMECUST]="CWR" AND [Client Type]=2, null,[Assests]))

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
I don't see any difference between my formula and yours.
thanks
 
I suggest read dhookom reply again, only more carefully. :)
 
Got it.
Sorry guys for missing it the first time.
And thanks again.

 
I guess you wanted this ?
IIf(([NAMECUST]="Carbro" Or [NAMECUST]="CWR") And [Client Type]=2,Null,[Assests])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
PHV. I did:
IIf([NAMECUST]="Carbro" And [Client Type]=2 , Null, IIf([NAMECUST]="CWR" AND [Client Type]=2, null,[Assests]))

But I like yours, it's easier..
thanks
 
Actually, I would probably never create an expression in a query or control source or code like this. It looks like you have some type of business logic in the calculation. IMHO you would be much better off creating a small user-defined function that would be used in place of the nested IIf()s.
Code:
Public Function FindAssets(pstrCust as String, _
    lngClientType as Long, dblAssets as Double) as Variant
    Select Case pstrCust
        Case "Carbo", "CWR"
            If lngClientType = 2 Then
                FindAssets = Null
             Else
                FindAssets = dblAssets
            End If
        Case Else
            FindAssets = dblAssets
    End Select
End Function
Save this function in a standard module with a name like "modBusinessCalcs". Then you can replace the nested IIf()s with
FindAssets([NAMECUST],[Client Type],[Assets])
[red]WHEN[/red] your list of NAMECUST or the Client Type values change, you shouldn't have to search through code or queries to find where you need to make changes. Just modify your code in the function that you should easily find in your business calculations.

The ultimate solution (IMHO) would be to store this model in tables, rather than code.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
My sample/suggested function makes some assumptions about your data types and needs. You may need to change some the of code to handle potential Nulls or whatever.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top