Val function returns zero not missing

[MatMac]

Hi

Im using the Val() function to return numeric data from stored text strings.

My problem is that the function returns zero where the data are missing (i.e. a space character at the desired position within the text string).

This is very problematic for me, as the numeric data I am pulling out are in themselves response codes, where zero means something very different from missing.

Is there a way I can use this or another function to pull out numeric values, but return missing/null where appropriate.

Many thanks

Mat.

 

[neileg]

The trouble is that Val() has to return a numeric value and zero is the equivalent to null or space in a text field. Try testing for a non-numeric value in the position with an iff() statement and use only Val() if the value is numeric.

 

[mhartman]

Hello:

Yes. Test your field or text control using the following:

ISNULL for null entries
ISNUMERIC for numbers
'
Regards
Mark

 

[MatMac]

Success!!

Neil - you're an absolute star! Many thanks. You have saved me one huge headache. Very grateful for your help.

Mark - many thanks also for further clarification on test function to use - I was just looking for such a thing when I received your reply.

Kind Regards - Mat

In the spirit of this forum, below is the completed function. This pulls a value from a stored text string called "datastring" in a table called "assess", found at a location specified by "start" and "length".

The IIF function returns either a numeric, if a numeric can be found at this specified location, or simply the contents of the location as text if not.

IIf(IsNumeric(Trim(Mid([assess]![datastring],[start],[length]))), Val(Trim(Mid([assess]![datastring],[start],[length]))), Trim(Mid([assess]![datastring],[start],[length])))

 

[mhartman]

Hello:

Good luck with this!

Regards
Mark

 

[MatMac]

Val etc

Slight error with my previous post.

If I put the text function in the IFFalse argument, then the function returns text for all values, even the Val ones. To make it work I simply need to leave the last argument blank, which puts in a missing. Thus the function will always return a numeric - or a missing.

Cheers

Mat