phone # in query

  • Thread starter Thread starter scott
  • Start date Start date
S

scott

For some reason access deletes leading zeros...I have a download that
pulls a phone # in 3 fields, area code, prefix, suffix.

I want to create a query that puts them together in one field..I can
get it to work by using & to put them together but my problem is that
if the suffix has leading zeros it does not show them so my phone
number of

413 565 0034 gets put together as 413-565-34

How can I get this to put the 2 zeros in?

Thanks
Scott
 
What is the data type of your fields? It should be text,
not number. If the data type is text and you are still
getting this problem, then look at how the data is getting
entered into the table. Probably somewhere along the way
the suffix is being treated as a numeric value and the
leading zeros are getting cut off...
 
Dear Scott:

Likely, the values have been saved as a numeric value. Typically, when you
see a numeric value formatted, you would not expect to see leading zeros.

You could have these values as a text datatype with an input mask that
specifies all digits.

But, given the situation as it exists, you can remedy the situation by
changing this into a string, concatenating zeros on the front of it, and
then taking only the last 4 characters:

RIGHT("000" & CStr(YourField), 4)

Change "YourField" to the actual name of your field.
 
Afraid I don't understand your question. You might want to repost it with an
appropriate topic: asking multiple questions in the same thread often leads
to people overlooking subsequent questions.
 
Back
Top