# If Else Qstn??

Discussion in 'Microsoft Access Queries' started by Guest, Nov 7, 2006.

1. ### GuestGuest

Hi,
I have calculated field LineTotal in a query that is based on the result of
the multiplication between 3 fields in the same table ie
[Qty]*[Weight]*[UnitPrice]=[LineTotal]
But the simple problem is the weight is either Null or Zero sometimes thus
making LineTotal end up being zero or null.
Therefore I would really like someone to help me out on getting the right
calculation statement for LineTotal... something in the line of the statement
below if you know what I mean.

If Not IsNull(Me!Weight) > 0 Then
Me.LineTotal = Me.PackQty * Me.Weight * Me.UnitPrice
Else
Me.LineTotal = Me.UnitPrice * Me.PackQty
End If

--
niuginikiwi
Nelson, New Zealand

Guest, Nov 7, 2006

2. ### fredgGuest

On Tue, 7 Nov 2006 13:50:02 -0800, niuginikiwi wrote:

> Hi,
> I have calculated field LineTotal in a query that is based on the result of
> the multiplication between 3 fields in the same table ie
> [Qty]*[Weight]*[UnitPrice]=[LineTotal]
> But the simple problem is the weight is either Null or Zero sometimes thus
> making LineTotal end up being zero or null.
> Therefore I would really like someone to help me out on getting the right
> calculation statement for LineTotal... something in the line of the statement
> below if you know what I mean.
>
> If Not IsNull(Me!Weight) > 0 Then
> Me.LineTotal = Me.PackQty * Me.Weight * Me.UnitPrice
> Else
> Me.LineTotal = Me.UnitPrice * Me.PackQty
> End If
>

In a query?

LineTotal:IIf(IsNull([Weight]) or [Weight] = 0,[UnitPrice] *
[PackQty], [PackQty] * [Weight] * [UnitPrice])
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

fredg, Nov 8, 2006

3. ### GuestGuest

Yes in a Query...
That is what I got for a LineTotal field at the moment...
Line Total:
nz([tblOrderDetails.UnitPrice],0)*nz([PackQty],0)*nz([Weight],1)*nz([Counts],1)
--
niuginikiwi
Nelson, New Zealand

"fredg" wrote:

> On Tue, 7 Nov 2006 13:50:02 -0800, niuginikiwi wrote:
>
> > Hi,
> > I have calculated field LineTotal in a query that is based on the result of
> > the multiplication between 3 fields in the same table ie
> > [Qty]*[Weight]*[UnitPrice]=[LineTotal]
> > But the simple problem is the weight is either Null or Zero sometimes thus
> > making LineTotal end up being zero or null.
> > Therefore I would really like someone to help me out on getting the right
> > calculation statement for LineTotal... something in the line of the statement
> > below if you know what I mean.
> >
> > If Not IsNull(Me!Weight) > 0 Then
> > Me.LineTotal = Me.PackQty * Me.Weight * Me.UnitPrice
> > Else
> > Me.LineTotal = Me.UnitPrice * Me.PackQty
> > End If
> >

>
> In a query?
>
> LineTotal:IIf(IsNull([Weight]) or [Weight] = 0,[UnitPrice] *
> [PackQty], [PackQty] * [Weight] * [UnitPrice])
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
>

Guest, Nov 8, 2006
4. ### John SpencerGuest

Well, you have handled Nulls, but not zeroes. Also, your posting has
invalid bracketing around tblOrderDetails.UnitPrice

Nz([tblOrderDetails].[UnitPrice],0) * IIF(NZ(Weight,0)=0,1,Weight) *
Nz(PackQty,0) * Nz([Counts],1)

I'm not sure that will fix the problem in your calculation, since if you
will still get zeroes if any of the other fields in the calculation are
zero, but that can be taken care of by using the same logic as is used for
Weight. It depends on what you want the calculation to return.

"niuginikiwi" <> wrote in message
news:...
> Yes in a Query...
> That is what I got for a LineTotal field at the moment...
> Line Total:
> nz([tblOrderDetails.UnitPrice],0)*nz([PackQty],0)*nz([Weight],1)*nz([Counts],1)
> --
> niuginikiwi
> Nelson, New Zealand
>
>
> "fredg" wrote:
>
>> On Tue, 7 Nov 2006 13:50:02 -0800, niuginikiwi wrote:
>>
>> > Hi,
>> > I have calculated field LineTotal in a query that is based on the
>> > result of
>> > the multiplication between 3 fields in the same table ie
>> > [Qty]*[Weight]*[UnitPrice]=[LineTotal]
>> > But the simple problem is the weight is either Null or Zero sometimes
>> > thus
>> > making LineTotal end up being zero or null.
>> > Therefore I would really like someone to help me out on getting the
>> > right
>> > calculation statement for LineTotal... something in the line of the
>> > statement
>> > below if you know what I mean.
>> >
>> > If Not IsNull(Me!Weight) > 0 Then
>> > Me.LineTotal = Me.PackQty * Me.Weight * Me.UnitPrice
>> > Else
>> > Me.LineTotal = Me.UnitPrice * Me.PackQty
>> > End If
>> >

>>
>> In a query?
>>
>> LineTotal:IIf(IsNull([Weight]) or [Weight] = 0,[UnitPrice] *
>> [PackQty], [PackQty] * [Weight] * [UnitPrice])
>> --
>> Fred
>> Please respond only to this newsgroup.
>> I do not reply to personal e-mail
>>

John Spencer, Nov 8, 2006