Re-naming during concatenation

  • Thread starter Jose Ruben Gonzalez-Baird
  • Start date
J

Jose Ruben Gonzalez-Baird

Hello,

I have a table with two fields; 'Reach' and 'Station'. For my particular
process, 'Reach' varies from 1-4 and 'Station' from 0-approximately 2200. I
am concatenating the values to form a unique 'Reach_Station' identifier, but
I want to be able to have the Reach_Station values sort properly when needed.
Therefore, I need to rename the values to be 01-14 and 0000-000n. As it is
now the concatenation of Reach = 1 and Station = 300 would read "1-300". I
want it to read "01-0300". Any suggestions? Thank you, Ruben
 
J

Jose Ruben Gonzalez-Baird

Thanks! That worked great. I noticed that it's important to include [ ]
around field names.

My colleague decided he wanted the data to appear as "re01-sta0020", for
example. I used your code there plus a little trial and error to figure that
one out.

Now I'm doing a little damage control. I decided that query work so well,
why not update the Reach and Station fields too. I did that but I
accidentally left in "re" when running the query for Reach. Now a typical
entry looks like this: "re01". This isn't so bad, but is it possible to
remove the "re" part?

Your help up to this point has been great. Thanks so much. Ruben
 
J

John Spencer

Use an update query to fix the data

UPDATE YourTable
SET [Reach] = Mid([Reach],3)
WHERE [Reach] Like "RE*"


"Jose Ruben Gonzalez-Baird"
Thanks! That worked great. I noticed that it's important to include [ ]
around field names.

My colleague decided he wanted the data to appear as "re01-sta0020", for
example. I used your code there plus a little trial and error to figure
that
one out.

Now I'm doing a little damage control. I decided that query work so well,
why not update the Reach and Station fields too. I did that but I
accidentally left in "re" when running the query for Reach. Now a typical
entry looks like this: "re01". This isn't so bad, but is it possible to
remove the "re" part?

Your help up to this point has been great. Thanks so much. Ruben

Roger Carlson said:
In the query builder, do this:

Reach_Station: Format(Reach, "00") & "-" & Format(Station,"0000")

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


"Jose Ruben Gonzalez-Baird"
Click to expand...
Click to expand...
 
Top