Extract the data between two consecutive comma set (1 Viewer)

[asadakhtar]

102,34112,021502503130010010000,QAR,1,24,1,{NULL:0},{5:Y},6,14.5,31-MAR-2011

above data is in one field , i want and expression to extract the data between two consecutive comma set by specifying the comma range.

for example if i specify range 2 to 3 then formula should return "021502503130010010000"

 

[pr2-eugin]

Hello asadakhtar, Welcome to AWF..

You might need to look at the Split function to get you started.. It is very simple method.. You might be required to write it as a function, that will allow you to pass the range as arguments to the function.. Thus outputting only the needed bits..

Just post back if you get stuck, in my head it looks like a pretty easy one.. I might be wrong.. Good Luck..

 

[asadakhtar]

Amigo, Thanks for replying.

I am new in Access world. Can you guide me through how to put this function in place.? my data is in [Field1]

 

[pr2-eugin]

Did you look through the links I provided above?

 

[asadakhtar]

I read it from end to end. All gone above my head.

 

[pr2-eugin]

Okay.. See if this helps..

Public Function extractData(tmpStr As String, startRange As Long, endRange As Long, _
                            Optional delimiterStr As String = ",") As String
[COLOR=Green]'************************************************************
'   Code to extract String between comma/any other seperator
'
' USAGE Exmaple:
'   ? extractData("102,34112,021502503130010010000,QAR,1,24", 2, 3)
'     021502503130010010000
'   ? extractData("102,34112,021502503130010010000,QAR,1,24", 0, 1)
'     102
'Code Courtesy of
'  Paul Eugin
'**************************************************************[/COLOR]
    Dim tmpStrArr() As String, midStr As String, iCtr As Long
    
    tmpStrArr = Split(tmpStr, delimiterStr)
    
    If startRange < 0 Or endRange > UBound(tmpStrArr) Then
        extractData = midStr
        Exit Function
    End If
    
    For iCtr = startRange To endRange - 1
        midStr = midStr & tmpStrArr(iCtr)
    Next
    
    extractData = midStr
End Function

The code take in thee arguments and one optional argument.. The first argument is the string that needs to be Looked into, the second is the Range Start, the last mandatory argument is the End Range. The optional string is just there, you can pass a different delimiter..

To use this function, just simple copy it into a module, give it a name OTHER than extractData and then Compile.. You should be good to go.. To use it in a Query just use..

SELECT extractData(Field1, 2, 3) As someNewName FROM theTable;

Hope this helps..

 

[asadakhtar]

You are a star Paul. Thanks for this code courtesy...

 

[asadakhtar]

Can you please also assist to add following in above code:

to extract the values where line starts with 102 only

 

[pr2-eugin]

Just add it to the IF condition in the code, using InStr function..

 

[asadakhtar]

However, i dont have idea where exactly in the code but i wil trying that. Lets see if i managed to add this simple thing otherwise will buzz you again
now leaving office. speak tomorrow

 

[pr2-eugin]

There is only one If in the code.. Just change,

If startRange < 0 Or endRange > UBound(tmpStrArr) Then

To,

If (startRange < 0 Or endRange > UBound(tmpStrArr)) And ([URL="http://www.techonthenet.com/access/functions/string/left.php"]Left[/URL](tmpStr,3) <> "102") Then

I think InStr would not be a good option.. The right one to use is the Left function..

 

[asadakhtar]

So here is what i want to achieve next. I have wrote this on Microsoft access groups but it seems guys over there are not much into solving my prob .

I am doing financial analysis on product wise customer's data. Data in my file contains different structure of records and each record structure is unique based on first three digits.

So going forward, i want to extract "Product Amount" field for each customer.

With above code i am able to extract the "Customer ID" from my data. Now i want to extract the field "Product Amount".

Complex here is that "Start Range" and "End Range" for extracting "Product Amount" field varies from record to record, but at the same time this range is unique based on first three digits of each record.

I have added this Range and Codes Mapping in a separate table. Now challenge for me is how to amend above VB code to Lookup in mapping table for "Product code" and its corresponding "Start Range" and "End Range" and then extract the desired "Product Amount" field.

I have a feeling that by now you have amended the code as i desire, without even completely reading the whole story above.

 

[pr2-eugin]

Looking at it again and your description suggests that this is a CSV data.. Is it? If so why go through all this hassle and just simply perform an import? Or have I missed something that you have not yet said..

 

[asadakhtar]

Data is in .TXT file format. I have tried import the file but import function is not working fine as it does not import all fields.

Adressing this import issue, microsoft guy said to me that "all records in import file must have the same number of fields of matching datatypes. In your case, it appears that not only do you have a variable number of fields, they have variable datatypes"

 

[asadakhtar]

Paul, please help.

 

[pr2-eugin]

Try DLookUp..

 

[asadakhtar]

Can you expend a bit as to how i can add Dlook with extractdata ?

 

[pr2-eugin]

I have provided the Link in the post.. Anyway here it is..

http://access.mvps.org/access/general/gen0018.htm

Good Luck !