INSERT A DASH IN A TABLE LISTING PART#'S

[KEVTERS JOY]

I have a table of part#'s such as CT11111111 and I need to change the part#'s
to read CT1111-1111, the parts need to all be formated the same so it is CT
then 4 #'s, a dash, and 4 #'s. How do I get Access to do this? Do I run an
update query, key an expression? I have tried to search this out on my own
but can't find anything on it.

 

[KARL DEWEY]

BACKUP DATABASE -------- BACKUP DATABASE
Assuming that the part numbers all are loaded with the same number of digits
and you want to place the dash before the last four digits, use an update
query.
The Update To of the query design view grid below your field would look like
this --
Left([YourFieldName], Len([YourFieldName])-4) & "-" &
Right([YourFieldName], 4)

 

[MGFoster]

I have a table of part#'s such as CT11111111 and I need to change the part#'s
to read CT1111-1111, the parts need to all be formated the same so it is CT
then 4 #'s, a dash, and 4 #'s. How do I get Access to do this? Do I run an
update query, key an expression? I have tried to search this out on my own
but can't find anything on it.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

**** MAKE A BACK UP BEFORE DOING THIS (just in case) ****

Run an UPDATE query like this (put this in a query's SQL View):

UPDATE table_name
SET part_nbr = Left$(part_nbr,6) & "-" & Right$(part_nbr,4)
WHERE part_nbr IS NOT NULL

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSc0Y2YechKqOuFEgEQJ4HgCfbchX8yOezoYA0xoQi0DEaixZHR4Anjwq
7ApzILqxWo5y9rbJL5CXRl9A
=HOnd
-----END PGP SIGNATURE-----

 

[KEVTERS JOY]

Thank you so much! I have been racking my brain over this. I just ran it and
it works. Thanks again!

BACKUP DATABASE -------- BACKUP DATABASE
Assuming that the part numbers all are loaded with the same number of digits
and you want to place the dash before the last four digits, use an update
query.
The Update To of the query design view grid below your field would look like
this --
Left([YourFieldName], Len([YourFieldName])-4) & "-" &
Right([YourFieldName], 4)

I have a table of part#'s such as CT11111111 and I need to change the part#'s
to read CT1111-1111, the parts need to all be formated the same so it is CT
then 4 #'s, a dash, and 4 #'s. How do I get Access to do this? Do I run an
update query, key an expression? I have tried to search this out on my own
but can't find anything on it.

 

[Marshall Barton]

I have a table of part#'s such as CT11111111 and I need to change the part#'s
to read CT1111-1111, the parts need to all be formated the same so it is CT
then 4 #'s, a dash, and 4 #'s. How do I get Access to do this? Do I run an
update query, key an expression? I have tried to search this out on my own
but can't find anything on it.

It's not clear to me why you would need to change the data
in the table. You can just set the format property of the
form or report text box that displays the part# to
@@@@@@-@@@@

 

[KC-Mass]

Let's see. If there are fifty reports and forms and any new ones that
display this data and it is always to be displayed this way, I can make one
change to the data or I can go change all the reports and forms - Hmm?

Regards
Kevin

 

[John W. Vinson]

Let's see. If there are fifty reports and forms and any new ones that
display this data and it is always to be displayed this way, I can make one
change to the data or I can go change all the reports and forms - Hmm?

A third alternative would be to rename the table, and create a query with the
same name as the table making the change.

But if it's not going to interfere with other uses of the table, then yes, I'd
edit the data in the table. Your call!