Leading Zereos

[Nigel1969]

This is the first time I have used this forum, hopefully someone can assist.

I have a data field, which is submitted to me, which i need to trim, in order
to link on in future queries.

The field is padded out with leading zereos, but can also contains extra
digits on the end which are not required.
example.

000A123456H1

I require the alphanumberic data A123456 ("[A-Z]######")

The problem I am facing is that the padded zereos are not always 3 characters
and sometimes contain spaces

example

00A123456H
or
00 A1234546

Please can someone assist.

 

[Marshall Barton]

This is the first time I have used this forum, hopefully someone can assist.

I have a data field, which is submitted to me, which i need to trim, in order
to link on in future queries.

The field is padded out with leading zereos, but can also contains extra
digits on the end which are not required.
example.

000A123456H1

I require the alphanumberic data A123456 ("[A-Z]######")

The problem I am facing is that the padded zereos are not always 3 characters
and sometimes contain spaces

example

00A123456H
or
00 A1234546

I suggest that you create a function to parse the field.
Here's a simple minded example:

Public Function fGetID(x)
Dim k As Integer

For k = 1 To 4
If Mid(x, k) Like "[a-z]######" Then
fGetID = Mid(x, k, 7)
Exit Function
End If
Next k
' no match, try something else??
fGetID = x
End Function

 

[Nigel1969 via AccessMonster.com]

Many thanks, it works!!!

much appreciated.

This is the first time I have used this forum, hopefully someone can assist.

[quoted text clipped - 17 lines]

or
00 A1234546

I suggest that you create a function to parse the field.
Here's a simple minded example:

Public Function fGetID(x)
Dim k As Integer

For k = 1 To 4
If Mid(x, k) Like "[a-z]######" Then
fGetID = Mid(x, k, 7)
Exit Function
End If
Next k
' no match, try something else??
fGetID = x
End Function