code to add a space

  • Thread starter Thread starter Robin Chapple
  • Start date Start date
R

Robin Chapple

I have inherited a database with telephone numbers entered as:

NN NNNNNNNN

where the common usage is:

NN NNNN NNNN

How do I add that space with code?

Thanks,

Robin Chapple
 
Use an Update query and update to Left([TelephoneNumber],7) & " " &
Right([TelephoneNumber],4)
 
Robin

Do you need to modify the data, or would it suffice to format the existing
data with the additional space?

In a query, you can use the Left(), Mid() and/or Right() functions to break
apart the original and add in a space (one way would be like:)

Left([YourPhoneField],7) & " " & Right([YourPhoneField],4)

In fact, if you decide to update the underlying data, you could still use
this as part of an update query -- but back up your database before
attempting!
 
I have inherited a database with telephone numbers entered as:

NN NNNNNNNN

where the common usage is:

NN NNNN NNNN

How do I add that space with code?

Thanks,

Robin Chapple
Click to expand...

Permanently?
Run an Update query.

Update YourTable Set YourTable.Phone = Left([Phone],7) & " " &
Right([Phone],4);
 
Robin,
Do an Update query with this in your TelephoneNumber field UpdateTo...

Left([TelephoneNumber],6) & " " & Mid([TelephoneNumber],7)

hth
Al Camp
 
Robin.
Typo... sorry... should be
Left([TelephoneNumber],7) & " " & Mid([TelephoneNumber],8)
Al Camp

AlCamp said:
Robin,
Do an Update query with this in your TelephoneNumber field UpdateTo...

Left([TelephoneNumber],6) & " " & Mid([TelephoneNumber],7)

hth
Al Camp

Robin Chapple said:
I have inherited a database with telephone numbers entered as:

NN NNNNNNNN

where the common usage is:

NN NNNN NNNN

How do I add that space with code?

Thanks,

Robin Chapple
Click to expand...
Click to expand...
 
Jeff,

I have now been successful with modifying the data in a test table.

I then tried to follow your advice and format the existing data in a
query. Without success.

The plan was to use a different field name from the inherited data
like this:

TelBH:Format(Left([Bus Phone],7) & " " & Right([Bus Phone],4)Bus
Phone)

where the original data is in a field [Bus Phone] and my new field
will be [TelBH]

Yes, the inherited data includes spaces in field names.

Thanks,

Robin
 
Jeff,

I found spare baggage at the end of the expression, removed it and all
is well.

TelBH:Format(Left([Bus Phone],7) & " " & Right([Bus Phone],4)

Robin

===================Original Message==============
I have now been successful with modifying the data in a test table.

I then tried to follow your advice and format the existing data in a
query. Without success.

The plan was to use a different field name from the inherited data
like this:

TelBH:Format(Left([Bus Phone],7) & " " & Right([Bus Phone],4)Bus
Phone)

where the original data is in a field [Bus Phone] and my new field
will be [TelBH]

Yes, the inherited data includes spaces in field names.

Thanks,

Robin
 
Back
Top