# Thread: Combining Text and Numeric fields (A2K - SR1)

1. ## 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??

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

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

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

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

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

5. ## 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>

#### Posting Permissions

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