Ignore text in query expression.

[GIDATS4978]

I tried searching the forums for this but can't seem to find it.

I have a table that has data as a text field. The data will be Numeric, Null or "N/O" as being "Non-Observed".

My query expression works if the data type is numeric (without the "N/O"'s). However, I can't seem to figure out how to get the expression to work if it is a text field (to include "N/O" as an option).

Here's my expression from a numeric field which works great:

(Nz([FieldA],0)+Nz([FieldB],0))/((IIf(Nz([FieldA],0)>0,1,0))+(IIf(Nz([FieldB],0)>0,1,0)))

As soon as I convert the data type to text the expression returns an error.

Is there a way I can convert all of the "N/O"'s to 0's and then the expression might work?

Or will expressions not work if it is a text data type?

Should I replace all the "N/O"'s to 99 or another random number and then exclude them in my expression (numeric data type) to get the correct results?

Thanks for the help.

 

[CJ_London]

text fields cannot be used in calculations (i.e. add/subtract etc) you would need to use the val function to convert to a number first

so if this is your requirement remove all the 'N/O' values (suggest treat as nulls) and convert your column to a numeric datatype

 

[CJ_London]

as a further note, you can use the format property as below so the field display 'N/0' if null

;;;"N/O"

to include zeros as well

;;"N/O";"N/O"

Note that properties only need to be applied at the final destination

 

[GIDATS4978]

Thanks. That would work, however I'm trying to track grades. The teacher can put a number, "N/O" as "Non-Observed" or some have left Nulls (meaning they just didn't put in the grades yet). I would like to get the average of the grades but leave out the "N/O"'s and the Nulls. That's why I was thinking of just assigning a random strange number to the "N/O"'s so that I can keep the Nulls (and track where the teacher didn't input any grade data at all.

Is this the best option?

 

[CJ_London]

in that case, assign 0 for the N/O's - then you have clarity of the data -

and format as

;;"'N/O"