Extracting Multiple Text Strings From Text Field

[gracm25]

Ok, I've reviewed almost every post thus far about how to extract a specific text string from a text/memo field. I have not yet found how to extract multiple text strings from a single text field that fit the same criteria. Let me be more specific...I am using the Mid and InStr functions to capture a 10-character alphanumeric text string from my text fields (i.e. K034567890). I've created multiple Mid and InStr fields for every possible letter (A0, B0, C0, etc.). It always has a letter, then a 0, then 8 digits. My problem exists when the same letter and then 0 can occur multiple times in the same text field (J012345678, J012345679, J012345670, etc.). How can I fix my query to pull out EVERY POSSIBLE letter, 0, and then 8 digit sequence even if the criteria is exactly the same. I would prefer help with a query-based answer. If someone has a solution venturing into the world of VBA, you'll have to REALLY dumb it down for me. Thank you for any help you can provide.

 

[MSAccessRookie]

Ok, I've reviewed almost every post thus far about how to extract a specific text string from a text/memo field. I have not yet found how to extract multiple text strings from a single text field that fit the same criteria. Let me be more specific...I am using the Mid and InStr functions to capture a 10-character alphanumeric text string from my text fields (i.e. K034567890). I've created multiple Mid and InStr fields for every possible letter (A0, B0, C0, etc.). It always has a letter, then a 0, then 8 digits. My problem exists when the same letter and then 0 can occur multiple times in the same text field (J012345678, J012345679, J012345670, etc.). How can I fix my query to pull out EVERY POSSIBLE letter, 0, and then 8 digit sequence even if the criteria is exactly the same. I would prefer help with a query-based answer. If someone has a solution venturing into the world of VBA, you'll have to REALLY dumb it down for me. Thank you for any help you can provide.

See if the following link gives you any insight as to how to split a string

http://msdn.microsoft.com/en-us/library/aa263365(VS.60).aspx

 

[boblarson]

And I'll ask the other question -

Why are you storing more than one value in a single field? Does it come from somewhere else like that? Or have you, or your users, entered it like that. If the the latter then bad design.

 

[gracm25]

boblarson,

It comes from Oracle databases that are maintained in another state. I access them using an ODBC connection.

 

[gracm25]

MSAccessRookie, thank you for the website link. May seem like a silly question, but is this an update in Access 2007? I'm still running 2003. Our company is looking to upgrade to 2007 in Q3/Q4 2010. I tried looking for the Split function in the Build button. I can't find it. I'm hesitant to venture into VBA since I suck at it. But, if the Split function is available on VBA in Access 2003, I'll take a swing at it.

 

[MSAccessRookie]

MSAccessRookie, thank you for the website link. May seem like a silly question, but is this an update in Access 2007? I'm still running 2003. Our company is looking to upgrade to 2007 in Q3/Q4 2010. I tried looking for the Split function in the Build button. I can't find it. I'm hesitant to venture into VBA since I suck at it. But, if the Split function is available on VBA in Access 2003, I'll take a swing at it.

Sorry for the confusion. As far as I am aware, The Split() functionality is part of VB 6.0, and has existed since Access 2000 (I think), so I believe that it should apply to Access 2003.

 

[boblarson]

Yes, it is there but you can't use it in the builder as it builds a single dimension array. So you use it in code.

Dim varSplit As Variant

varSplit = Split(strSomeText, ",")

And then you refer to it using the array:

Me.Textbox = varSplit(0)
Me.Othertextbox = varSplit(1)

etc.