Length of field in Query (Access 97 SR2)

Topic

New Reply

In a query, I’m wanting to test the length of a field containing either a 7 or 11 digit phone number and format them based on the length of the field. Here’s the syntax I’m using:

Number Dialed: IIF(Len(Trim([Out_NumberDialed]))=7, Format([Out_NumberDialed],”###-####”), Format([Out_NumberDialed],”(###) ###-####”))

which isn’t working… Any suggestions will be much appreciated.

BTW, the data is being pushed from a SQL database into Access, so I can’t change any field properties in the table.

Many thanks!
Kathy

Reply | Quote

Replies

In what way isn’t it working? And is [Out_NumberDialed] a text field or a numeric field?

Reply | Quote

When the query runs, that field is empty; I do not receive an error message. It is a text field. Also, the query pulls info from two related tables. I tried a few ways of putting the table name in along with the field name, but no luck.

Thanks!
Kathy

Reply | Quote

>>It is a text field<<

This is part (if not all) of your problem. the # symbol only applies to numeric fields. Use the @ symbol instead, as in "@@@-@@@@".

Reply | Quote

That did it!

Thanks so much for your help,
Kathy

Reply | Quote