Solved

Remove all text from an MS access field

Posted on 2011-09-15
14
283 Views
Last Modified: 2012-05-12
Hi,

I have a field in MS Access 2007 that I want to change to only digits.  I want to remove all text from the string, text can appear anywhere in the string, or not at all.

Once I have just the numbers, I want to remove any leading zeros.  I think that's simple enough with trim, I'm happy to figure that out on my own.  I really just need help removing the text.

Thank you
0
Comment
Question by:TelMaco
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 36546641
How about this:

Public Function aaaaQuickTest(sText As String) As String

    If IsNumeric(sText) Then
        aaaaQuickTest = Val(sText) 'already all numeric
        Exit Function
    End If
   
    Dim i
    Dim s As String
   
    For i = 1 To Len(sText)
       If IsNumeric(Mid(sText, i, 1)) Then s = s & Mid(sText, i, 1)
    Next i
    aaaaQuickTest = Val(s)
   
End Function
0
 
LVL 75
ID: 36546649
Better change the last line from

aaaaQuickTest = Val(s)

to

aaaaQuickTest = IIf(Len(s) > 0, Val(s), "")

in case you pass a zero length string.

mx
0
 

Author Comment

by:TelMaco
ID: 36550497
Hi MX,

I'm not sure how to use macros in Access, can I include this as part of my SQL?  

Example:

SELECT table.enteredby
FROM table;

It's the table.enteredby feild that I want to fix - to remove any text.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75
ID: 36550557
Technically ... not a Macro ... but a vba code Function. Yes, you can call that from your query.  

This will display w/o text

SELECT aaaaQuickTest([enteredby]) AS ConvertFld
FROM [Table];

If you need to actually change the data in that field, let me know.

mx
0
 

Author Comment

by:TelMaco
ID: 36550576
here's what I was trying in my SQL:
SELECT Replace(table.[enteredby],"[A-Za-z]","") AS Expr1
FROM table

but it's not filtering out the text...
0
 
LVL 75
ID: 36550598
Try what I posted ...

mx
0
 

Author Comment

by:TelMaco
ID: 36551445
lol sry I posted without refreshing the page, I will try your suggestion and let you know
0
 
LVL 75
ID: 36551511
For some reason, that pattern matching gig does not work in the Replace() function.

mx
0
 

Author Comment

by:TelMaco
ID: 36551536
nice that totaly works!

0
 
LVL 75
ID: 36551676
cool ...
0
 

Author Comment

by:TelMaco
ID: 36551927
sry, I know this is closed, but I have a quick followup
I am getting a data type mismatch error when I try to use ConvertFld in the group by part of the sql

I tried removing the ConvertFld and just doing the group by mnth and that works ok, so I think something is funny with the ConvertFld
select 
count(*), mnth, ConvertFld 
from 
(
select 
tracker.id, 
aaaaQuickTest([enteredby]) AS ConvertFld, 
(Format(tracker.[orderstart],"yyyy") & " - " & MonthName(Format(tracker.[orderstart],"m"))) AS mnth
FROM tracker
WHERE (((tracker.[orderstart])>=#1/1/2011#))
) 
group by mnth, ConvertFld

Open in new window

0
 

Author Comment

by:TelMaco
ID: 36552125
I changed aaaaQuickTest([enteredby])
to
aaaaQuickTest(nz(enteredby,0))

works ok now
0
 
LVL 75
ID: 36552143
Sorry ... yes ...

change
Public Function aaaaQuickTest(sText As String) As String

To

Public Function aaaaQuickTest(sText As Variant) As String

should also work ... A Sting cannot be Null, a Variant can.  But what you did is fine ...

mx
0
 
LVL 75
ID: 36552228
If you did change it to Variant, we would still need a text in the code to check for Null ... else the Len() function would blow up here:

For i = 1 To Len(sText)

so ... Nz is a good work around.  Well, except that will return a zero ... when is fact no data is present.  Is that ok ?

mx
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question