Alpha and Numeric in one field

L

Lori

Okay, I have one field that contains either an assigned project # or text
indicating Prorate. I need to separate out the prorated items which isn't the
problem.

I've used CO:Left(DAC Name],1) to pull the first character of the field.
This character will be either a 3, 4 or the letter P.

The P items are the prorated ones that I need to work with separately,
however, the 3 and 4 items are still needed.

My problem is that in order for my final output to look the way I need I
have to create an expression that says IIF([CO]= 3 Or 4,100,0) and then work
with those items with the 0 entry to prorate the values. My problem is that
if I look for the numbers, I get #Error for all of the "P" items since there
are not numeric. If I change the expression to read IIF([CO] ="P",0,100) it
reads all items as alpha and gives me the 100 regardless of the value of the
CO Field.

I know the issue is the combination of the alpha and numerica in the same
field but this is not something I can change. The Field is designated as text
in the table (it was imported from an excel spreadsheet and that is added to
every month).

I know there has to be a way around it, I'd appreciate any help I can get.
 
O

Ofer Cohen

Instead of using the CO (which insome cases the query might not recognise it)
write the fomula

IIf(Left([DAC Name],1) In ("3","4"),100,0)
 
L

Lori

Works Perfectly, THANKS!
--
Lori A. Pong


Ofer Cohen said:
Instead of using the CO (which insome cases the query might not recognise it)
write the fomula

IIf(Left([DAC Name],1) In ("3","4"),100,0)

--
Good Luck
BS"D


Lori said:
Okay, I have one field that contains either an assigned project # or text
indicating Prorate. I need to separate out the prorated items which isn't the
problem.

I've used CO:Left(DAC Name],1) to pull the first character of the field.
This character will be either a 3, 4 or the letter P.

The P items are the prorated ones that I need to work with separately,
however, the 3 and 4 items are still needed.

My problem is that in order for my final output to look the way I need I
have to create an expression that says IIF([CO]= 3 Or 4,100,0) and then work
with those items with the 0 entry to prorate the values. My problem is that
if I look for the numbers, I get #Error for all of the "P" items since there
are not numeric. If I change the expression to read IIF([CO] ="P",0,100) it
reads all items as alpha and gives me the 100 regardless of the value of the
CO Field.

I know the issue is the combination of the alpha and numerica in the same
field but this is not something I can change. The Field is designated as text
in the table (it was imported from an excel spreadsheet and that is added to
every month).

I know there has to be a way around it, I'd appreciate any help I can get.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top