Split a field

[jiayue]

Hi,

I have a field which has already contain the product name as :

EnchSG100ml,
EnchSG100ml+RomaSH50ml,
EnchSH250ml+RomaHB100ml,
RomaSG700ml+EnchSH120ml,
RomaHB250ml,

(Where Ench/Roma are product brand, SG/SH/HB are product type & ml is the product volume & "+" means the it banded together as a single product.)

I would like to split this fiels to 2 fields as Product1 & Product2, how should I do so?

I had tried with Right / Left function but it does not work for a records with "+"

Thanks

 

[scriverb]

Are the rules here if there is just one product then a comma follows the Product1 and if two products they are split with the + sign.

Please respond with the confirmation or description of how all of the fields are setup and I can parse this for you.


[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

Try this:

Product1:

IIf(Len([[i][red]combinedfield[/red][/i]]) > 12, Mid$([[i][red]combinedfield[/red][/i]], 1, 11), Mid$([[i][red]combinedfield[/red][/i]], 1, 11))

Product2:

IIf(Len([[i][red]combinedfield[/red][/i]]) > 12, Mid$([[i][red]combinedfield[/red][/i]], 1, 11), Mid$([[i][red]combinedfield[/red][/i]], 1, 11))
Me.Text5 = IIf(Len([[i][red]combinedfield[/red][/i]]) > 12, Mid$([[i][red]combinedfield[/red][/i]], 13, 11), "")

These two expressions can be used in a select or as the value to update two new fields in an Update query of your table. Let me know if you need assistance setting these up.


[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jiayue]

The product 1 & the combine with "+" do not end with comma, sorry for misleading.

Thanks

 

[PHV]

Have you tried to play with the Split(...,"+") function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scriverb]

Sorry about my cutting and pasting from my test database:

Product1:

Mid$([combinedfield], 1, 11)

Product2:

IIf(Len([combinedfield]) > 12, Mid$([combinedfield], 13, 11), "")

This will work now.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jiayue]

sorry, i am new in programming & not sure of using Split function.
Also I do not know how to use the coding. I had try in the query expression but it does not work.

Thanks

 

[scriverb]

Here is code for a Select SQL query:

Select A.[[i][red]yourcombinedfield[/red][/i]], Mid$([[i][red]yourcombinedfield[/red][/i]], 1, 11) as Product1, IIf(Len([[i][red]yourcombinedfield[/red][/i]]) > 12, Mid$([[i][red]yourcombinedfield[/red][/i]], 13, 11), "") as Product2 
FROM [i][red]yourtablename[/red][/i] as A 
ORDER BY  A.[[i][red]yourcombinedfield[/red][/i]];

Update the red code with your field name and the name of the table and this will show you three columns. Your original field and then the two new fields with the products identified. If you need to permanently assign then new field names then we need to add two fields called Product1 and Product2 to the table and create an Update action query to update those two fields. let me know what you want to do to proceed.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jiayue]

I think you may misunderstand my product name. The DB I had does not have fix alphbet for product1 and product2. It may vary from 5 to 15 characters.

Also today i found out that the combineroduct field may contains product1+product2+product3...!!It make me further confuse..

 

[scriverb]

I am leaving right now for most of the day. PHV's suggestion to use the Split function should work quite well in this situation. If he doesn't respond to this by the time I get back then I will try to help this evening.

Try looking up the Split function in ACCESS Help and see if you can get that to work for you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

You need to determine what you want to do with the data after the split takes place. In your original posting you wanted to put into two fields. Now you say it could be any number of fields.

The following demonstrates how to parse a string field using the Split function and display the data in a individual line in the Debug window:

Dim arrParsed() As String, i As Integer
arrParsed = Split([yourconbinedfield], "+")
For i = 0 To UBound(arrParsed)
    Debug.Print arrBob(i)
Next i

When you figure out just what you want to do with the data I can help you place this data in other fields. You need to know how many is the max number of items per field. Do you want to add them as individual records in another table?

Let me know.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jiayue]

Probabaly let me explain again the data situation. The data I am download from my company system & I am try to analyse with query. One of the field, the so call [Product], has the combinations of either one or two or even three product. ( I understand that the table is not been normalised but I just can do anything at this moment).

My purpose is to split those products in the combinedfield which is seperated by "+". These products may contains digit from 5 to 15 digits & varies from different products.

I had been using Left() & Mid() which work well for 2 products only. For those with one producys (which is without the "+", it does not work. For those with 3 products, it fails too.

Hope this can explain your doubts.

Many thanks

 

[scriverb]

I don't really have any doubts here I am just trying to figure out just what you want to do with the data. You have a field called [/b]Product[/b] in your table. You also have three extra new fields for the individual products Product1, Product2, Product3. You want Product parsed into the three new fields if there is data to go in them, that is correct? If you one Product then Product1 gets the data and 2 and 3 are left blank. If this is the case please respond back and I will provide you with code to do this.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jiayue]

You are right, i need to have product 1, Product2 & Product3 fields.

Thanks

 

[scriverb]

Put this code with red code updated behind a Command Buttons OnClick event procedure:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim arrParsed() As String, i As Integer
Set db = CurrentDB
Set rs = db.OpenRecordset("[i][red]yourtablename[/red][/i]", dbOpenDynaset)
rs.MoveFirst
Do
   arrParsed = Split([Product], "+")
   rs.Edit
   rs("Product1") = IIF(Instr(arrParsed(0),",")>0, Left(aarParsed(0),Len(aarParsed(0))-1))
   rs("Product2") = IIF(Instr(arrParsed(1),",")>0, Left(aarParsed(1),Len(aarParsed(1))-1))
   rs("Product3") = IIF(Instr(arrParsed(2),",")>0, Left(aarParsed(2),Len(aarParsed(2))-1))
   rs.Update
   rs.MoveNext
Loop Until rs.EOF
rs.close
db.close

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[PHV]

In a standard code module, create a public function like this:
Public Function getProductByPos(strProduct, intPos As Integer)
On Error Resume Next
getProductByPos = Split(strProduct, "+")(intPos - 1)
End Function
And then in a query you can use something like this:
SELECT [product name]
, getProductByPos([product name],1) As Product1
, getProductByPos([product name],2) As Product2
, getProductByPos([product name],3) As Product3
FROM [your table]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bsim]

I have something similar, but a bit more complex.

I have two databases.

table 1 contains
name
address

table 2 contains
name (not exact field match to table 1 name)
address (not exact field match to table 1 address)
phone
extended information

I'm trying to use table 1 name and address fields to find the corresponding information in table 2.

The field data will not be exact in the corresponding fields, so i'm thinking that the fields need to be broken down by using the first word in table 1 name (probably using SPLIT), searching for this name in ALL of table 2 and then any matches can be cross checked by the street address # in table 2.

it is almost 2 simple queries except for the breaking down of the fields, and searching an entire table for a match, and the cross checking of the address # field.

I'm thinking i could use the code posted above (with a little modification) by "scriverb" to split the fields, but how do I implement the "Command Buttons OnClick event procedure", and how would i search ALL of the second table to find matches, and then cross check them?

Can anyone help?