Splitting one record to 2 records

[Louiscande]

Hi there,

I have a database that has field "location".

In this field shows the following for one record:

203:4~204:7

Which mean: In location 203 a qty 3 of an item and the same item is in location 204 qty 7.

I want to break that field at the "~" and have two records with the two different locations and quantities.

Item location qty
box 203 3
box 204 7

I think I can do it through an update query or update button with vba.

Can someone help on this.

Thank you in advance.

LouisC
Splitting Hair

 

[jdraw]

Hi there,

I have a database that has field "location".

In this field shows the following for one record:

203:4~204:7

Which mean: In location 203 a qty 3 of an item and the same item is in location 204 qty 7.

I want to break that field at the "~" and have two records with the two different locations and quantities.

Item location qty
box 203 3
box 204 7

I think I can do it through an update query or update button with vba.

Can someone help on this.

Thank you in advance.

LouisC
Splitting Hair

Twice in your post you say that 203:4 means location 203 and quantity 3 ---
I don't get it. But you are wise to change this symbology where you have too much intelligence in your coding.

 

[Louiscande]

I 'm sorry. The correct post is:

In this field shows the following for one record:

203:3~204:7

Which mean: In location 203 a qty 3 of an item and the same item is in location 204 qty 7.

I want to break that field at the "~" and have two records with the two different locations and quantities.

Item location qty
box 203 3
box 204 7

Good catch jdraw

LouisC

 

[HiTechCoach]

Hi there,

I have a database that has field "location".

In this field shows the following for one record:

203:4~204:7

Which mean: In location 203 a qty 3 of an item and the same item is in location 204 qty 7.

I want to break that field at the "~" and have two records with the two different locations and quantities.

Item location qty
box 203 3
box 204 7

I think I can do it through an update query or update button with vba.

Can someone help on this.

Thank you in advance.

LouisC
Splitting Hair

If you want to create two new record, then you would need to use an append query to create records. An update query just changes existing records.


The first query would append records using the first part ff the field. A second query would then create records using the second half of the field.

Here is one way to split out the data:

? Left(Left("203:4~204:7",Instr("203:4~204:7","~")-1), Instr(Left("203:4~204:7",Instr("203:4~204:7","~")-1),":")-1 )
203

? Mid(Left("203:4~204:7",Instr("203:4~204:7","~")-1), Instr(Left("203:4~204:7",Instr("203:4~204:7","~")-1),":")+1 )
4

To use in a query, replace the "203:4~204:7" with your field name

example in a query:

Location: Left(Left([myFieldName],Instr([myFieldName],"~")-1), Instr(Left([myFieldName],Instr([myFieldName],"~")-1),":")-1 )