If Else Qstn??

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

  1. Guest

    Guest Guest

    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

    Thanks in advance
    --
    niuginikiwi
    Nelson, New Zealand
     
    Guest, Nov 7, 2006
    #1
    1. Advertisements

  2. Guest

    fredg Guest

    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
    >
    > Thanks in advance


    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
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    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
    > >
    > > Thanks in advance

    >
    > 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
    #3
  4. Guest

    John Spencer Guest

    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
    >> >
    >> > Thanks in advance

    >>
    >> 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
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Dan

    Re: sql syntax - if .. then update else append

    Dan, Jul 14, 2003, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    3,678
    John Vinson
    Jul 14, 2003
  2. Harold

    if exist update else insert

    Harold, Sep 4, 2003, in forum: Microsoft Access Queries
    Replies:
    5
    Views:
    1,001
    Michel Walsh
    Sep 5, 2003
  3. Laura

    If Then Else Statements

    Laura, Nov 4, 2003, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    195
    Yvonneb
    Dec 15, 2003
  4. Carlos

    If Else Statements

    Carlos, Dec 29, 2003, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    153
    Guest
    Dec 30, 2003
  5. Nancy

    If then else statements

    Nancy, Feb 20, 2004, in forum: Microsoft Access Queries
    Replies:
    9
    Views:
    154
    Steve Schapel
    Feb 23, 2004
Loading...

Share This Page