Results 1 to 5 of 5
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Combining Text and Numeric fields (A2K - SR1)

    Query X calculates field Y as A/B. Y is set as a general number of fixed length of 3. If B = 0, an IIf statement is used to set Y = 0 to avoid getting a divide by zero error. However, the user requests that if B = 0, apply the value N/A to Y. Here is the problem, if an IIf statement is used to set the value of Y to N/A whenever B = 0 (the calculation divides by zero) the fixed formatting is lost and the query returns a long string of numbers. Some of these numbers are exponential type, i.e., 1.543 E-7 instead of 0.000... (Thus, if I try to take a leftI(Y,4) I get 1.58 instead of zero.

    Is there a way to have both the N/A and general fixed length numbers combined in a query such that the formatting of the numbers still works and the correct values are returned? The problem appears to be that I am mixing text and numeric fields at the same time.

    Note: If I set Y equal to Null instead of N/A when dividing by zero, the query works fine.

    Ultimately, the Y value is used as part of a report.

    Any ideas??
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Text and Numeric fields (A2K - SR1)

    You could use the format function
    <pre>IIf([ B ]=0,"N/A",Format([ A ]/[ B ],"#.###"))</pre>

    Francois

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Combining Text and Numeric fields (A2K - SR1)

    Francois,

    Thanks for the input. I input your suggestion as follows:
    CPI TO GO: IIf(([CUM BAC]-[CUM ACWP])=0,"N/A",Format(([CUM BAC]-[CUM BCWP])/([CUM BAC]-[CUM ACWP]),"#.###"))

    However, I do get the N/A, but now the formats on the other numbers are not exactly correct.

    For example:

    Instead of 0.886, I know get .886 and instead of 1.000, I now get 1.

    Is there a way to get the 0.886 and 1.000 as well as the N/A?

    Thanks.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Text and Numeric fields (A2K - SR1)

    Replace the "#.###" with "0.000"
    Francois

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Combining Text and Numeric fields (A2K - SR1)

    Thanks once again. It worked exactly as needed.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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