Converting to Number

[Hillary]

I have a table linked into my Access Database which has two fields for scores as a varchar type fields. One field I was able to create the formula, Raw Score: Val([RAW_SCORE]) and then put criteria of <65 and it works great. When I try to create the same formula for SCALED_SCORE and put a < criteria with it, I get "Data type mismatch in criteria expression" error. There are some Nulls in the SCALED_SCORE field and I've tried to convert these to 0 but I still get the same error.

Is there a better way to convert varchar to Number? I don't want to report nulls or zeros.

Thanks for your help,

Hillary

 

[mgolla]

Have you tried this:

Scaled Score: IIf(IsNull([SCALED_SCORE],0,Val([SCALED_SCORE]))

Then <65 in criteria?

HTH
Mike

Dooobie...Doobie......Dooo

 

[Golom]

You can filter out the NULL or zero values with
[tt]
WHERE SCALED_SCORE IS NOT NULL OR SCALED_SCORE = "0"
[/tt]
where I'm assuming that SCALED_SCORE is a text field
(BTW SQL Server calls them varchar; in Access they are text.)

The other option is to use the NZ function to handle the NULLS
[tt]
[Scaled Score] : Val(NZ([SCALED_SCORE],"0"))
[/tt]

 

[Hillary]

I tried Scaled Score: IIf([SCALED_SCORE]Is Null,Val(0),Val([SCALED_SCORE])) and that didn't work but your formula does B-)

Thanks!

Hillary