X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Finding A Format Within A String, Access 2016    
post Dec 11 2017, 03:22 PM

Posts: 127
Joined: 11-October 10

I want to filter a query to only records that have a certain format within the string.

For example, my data can look like this

002-rd-02 500
9998B-02-03-01 Detail 4

I only want the query to return data with -##-##-##

That criteria would only return records 2, 3, & 6 and omit 1, 4, & 5

Ultimately, I would like to identify and trim the data to just that part of the string and replace the dashes with dots so in the end my data would be:


Go to the top of the page
post Dec 11 2017, 03:48 PM

UtterAccess Editor
Posts: 18,022
Joined: 29-March 05
From: Wisconsin


This may not be the most elegant solution, but based on your examples, it looks like you could basically count dashes that occur every three characters. If there are three of them in a row, return the record. But here's a User Defined Function you could use that would either return an empty string or the digits and dashes converted to digits and periods:

Public Function ExtractedNumbers(ByVal strInput As String) As String

    Dim i As Integer

    If Len(strInput) - Len(Replace(strInput, "-", "")) > 2 Then
        strInput = Mid(strInput, InStr(1, strInput, "-"))
        For i = 1 To 7 Step 3
            If Mid(strInput, i, 1) <> "-" Then Exit For
        Next i
        If i > 7 Then
            ExtractedNumbers = Replace(Mid(strInput, 2, 8), "-", ".")
        End If
    End If

End Function

Add an expression to your query that uses this function on the original text. For the filtering bit, set up the WHERE clause so this expression needs to have a length that's greater than zero.

Hope this helps,


(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    19th January 2018 - 02:46 AM