UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> run query to remove formatted dashes in text field    
 
   
kevinlaw
post Jun 1 2009, 01:23 PM
Post #1

UtterAccess Ruler
Posts: 1,195



Hello UA,
Is there a way to create a query that will remove the slashes in a text field's values. I'm working on a db that has a table with social security numbers in a text field. When data is entered it is formatted with the '-' after the first 3 numbers and then the next 2 (xxx-xx-xxxx).
roblem is I have another table with the ss number and it is just a number field with no slashes or formatting (xxxxxxxxx). I need to connect the records of both tables by the ss#. I can change the 2nd table's ss# field to text but they still won't match up without the formatting. So I was thinking if there's some way to run a query on the first table with the formatting and create a field that removes the slash formatting and just has all the numbers grouped together. Then I could link that query up with the 2nd table. The thing is I need that new query to be updatable because I need to run an update to modifiy records on that 1st table (the one with the formatting). This isn't my db so I'm sort of dealing with someone else's setup, always a little difficult. Any help would be greatly appreciated!
Go to the top of the page
 
+
theDBguy
post Jun 1 2009, 01:26 PM
Post #2

Access Wiki and Forums Moderator
Posts: 57,829
From: SoCal, USA



Hi Kevin,
You can use the Replace() function, such as:
Replace([SSN], "-", "")
(untested)
Hope that helps...
Go to the top of the page
 
+
kevinlaw
post Jun 1 2009, 01:45 PM
Post #3

UtterAccess Ruler
Posts: 1,195



That works perfect! Thanks for the nice simple solution- I always love that. Thanks dbGuy!
Go to the top of the page
 
+
theDBguy
post Jun 1 2009, 03:29 PM
Post #4

Access Wiki and Forums Moderator
Posts: 57,829
From: SoCal, USA



You're welcome. Good luck with your project.
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 1st September 2014 - 06:27 PM