hello everyone,

i have a formatting question i was hoping someone could help me with. i have a couple fields in my table which are of numeric type with data validation ranges assigned to them. the problem is this data is taken during 2 different shifts: night and day. sometimes only the night shift takes data and as such the day shift places and 'X' in the text box. my question is how to i keep numeric type data validation on these fields but allow the user to input a text type ('X') value into the box. is there a simpler solution to my problem. i'm hesitant to add any automation to assign specific values to fields based on a selection of day or night shift because sometimes there might be an 'X' on the day shift for a specific field and a couple days later there might be an 'X' for that same field on the night.

thanks all

To allow the entry of any number between 10 and 25, or an "X", set the field's Validation Rule property to:

(= "X") Or (>= 10 And <= 25)

Thanks for the response alan. you post makes complete sense and gave myself a big DUH for not thinking of that, but when i tried to implement it and got the "too complicated expression...." error dialog box. i usually see this when trying to pass a numeric value in an expression requiring a text type data or vice versa. is it possible to place a text validation rule within a numeric data type? could you explain a little more?

thanks

If the table field's ValidationRule property won't work, you can always go to the form where the users will be adding/editing records, and use the BeforeUpdate event procedure of the text box bound to the field. In that procedure, you can use the IsNumeric function to determine whether text or a number has been entered. If it's text and not "X", or if it's numeric and out of range, display an appropriate MsgBox and set the Cancel argument to True.

I hate to state the obvious but you can't store text values in a numeric field. Either the field is numeric or it is text. Is there something wrong with using a 0 to indicate that no data is entered. You can remove the field's default (which is probably 0) and thus their will be no default supplied. You can then make the field required. This will require the entry of 0 or some number and then your validation rule would look like:

(= 0) Or (>= 10 And <= 25)

thanks for the response pat. i thought about using '0' as the default value, but unfortunately i'm afraid it would hurt more than help. the values for this field (text box) are measured, so i don't want to give the illusion that the user took a reading from the monitor and the value was '0'. originally i thought the only way around this was to provide a default value linked to the value of another field (Shift). for example, normally the first shift doesn't record values for 5 fields (controls) on my form; the second shift does this. so, i was thinking to place a default value in those controls when the user selects "first shift" from a combo box. the kicker is that sometimes the second shift might forget to take this value and the first shift will have to do this. its a shame there isn't any syntax for automating people.

Thanks for pointing out what I overlooked, Pat: this was a numeric field we were talking about, and so it cannot store an "X". However, the last approach I suggested will work if you change the table field to Text. If you do so, you'll need to always verify that the contents are numeric before attempting any computations using them.

Rather than make what should be a numeric field, text, use the default that is intended to mean "nothing was entered". Let the fields default to null. SQL aggregate functions will properly ignore any fields with null values.

If you have three records with the following values 2, null, 4, the average using the Avg() function will be 3 NOT 2. Is that what you want?

i decided to go with default values (Null) and added a little label to identify to the user why those fields in question were blank for a particular shift. for these fields, i'm not interested in performing any calculations on them, but its good to know that aggregate SQL functions omit Null values. i didn't know that. thanks for the help alan and pat.