Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2006
    Posts
    12

    Unanswered: Nz Query Function

    After spending all day trying to combine to summary queries into a totals query and then having blank entries pull up a "0" when running the query, I'm now trying to get a totals column.

    Here's the deal, I'm trying to get a difference of two different summaries into a totals column. For example, Total: SumofShipped+SumofReturns (SumofReturns are negative values). Now, both columns have the Nz function in order to put a "0" in blank spaces, because some items may have been shipped but not returned, and vice versa. The problem I'm having with the Total: SumofShipped+SumofReturns expression is that is shows the value of the SumofShipped and SumofReturns occupying the same block. For example, if SumofShipped = 12 and SumofReturns = 0, then the expersion displays "120".

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Looks like your data got converted to text somewhere along the line.

    Try using:
    CInt(SumofShipped)+CInt(SumofReturns)

    or CSng or CLng or whatever matches your number type.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jun 2006
    Posts
    12
    Hey, back at work now and tried the conversion CInt and it worked! Any idea as to how it was reading it as text opposed to number? Checked the format in the table from which the quantities are stored, and it's a Long Integer field size, General Number Format, and Number Data Type. That's why I was confused.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    My guess is it's happening somewhere in your query. Post your SQL here,
    maybe someone will see the cause.

    I also noticed that you said your base field is Long Integer. Use CLNG instead of CINT, just to avoid one of those pesky overflow errors.
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •