Solved

Remove all text from an MS access field

Posted on 2011-09-15
14
280 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
  • 8
  • 6
14 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Try what I posted ...

mx
0
 

Author Comment

by:TelMaco
Comment Utility
lol sry I posted without refreshing the page, I will try your suggestion and let you know
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
For some reason, that pattern matching gig does not work in the Replace() function.

mx
0
 

Author Comment

by:TelMaco
Comment Utility
nice that totaly works!

0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
cool ...
0
 

Author Comment

by:TelMaco
Comment Utility
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
Comment Utility
I changed aaaaQuickTest([enteredby])
to
aaaaQuickTest(nz(enteredby,0))

works ok now
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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 …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now