Solved How to run a query on a column to give a different name (1 Viewer)

Sampoline

Member
Local time
Today, 13:29
Joined
Oct 19, 2020
Messages
161
1611718897378.png


Hi, I need to run a query to get the 'OriginalName' column to return back the 'RequiredName' column. I've actually done this in Excel as I'm experienced with formulas but not quite strong in the Access query department, anyone know what to do? Thanks.

And if possible, to return another separate column that will replace all hyphens with forward strokes/slashes and remove the leading zero for single digit numbers at the end like B/GA/A1/1 (instead of B-GA-A1-01).

EDIT: Noticed a mistake in my screenshot, line 6 required name should read as "B-GA-A4a-06"
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:29
Joined
Feb 28, 2001
Messages
27,001
OK, in what context? There are a couple of ways to get the implied lookup. Even that bit about getting the dashes to come back as slashes is easy. BUT that last part about leading zeros is going to be a lot tougher. Not impossible, though.

You want to run a query? You can't be doing that in isolation, so tell us about how you want this presented. At least two different possible scenarios come to mind and I would rather not confuse you by giving you the wrong one.

As to the dashes-to-slashes, there is a function called REPLACE that will do that in a single call.


That bit about removing a leading zero from a specific field is going to require a user-defined function, or UDF. But as a matter of fact, from what data you presented to us, a single UDF could do ALL of that WITHOUT a table. If the "required name" is always going to match the beginning of the "original name" then it would not be hard to do what you wanted with a single UDF. But again, rather than mislead you, I have to ask: Would ever occur that the original name would be B-GA-C3a-04-0001.jpg and the required name would NOT be B/GA/C3a/4?

My question about original/required names is to determine whether a single UDF could do everything or whether more complexity is required.
 

Sampoline

Member
Local time
Today, 13:29
Joined
Oct 19, 2020
Messages
161
OK, in what context? There are a couple of ways to get the implied lookup. Even that bit about getting the dashes to come back as slashes is easy. BUT that last part about leading zeros is going to be a lot tougher. Not impossible, though.

You want to run a query? You can't be doing that in isolation, so tell us about how you want this presented. At least two different possible scenarios come to mind and I would rather not confuse you by giving you the wrong one.

As to the dashes-to-slashes, there is a function called REPLACE that will do that in a single call.


That bit about removing a leading zero from a specific field is going to require a user-defined function, or UDF. But as a matter of fact, from what data you presented to us, a single UDF could do ALL of that WITHOUT a table. If the "required name" is always going to match the beginning of the "original name" then it would not be hard to do what you wanted with a single UDF. But again, rather than mislead you, I have to ask: Would ever occur that the original name would be B-GA-C3a-04-0001.jpg and the required name would NOT be B/GA/C3a/4?

My question about original/required names is to determine whether a single UDF could do everything or whether more complexity is required.
Hi Doc,

To answer your question, no, it won't be different. The Original Name should always relate to the Required Name. So if original name is B-GA-C3a-04-0001.jpg the required name will always be B/GA/C3a/4 and so on. Which I guess is a relief for me.

Basically I want to use this new column as a FK to another table. I'm doing some data auditing with this information, but this is all in the beginning stages. I'm sure once I keep progressing further I'll have more questions to ask.

Thanks.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:29
Joined
May 7, 2009
Messages
19,169
Create a function in a Module:
Code:
'''''''''''''
'arnelgp
Public Function RequiredName(ByVal sText As Variant) As String
    Dim v As Variant
    Dim i As Integer
    Dim sNew As String
    sText = sText & vbNullString
    If Len(sText) = 0 Then Exit Function
    v = Split(sText, "-")
    For i = 1 To 3
        sNew = sNew & v(i - 1) & "/"
    Next
    sNew = sNew & Format$(Val(v(3)), "00")
    RequiredName = sNew
End Function

.. then on your Query:

SELECT OriginalName, RequiredName([OriginalName]) AS RequiredName FROM yourTableName;
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:29
Joined
Feb 28, 2001
Messages
27,001
Rather than an actual lookup, I think what you want is a couple of functions inside a user-defined function. This will depend on that number for which you want to remove leading zeros to ALWAYS be the 4th string in the sequence and it must ALWAYS be numeric. If either of those is not true, this user-defined function won't do what you want.

First function you need is SPLIT


Next two functions are VAL (get numeric value of string) and CStr( Convert numeric value to string). You can look those up yourself if you need to.

For this to work in a query, you have to define the function in a general module (as opposed to a form's class module.)

Code:
Public Function CvtOrgReq( OrgName As String ) As String
Dim Partials As Variant                                       'individual parts go here
Dim Reqd As String                                            'reassemble parts here

Partial = Split( OrgName )                                  'split into parts (probably 5)
Partial(3) = CStr( Val( Partial(3) ) )                      'convert 4th part to number to lose leading zeros, then back to string
Reqd = Partial(0) & "/" & Partial(1) & "/" & Partial(2) & "/" & Partial(3)     'reassemble parts 0-3 (because it is a 0-based array)

Return Reqd                                                         'return the reassembed substrings, omitting the 5th substring

End Function

If the comments don't align correctly, it is because I was lazy.

To use it, you can have it as a function in an expression in VBA OR you can use it in a query.

Code:
SELECT A, B, C, CvtOrgReq( OriginalName) As RequiredName, E, .... FROM mytable WHERE some-condition ;

I shot this from the hip so you might have to play with it just a tad.
 

Sampoline

Member
Local time
Today, 13:29
Joined
Oct 19, 2020
Messages
161
Create a function in a Module:
Code:
'''''''''''''
'arnelgp
Public Function RequiredName(ByVal sText As Variant) As String
    Dim v As Variant
    Dim i As Integer
    Dim sNew As String
    sText = sText & vbNullString
    If Len(sText) = 0 Then Exit Function
    v = Split(sText, "-")
    For i = 1 To 3
        sNew = sNew & v(i - 1) & "/"
    Next
    sNew = sNew & Format$(Val(v(3)), "00")
    RequiredName = sNew
End Function

.. then on your Query:

SELECT OriginalName, RequiredName([OriginalName]) AS RequiredName FROM yourTableName;
Hi Arnel,

This worked for some of my fields except, but for ones like e.g. B/GA/C3a/01 becomes B/GA/C3a/01 (when it should be B/GA/C3a/1); or B/GA/C3/04.1 becomes B/GA/C3/04 (when it should be B/GA/C3/4.1); or B/JF/D3/094a becomes B/JF/D3/94 (when it should be B/JF/D3/94a)

I think the issue is when there is an alphanumeric value in the string? Not 100% sure.
 

Sampoline

Member
Local time
Today, 13:29
Joined
Oct 19, 2020
Messages
161
Rather than an actual lookup, I think what you want is a couple of functions inside a user-defined function. This will depend on that number for which you want to remove leading zeros to ALWAYS be the 4th string in the sequence and it must ALWAYS be numeric. If either of those is not true, this user-defined function won't do what you want.

First function you need is SPLIT


Next two functions are VAL (get numeric value of string) and CStr( Convert numeric value to string). You can look those up yourself if you need to.

For this to work in a query, you have to define the function in a general module (as opposed to a form's class module.)

Code:
Public Function CvtOrgReq( OrgName As String ) As String
Dim Partials As Variant                                       'individual parts go here
Dim Reqd As String                                            'reassemble parts here

Partial = Split( OrgName )                                  'split into parts (probably 5)
Partial(3) = CStr( Val( Partial(3) ) )                      'convert 4th part to number to lose leading zeros, then back to string
Reqd = Partial(0) & "/" & Partial(1) & "/" & Partial(2) & "/" & Partial(3)     'reassemble parts 0-3 (because it is a 0-based array)

Return Reqd                                                         'return the reassembed substrings, omitting the 5th substring

End Function

If the comments don't align correctly, it is because I was lazy.

To use it, you can have it as a function in an expression in VBA OR you can use it in a query.

Code:
SELECT A, B, C, CvtOrgReq( OriginalName) As RequiredName, E, .... FROM mytable WHERE some-condition ;

I shot this from the hip so you might have to play with it just a tad.
Hi Doc,

Quite a few of my values can contain alphanumeric text in the 3rd or 4th string in the sequence. And there is one odd discrepancy I found last night:

Found the value B/GA/C3/04.1 in there too. That was the only one, but that may mean in the future I could more data like that. Other than these two issues, every other number is exactly the same in pattern and sequence (as shown in my original post).
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:29
Joined
May 7, 2009
Messages
19,169
here is the modified function
Code:
'''''''''''''
'arnelgp
Public Function RequiredName(ByVal sText As Variant) As String
    Dim v As Variant
    Dim i As Integer
    Dim sNew As String
    Dim sLast As String
    sText = sText & vbNullString
    If Len(sText) = 0 Then Exit Function
    v = Split(sText, "-")
    For i = 1 To 3
        sNew = sNew & v(i - 1) & "/"
    Next
    sLast = v(3)
    Do While InStr(1, sLast, "0") = 1
        sLast = Replace$(sLast, "0", "", 1, 1)
    Loop
    sNew = sNew & sLast
    RequiredName = sNew
End Func
 

Sampoline

Member
Local time
Today, 13:29
Joined
Oct 19, 2020
Messages
161
here is the modified function
Code:
'''''''''''''
'arnelgp
Public Function RequiredName(ByVal sText As Variant) As String
    Dim v As Variant
    Dim i As Integer
    Dim sNew As String
    Dim sLast As String
    sText = sText & vbNullString
    If Len(sText) = 0 Then Exit Function
    v = Split(sText, "-")
    For i = 1 To 3
        sNew = sNew & v(i - 1) & "/"
    Next
    sLast = v(3)
    Do While InStr(1, sLast, "0") = 1
        sLast = Replace$(sLast, "0", "", 1, 1)
    Loop
    sNew = sNew & sLast
    RequiredName = sNew
End Func
Thanks Arnel,

I can see it all coming together now. After exploring the full list, I found a couple of ones that had the following sequence: B-GP-A3-13_scan-0001.jpg which would show up as B/GP/A3/13_scan is there a way to get rid of that text after the underscore? So should just be B/GP/A3/13

This is the final discrepancy, everything else has worked out.

Thankyou
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:29
Joined
May 7, 2009
Messages
19,169
Here is another modification:
Code:
'''''''''''''
'arnelgp
Public Function RequiredName(ByVal sText As Variant) As String
    Dim v As Variant
    Dim i As Integer
    Dim sNew As String
    Dim sLast As String
    Dim sLastChar As String
    sText = sText & vbNullString
    If Len(sText) = 0 Then Exit Function
    v = Split(sText, "-")
    For i = 1 To 3
        sNew = sNew & v(i - 1) & "/"
    Next
    sLast = v(3)
    Do While InStr(1, sLast, "0") = 1
        sLast = Replace$(sLast, "0", "", 1, 1)
    Loop
    i = InStr(sLast, "_")
    If i > 0 Then
        sLast = Left$(sLast, i - 1)
    End If
    sNew = sNew & sLast
    RequiredName = sNew
End Function
 

Sampoline

Member
Local time
Today, 13:29
Joined
Oct 19, 2020
Messages
161
Here is another modification:
Code:
'''''''''''''
'arnelgp
Public Function RequiredName(ByVal sText As Variant) As String
    Dim v As Variant
    Dim i As Integer
    Dim sNew As String
    Dim sLast As String
    Dim sLastChar As String
    sText = sText & vbNullString
    If Len(sText) = 0 Then Exit Function
    v = Split(sText, "-")
    For i = 1 To 3
        sNew = sNew & v(i - 1) & "/"
    Next
    sLast = v(3)
    Do While InStr(1, sLast, "0") = 1
        sLast = Replace$(sLast, "0", "", 1, 1)
    Loop
    i = InStr(sLast, "_")
    If i > 0 Then
        sLast = Left$(sLast, i - 1)
    End If
    sNew = sNew & sLast
    RequiredName = sNew
End Function
Hi Arnel, I've checked the list completely and you've solved my issue.

Thankyou so much! This was eating me up seriously.
 

Users who are viewing this thread

Top Bottom