Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Remove all text from an MS access field

Posted on 2011-09-15
14
Medium Priority
?
293 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

604 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