# If Else Qstn??

=?Utf-8?B?bml1Z2luaWtpd2k=?=
Guest
Posts: n/a

 7th Nov 2006
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

fredg
Guest
Posts: n/a

 8th Nov 2006
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

=?Utf-8?B?bml1Z2luaWtpd2k=?=
Guest
Posts: n/a

 8th Nov 2006
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
Guest
Posts: n/a

 8th Nov 2006
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post deb Microsoft Access 1 18th Aug 2008 08:46 PM =?Utf-8?B?V2FoaW5l?= Microsoft Excel Programming 4 19th Jul 2006 03:45 PM Alexander Schiebel Windows XP Help 2 9th Dec 2003 07:51 AM David Candy Windows XP Help 29 4th Oct 2003 04:10 AM muckshifter General Discussion 5 21st Oct 2002 01:53 AM

Features