If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
In String - Urgent Request
Is there a way to use the in string function to pull out text if you do not
know the position of the text you are looking for? For example, if my string shows up as "TJ GHO PRN DLVD" or "GHO PRN DLVD" as you can see, "GHO" does not always show up in the same position within the text field. How to I pull out "GHO" if it exist within the text field. Thanks much, |
#2
|
|||
|
|||
In String - Urgent Request
OOps, I forgot to mention that I am trying to pull out each of the char sets
to create a field for each one, so that I can then query off of each filed as needed. So I would have one filed with all of them, ex. "TJ GHO PRN DLV" and then a seperate field for each one, TJ, GHO, PRN, DLV. Thanks "Sean" wrote: Is there a way to use the in string function to pull out text if you do not know the position of the text you are looking for? For example, if my string shows up as "TJ GHO PRN DLVD" or "GHO PRN DLVD" as you can see, "GHO" does not always show up in the same position within the text field. How to I pull out "GHO" if it exist within the text field. Thanks much, |
#3
|
|||
|
|||
In String - Urgent Request
Search on the function 'Split' as it should do what you want (I only know of
its existence, not what the syntax might be). If you plan on using those new fields to search on for the char set then there is an easier way. Use this criteria -- Like "*" & [Enter Character String] & "*" "Sean" wrote: OOps, I forgot to mention that I am trying to pull out each of the char sets to create a field for each one, so that I can then query off of each filed as needed. So I would have one filed with all of them, ex. "TJ GHO PRN DLV" and then a seperate field for each one, TJ, GHO, PRN, DLV. Thanks "Sean" wrote: Is there a way to use the in string function to pull out text if you do not know the position of the text you are looking for? For example, if my string shows up as "TJ GHO PRN DLVD" or "GHO PRN DLVD" as you can see, "GHO" does not always show up in the same position within the text field. How to I pull out "GHO" if it exist within the text field. Thanks much, |
#4
|
|||
|
|||
In String - Urgent Request
I am unable to get the Split function to work, any thoughts on syntax?
"KARL DEWEY" wrote: Search on the function 'Split' as it should do what you want (I only know of its existence, not what the syntax might be). If you plan on using those new fields to search on for the char set then there is an easier way. Use this criteria -- Like "*" & [Enter Character String] & "*" "Sean" wrote: OOps, I forgot to mention that I am trying to pull out each of the char sets to create a field for each one, so that I can then query off of each filed as needed. So I would have one filed with all of them, ex. "TJ GHO PRN DLV" and then a seperate field for each one, TJ, GHO, PRN, DLV. Thanks "Sean" wrote: Is there a way to use the in string function to pull out text if you do not know the position of the text you are looking for? For example, if my string shows up as "TJ GHO PRN DLVD" or "GHO PRN DLVD" as you can see, "GHO" does not always show up in the same position within the text field. How to I pull out "GHO" if it exist within the text field. Thanks much, |
#5
|
|||
|
|||
In String - Urgent Request
As I said I know nothing of the syntax.
"Sean" wrote: I am unable to get the Split function to work, any thoughts on syntax? "KARL DEWEY" wrote: Search on the function 'Split' as it should do what you want (I only know of its existence, not what the syntax might be). If you plan on using those new fields to search on for the char set then there is an easier way. Use this criteria -- Like "*" & [Enter Character String] & "*" "Sean" wrote: OOps, I forgot to mention that I am trying to pull out each of the char sets to create a field for each one, so that I can then query off of each filed as needed. So I would have one filed with all of them, ex. "TJ GHO PRN DLV" and then a seperate field for each one, TJ, GHO, PRN, DLV. Thanks "Sean" wrote: Is there a way to use the in string function to pull out text if you do not know the position of the text you are looking for? For example, if my string shows up as "TJ GHO PRN DLVD" or "GHO PRN DLVD" as you can see, "GHO" does not always show up in the same position within the text field. How to I pull out "GHO" if it exist within the text field. Thanks much, |
#6
|
|||
|
|||
In String - Urgent Request
You cannot use split in a query. It returns an array which a query
cannot handle. You can write a custom VBA function to extract each part and place it in a separate field. A more complete explanation of what you want might help to give you a good solution. For instance, do you want to put the first value you extract into the first column, the second value into the next column, etc. Or do you want to put the values into one field in multiple records, So the first value goes in record 1, 2nd into record 2, etc. Also, are there a limited number of values. You seemed to suggest that there were only four values - TJ, GHO, PRN, and DLV. If you had up to four values and the values were exactly as stated and you wanted to put them into four new fields in your table THEN the solution would be to use four update queries to populate the values. You could use a query like the following. SELECT getSection([yourField]," ",1) as Column1 getSection([yourField]," ",2) as Column2 getSection([yourField]," ",3) as Column3 getSection([yourField]," ",4) as Column4 getSection([yourField]," ",5) as Column5 FROM [YourTable] The function below can be copied into a VBA module and called from a query (see above). Save the module with a name other than getSection. The function parses a string into sections and gets a specific section / Item / token from the string Public Function getSection(strIn, _ Optional strDelimiter As String = ";", _ Optional intSectionNumber As Integer = 1) '================================================= ============= ' Procedure : getSection ' Author : John Spencer ' Purpose : Return section of string ' Access 2000 and later '================================================= ============= Dim strArray As Variant If Len(strIn & vbNullString) = 0 Then getSection = strIn Else strArray = Split(strIn, strDelimiter, -1, vbTextCompare) If UBound(strArray) = intSectionNumber - 1 Then getSection = strArray(intSectionNumber - 1) Else getSection = Null End If End If End Function Public Function fGetToken(strIn, _ Optional strDelimiter As String = " ", _ Optional LPos As Long = 1) 'Return the Nth item from a delimited list of items. Dim strArr As Variant If Len(strIn & "") = 0 Then fGetToken = strIn Else strArr = Split(strIn, strDelimiter) If LPos - 1 = UBound(strArr) Then fGetToken = strArr(LPos - 1) Else fGetToken = Null End If End If End Function '================================================= === John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County '================================================= === KARL DEWEY wrote: As I said I know nothing of the syntax. "Sean" wrote: I am unable to get the Split function to work, any thoughts on syntax? "KARL DEWEY" wrote: Search on the function 'Split' as it should do what you want (I only know of its existence, not what the syntax might be). If you plan on using those new fields to search on for the char set then there is an easier way. Use this criteria -- Like "*" & [Enter Character String] & "*" "Sean" wrote: OOps, I forgot to mention that I am trying to pull out each of the char sets to create a field for each one, so that I can then query off of each filed as needed. So I would have one filed with all of them, ex. "TJ GHO PRN DLV" and then a seperate field for each one, TJ, GHO, PRN, DLV. Thanks "Sean" wrote: Is there a way to use the in string function to pull out text if you do not know the position of the text you are looking for? For example, if my string shows up as "TJ GHO PRN DLVD" or "GHO PRN DLVD" as you can see, "GHO" does not always show up in the same position within the text field. How to I pull out "GHO" if it exist within the text field. Thanks much, |
Thread Tools | |
Display Modes | |
|
|