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
Solved

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

Posted on 2008-06-20
6
325 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
  • 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

791 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