Re-naming during concatenation

[Guest]

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

 

[Roger Carlson]

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"

 

[Guest]

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

 

[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

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"