Solved

Remove all text from an MS access field

Posted on 2011-09-15
14
281 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
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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

863 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

23 Experts available now in Live!

Get 1:1 Help Now