If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Removing Formatting Symbols from Phone numbers
Dumb question, but I've got to ask.
Did you replace YourTableName and YourFieldName with the names of your table and Fax field? Does Access add quote marks around the table and field names or around the entire expression? There should be NO quote marks. If there are, the function will not get the value from the field, but will process the string contained between the quote marks. Update To: fStripToNumbersOnly("YourTable.YourField") is bad as there are no numbers in the phrase "YourTable.YourField" IF you can't solve this please switch to SQL view and post the SQL text of the query that is failing. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "faxylady" wrote in message ... John, sorry to bother you with this again, but I've run into a problem. Twice now, in applying the code to two other databases, when the query has been run, it wiped out all my fax nos. I used this to reformat the fax field. What it did was wipe out the entire fax number except for the 1 at the beginning. The 1 was inserted by means of an update query to facilitate the fax program. When I ran the query you suggested-- Update To: fStripToNumbersOnly ([YourTableName].[YourFieldName]) ------ Criteria: Not Like "##########" all the fax nos were wiped out and only the 1 at the beginning was left in each of the records. Please tell me what might have happened, what I did wrong and what may be done to correct it. Thanks. "John Spencer" wrote: You can use a VBA function to do this. Paste the function below into a module and save it the module (Don't name the module the same as the function) If you want to do this permanently then use the function in an Update query. If you just want to do this to see the value then you can use the function in the select clause SELECT fStripToNumbersOnly([Telephone]) as PhoneNum FROM YourTable IF you have a lot of records this could be slow. The update query would look like UPDATE YourTable SET [Telephone] = fStripToNumbersOnly([YourTable].[Telephone]) WHERE [Telephone] is Not Null Public Function fStripToNumbersOnly(ByVal varText As Variant) As Variant 'Takes input and returns only the numbers in the input. Strips out 'all other characters. Handles nulls, dates, numbers, and strings. Const strNumbers As String = "0123456789" Dim strOut As Variant Dim intCount As Integer If Len(varText & "") = 0 Then strOut = varText Else varText = varText & "" For intCount = 1 To Len(varText) If InStr(1, strNumbers, Mid(varText, intCount, 1)) 0 Then strOut = strOut & Mid(varText, intCount, 1) End If Next intCount End If fStripToNumbersOnly = strOut End Function -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "faxylady" wrote in message ... A project that I am doing requires collecting large amounts of contact data and working with the phone numbers. My job would be a lot easier if the formatting on the phone numbers were deleted. For example, the left and right parentheses, Slashes, dashes and spaces cause some problems and I would like to know how to remove them. I am not knowledgable in writing code but do know a few things about queries. Everything I try results in a syntax error message. What can you suggest? |
#22
|
|||
|
|||
Removing Formatting Symbols from Phone numbers
Yes, there are quote marks. What gets me is that with some tables, I insert
the table name and fax in the appropriate places and they work fine, others don't. Here is the SQL view. UPDATE LG9LongDistanceTABLE SET LG9LongDistanceTABLE.Fax = fStripToNumbersOnly("[LG9LongDistanceTABLE].[Fax]") WHERE ((([LG9LongDistanceTABLE].[Fax]) Not Like "##########")); Thanks. "John Spencer" wrote: Dumb question, but I've got to ask. Did you replace YourTableName and YourFieldName with the names of your table and Fax field? Does Access add quote marks around the table and field names or around the entire expression? There should be NO quote marks. If there are, the function will not get the value from the field, but will process the string contained between the quote marks. Update To: fStripToNumbersOnly("YourTable.YourField") is bad as there are no numbers in the phrase "YourTable.YourField" IF you can't solve this please switch to SQL view and post the SQL text of the query that is failing. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "faxylady" wrote in message ... John, sorry to bother you with this again, but I've run into a problem. Twice now, in applying the code to two other databases, when the query has been run, it wiped out all my fax nos. I used this to reformat the fax field. What it did was wipe out the entire fax number except for the 1 at the beginning. The 1 was inserted by means of an update query to facilitate the fax program. When I ran the query you suggested-- Update To: fStripToNumbersOnly ([YourTableName].[YourFieldName]) ------ Criteria: Not Like "##########" all the fax nos were wiped out and only the 1 at the beginning was left in each of the records. Please tell me what might have happened, what I did wrong and what may be done to correct it. Thanks. "John Spencer" wrote: You can use a VBA function to do this. Paste the function below into a module and save it the module (Don't name the module the same as the function) If you want to do this permanently then use the function in an Update query. If you just want to do this to see the value then you can use the function in the select clause SELECT fStripToNumbersOnly([Telephone]) as PhoneNum FROM YourTable IF you have a lot of records this could be slow. The update query would look like UPDATE YourTable SET [Telephone] = fStripToNumbersOnly([YourTable].[Telephone]) WHERE [Telephone] is Not Null Public Function fStripToNumbersOnly(ByVal varText As Variant) As Variant 'Takes input and returns only the numbers in the input. Strips out 'all other characters. Handles nulls, dates, numbers, and strings. Const strNumbers As String = "0123456789" Dim strOut As Variant Dim intCount As Integer If Len(varText & "") = 0 Then strOut = varText Else varText = varText & "" For intCount = 1 To Len(varText) If InStr(1, strNumbers, Mid(varText, intCount, 1)) 0 Then strOut = strOut & Mid(varText, intCount, 1) End If Next intCount End If fStripToNumbersOnly = strOut End Function -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "faxylady" wrote in message ... A project that I am doing requires collecting large amounts of contact data and working with the phone numbers. My job would be a lot easier if the formatting on the phone numbers were deleted. For example, the left and right parentheses, Slashes, dashes and spaces cause some problems and I would like to know how to remove them. I am not knowledgable in writing code but do know a few things about queries. Everything I try results in a syntax error message. What can you suggest? |
#23
|
|||
|
|||
Removing Formatting Symbols from Phone numbers
THERE SHOULD NOT BE QUOTE MARKS. The query should look like the following
UPDATE LG9LongDistanceTABLE SET LG9LongDistanceTABLE.Fax = fStripToNumbersOnly([LG9LongDistanceTABLE].[Fax]) WHERE ((([LG9LongDistanceTABLE].[Fax]) Not Like "##########")); Sometimes the Design View (query grid) will insert quotes in the update to when you do not want them. This seems to be what happened here. You should be able to remove the quotes in the query grid and this should run correctly. The usual warning applies, back up your data first and then run the query. Once you are sure it is working, then you can skip backing up just before running the query. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "faxylady" wrote in message ... Yes, there are quote marks. What gets me is that with some tables, I insert the table name and fax in the appropriate places and they work fine, others don't. Here is the SQL view. UPDATE LG9LongDistanceTABLE SET LG9LongDistanceTABLE.Fax = fStripToNumbersOnly("[LG9LongDistanceTABLE].[Fax]") WHERE ((([LG9LongDistanceTABLE].[Fax]) Not Like "##########")); Thanks. "John Spencer" wrote: Dumb question, but I've got to ask. Did you replace YourTableName and YourFieldName with the names of your table and Fax field? Does Access add quote marks around the table and field names or around the entire expression? There should be NO quote marks. If there are, the function will not get the value from the field, but will process the string contained between the quote marks. Update To: fStripToNumbersOnly("YourTable.YourField") is bad as there are no numbers in the phrase "YourTable.YourField" IF you can't solve this please switch to SQL view and post the SQL text of the query that is failing. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "faxylady" wrote in message ... John, sorry to bother you with this again, but I've run into a problem. Twice now, in applying the code to two other databases, when the query has been run, it wiped out all my fax nos. I used this to reformat the fax field. What it did was wipe out the entire fax number except for the 1 at the beginning. The 1 was inserted by means of an update query to facilitate the fax program. When I ran the query you suggested-- Update To: fStripToNumbersOnly ([YourTableName].[YourFieldName]) ------ Criteria: Not Like "##########" all the fax nos were wiped out and only the 1 at the beginning was left in each of the records. Please tell me what might have happened, what I did wrong and what may be done to correct it. Thanks. "John Spencer" wrote: You can use a VBA function to do this. Paste the function below into a module and save it the module (Don't name the module the same as the function) If you want to do this permanently then use the function in an Update query. If you just want to do this to see the value then you can use the function in the select clause SELECT fStripToNumbersOnly([Telephone]) as PhoneNum FROM YourTable IF you have a lot of records this could be slow. The update query would look like UPDATE YourTable SET [Telephone] = fStripToNumbersOnly([YourTable].[Telephone]) WHERE [Telephone] is Not Null Public Function fStripToNumbersOnly(ByVal varText As Variant) As Variant 'Takes input and returns only the numbers in the input. Strips out 'all other characters. Handles nulls, dates, numbers, and strings. Const strNumbers As String = "0123456789" Dim strOut As Variant Dim intCount As Integer If Len(varText & "") = 0 Then strOut = varText Else varText = varText & "" For intCount = 1 To Len(varText) If InStr(1, strNumbers, Mid(varText, intCount, 1)) 0 Then strOut = strOut & Mid(varText, intCount, 1) End If Next intCount End If fStripToNumbersOnly = strOut End Function -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "faxylady" wrote in message ... A project that I am doing requires collecting large amounts of contact data and working with the phone numbers. My job would be a lot easier if the formatting on the phone numbers were deleted. For example, the left and right parentheses, Slashes, dashes and spaces cause some problems and I would like to know how to remove them. I am not knowledgable in writing code but do know a few things about queries. Everything I try results in a syntax error message. What can you suggest? |
#24
|
|||
|
|||
Removing Formatting Symbols from Phone numbers
I think this just about gets it. Thanks for all your help. It is most
appreciated. "John Spencer" wrote: THERE SHOULD NOT BE QUOTE MARKS. The query should look like the following UPDATE LG9LongDistanceTABLE SET LG9LongDistanceTABLE.Fax = fStripToNumbersOnly([LG9LongDistanceTABLE].[Fax]) WHERE ((([LG9LongDistanceTABLE].[Fax]) Not Like "##########")); Sometimes the Design View (query grid) will insert quotes in the update to when you do not want them. This seems to be what happened here. You should be able to remove the quotes in the query grid and this should run correctly. The usual warning applies, back up your data first and then run the query. Once you are sure it is working, then you can skip backing up just before running the query. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "faxylady" wrote in message ... Yes, there are quote marks. What gets me is that with some tables, I insert the table name and fax in the appropriate places and they work fine, others don't. Here is the SQL view. UPDATE LG9LongDistanceTABLE SET LG9LongDistanceTABLE.Fax = fStripToNumbersOnly("[LG9LongDistanceTABLE].[Fax]") WHERE ((([LG9LongDistanceTABLE].[Fax]) Not Like "##########")); Thanks. "John Spencer" wrote: Dumb question, but I've got to ask. Did you replace YourTableName and YourFieldName with the names of your table and Fax field? Does Access add quote marks around the table and field names or around the entire expression? There should be NO quote marks. If there are, the function will not get the value from the field, but will process the string contained between the quote marks. Update To: fStripToNumbersOnly("YourTable.YourField") is bad as there are no numbers in the phrase "YourTable.YourField" IF you can't solve this please switch to SQL view and post the SQL text of the query that is failing. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "faxylady" wrote in message ... John, sorry to bother you with this again, but I've run into a problem. Twice now, in applying the code to two other databases, when the query has been run, it wiped out all my fax nos. I used this to reformat the fax field. What it did was wipe out the entire fax number except for the 1 at the beginning. The 1 was inserted by means of an update query to facilitate the fax program. When I ran the query you suggested-- Update To: fStripToNumbersOnly ([YourTableName].[YourFieldName]) ------ Criteria: Not Like "##########" all the fax nos were wiped out and only the 1 at the beginning was left in each of the records. Please tell me what might have happened, what I did wrong and what may be done to correct it. Thanks. "John Spencer" wrote: You can use a VBA function to do this. Paste the function below into a module and save it the module (Don't name the module the same as the function) If you want to do this permanently then use the function in an Update query. If you just want to do this to see the value then you can use the function in the select clause SELECT fStripToNumbersOnly([Telephone]) as PhoneNum FROM YourTable IF you have a lot of records this could be slow. The update query would look like UPDATE YourTable SET [Telephone] = fStripToNumbersOnly([YourTable].[Telephone]) WHERE [Telephone] is Not Null Public Function fStripToNumbersOnly(ByVal varText As Variant) As Variant 'Takes input and returns only the numbers in the input. Strips out 'all other characters. Handles nulls, dates, numbers, and strings. Const strNumbers As String = "0123456789" Dim strOut As Variant Dim intCount As Integer If Len(varText & "") = 0 Then strOut = varText Else varText = varText & "" For intCount = 1 To Len(varText) If InStr(1, strNumbers, Mid(varText, intCount, 1)) 0 Then strOut = strOut & Mid(varText, intCount, 1) End If Next intCount End If fStripToNumbersOnly = strOut End Function -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "faxylady" wrote in message ... A project that I am doing requires collecting large amounts of contact data and working with the phone numbers. My job would be a lot easier if the formatting on the phone numbers were deleted. For example, the left and right parentheses, Slashes, dashes and spaces cause some problems and I would like to know how to remove them. I am not knowledgable in writing code but do know a few things about queries. Everything I try results in a syntax error message. What can you suggest? |
Thread Tools | |
Display Modes | |
|
|