Solved

How do I check that text data is entered in correct format in Access database

Posted on 2008-06-20
6
338 Views
Last Modified: 2010-07-27
I need to go through a table listing all the filenames within a directory to check that those filenames are adhering to the prescribed naming convention. The users are supposed to create the filenames in the format AAAA###-A-####-#, where A stands for letters and # for numbers. I have shown the maximum number of letter and numbers allowed, but even A#-A-#-# is a valid filename. I don't know how to make a query in MS Access  that can account for variable number of characters and numbers but at prescribed locations and return offending filenames.
0
Comment
Question by:PopoDi
[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
  • 4
  • 2
6 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 21835223
Hello PopoDi,

1) Add the UDF in the snippet to your VBA project (in a regular module)

2) Run a query like this to find invalid names:

SELECT FileName
FROM YourTable
WHERE RegExpFind(FileName, "^[A-Z]+\d+-[A-Z]+-\d+-\d+$", 1,False) = ""

Regards,

Patrick
Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
    Optional MatchCase As Boolean = True)
    
    ' For more info see: http://vbaexpress.com/kb/getarticle.php?kb_id=841 
    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
    ' pattern (PatternStr).  Use Pos to indicate which match you want:
    ' Pos omitted               : function returns a zero-based array of all matches
    ' Pos = 0                   : the last match
    ' Pos = 1                   : the first match
    ' Pos = 2                   : the second match
    ' Pos = <positive integer>  : the Nth match
    ' If Pos is greater than the number of matches, is negative, or is non-numeric, the function
    ' returns an empty string.  If no match is found, the function returns an empty string
    
    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
    
    ' If you use this function in Excel, you can use range references for any of the arguments.
    ' If you use this in Excel and return the full array, make sure to set up the formula as an
    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
    
    Dim RegX As Object
    Dim TheMatches As Object
    Dim Answer() As String
    Dim Counter As Long
    
    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
    If Not IsMissing(Pos) Then
        If Not IsNumeric(Pos) Then
            RegExpFind = ""
            Exit Function
        Else
            Pos = CLng(Pos)
        End If
    End If
    
    ' Create instance of RegExp object
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = True
        .IgnoreCase = Not MatchCase
    End With
        
    ' Test to see if there are any matches
    If RegX.test(LookIn) Then
        
        ' Run RegExp to get the matches, which are returned as a zero-based collection
        Set TheMatches = RegX.Execute(LookIn)
        
        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
        ' function's return value
        If IsMissing(Pos) Then
            ReDim Answer(0 To TheMatches.Count - 1) As String
            For Counter = 0 To UBound(Answer)
                Answer(Counter) = TheMatches(Counter)
            Next
            RegExpFind = Answer
        
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
        Else
            Select Case Pos
                Case 0                          ' Last match
                    RegExpFind = TheMatches(TheMatches.Count - 1)
                Case 1 To TheMatches.Count      ' Nth match
                    RegExpFind = TheMatches(Pos - 1)
                Case Else                       ' Invalid item number
                    RegExpFind = ""
            End Select
        End If
    
    ' If there are no matches, return empty string
    Else
        RegExpFind = ""
    End If
    
    ' Release object variables
    Set RegX = Nothing
    Set TheMatches = Nothing
    
End Function

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 21835235
PopoDi,

BTW, I am assuming:

1) The file name four segments, separated by hyphens

2) Segment one is [A-Z] one or more times and then [0-9] one or more times

3) Segment two is [A-Z] one or more times

4) Segments three and four are each [0-9] one or more times

5) There is no file extension in the string in your field

If any of these assumptions are incorrect, please let me know and I can adjust the pattern string accordingly.

Regards,

Patrick
0
 

Author Comment

by:PopoDi
ID: 21835281
Hi Patrick,

Thank you for your replies!
Segment one is [A-Z] one to 4 times then [0-9] one to 3 times, segment two is only one [A-Z], segment three is [0-9] one to 4 times and segment 4 is [0-9] once to 2 times.

I'm trying out your solution now (I'm completely new to this, so give me some time). Thank you!

Dijana
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 21835352
Dijana, based on your reply, this will be a better fit:

SELECT FileName
FROM YourTable
WHERE RegExpFind(FileName, "^[A-Z]{1,4}\d{1,3}-[A-Z]-\d{1,4}-\d{1,2}$", 1,False) = ""
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 21835358
Regular Expressions is just a truly wonderful thing :)
0
 

Author Closing Comment

by:PopoDi
ID: 31469335
Thank you!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

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.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

737 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