Ok, here's my dilema;
I have a Access 2002 Data Project connected to a SQL2000 Database
I have a form displaying client info. One of the fields is a Phone field. The Field itself is a bigInt data type. I have the Input Mask set to \(000") "000\-0000 and the format to (000) 000-0000.
The Phone number displayed when the textbox doesn't have the focus is 9995551212 instead of (999) 555-1212. It looks like Access doesn't recognize the bigInt field as a numeric value and therefore doen't apply the format. I've created a workaround, but it seems messy:
1. I changed the Record Source to be =CDbl(nz([Phone],0))
2. Created an GotFocus Event Procedures which changes the RecordSource to [Phone] (to allow for edits of the field)
3. Created a LostFocus Event Procedure to change it back to =CDbl(Nz([Phone],0))
The Phone numbers now display properly, but it slows down the form.
Is there a better way of handling this?
I think that your problem is the data type that you are using.. you will never do any math on a telephone number, so there is no reason for it to be an integer. Try changing the data type to a text type, and I think that your formatting problem will be gone.
a bigint field is much smaller then a char(10) field, searching on that field is much faster as well. Indexing the field also takes up less space. Other problems that I can foresee with changing it to a text type is searches would have to be formatted exactly as what was typed, ie. searching for "999-555-1212" would not return records with "(999)555-1212".
I guess my main concern (or puzzlement) here is why is access not recognizing bigint data types as numeric values.