Alpha and Numeric in one field

[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.

 

[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)

 

[Lori]

Works Perfectly, THANKS!
--
Lori A. Pong

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

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.