Text query

[knightwest]

Hi,
I am fairly new to VBA.

I am trying to split policy numbers from a field in an access table containing more text than just the policy number. This is the VBA we use in Excel, it looks at all the different types and isolates them:

FindText(1) = "*[A-Z][A-Z]#######[A-Z][A-Z][A-Z]*"
TextLength(1) = 12
RefType(1) = "Full"
FindText(2) = "*[A-Z][A-Z]#######[A-Z][A-Z]*"
TextLength(2) = 11
RefType(2) = "Full"
FindText(3) = "*87########[A-Z]##*"
TextLength(3) = 13
RefType(3) = "Full"
FindText(4) = "*87# #######[A-Z]##*"
TextLength(4) = 14
RefType(4) = "Full"
FindText(5) = "*[A-Z][A-Z]#######[A-Z]*"
TextLength(5) = 10
RefType(5) = "Part"
FindText(6) = "*#######[A-Z]*"
TextLength(6) = 8
RefType(6) = "Part"
FindText(7) = "*5######*"
TextLength(7) = 7
RefType(7) = "Full"

How can I use this code in access to do the same thing. We have to move this stuff to access because my boss wants it there.

Thanks
Knightwest

 

[Bodisathva]

You want to look into using the InStr function to find the text within the string, and most likely the Mid function to return the substring you are searching for.

 

[modest]

Good for your boss Access is mostly superior.

As Bod suggested, use the InStr() to find the text that you're looking for and use the Mid$() to retrieve the data. You may still use your arrays to store what you're looking for. However, you should use a multidimensional array instead of three different ones.

For instance instead of using FindText(7) and TextLength(7) and RefType(7), you could just use TextInfo(7,3)

TextInfo(X,1) would be FindText()
TextInfo(X,2) would be TextLength()
TextInfo(X,3) would be RefType()