UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   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



Posts: 1,279
Joined: 15-April 04



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: 63,403
Joined: 19-June 07
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



Posts: 1,279
Joined: 15-April 04



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: 63,403
Joined: 19-June 07
From: SoCal, USA


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


Custom Search
RSSSearch   Top   Lo-Fi    29th August 2015 - 03:54 PM