?
Solved

Remove all text from an MS access field

Posted on 2011-09-15
14
Medium Priority
?
287 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 2000 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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
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

DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Suggested Courses

764 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