I have a parts table. In it there are two fields that have string info (Customer and CPN). For instance:
Parts Table Example:
In the example above, there are three customers that use the partNo 1678 separated by “/”, and the CPN is separated by an asterisk “*”
I want to create a temp table that looks like this:
I tried strVarCust1 = InStr(1, [strPartCustomer1], "/") to count the position before the first slash in vba with not much luck because the Parts table can have a customer field that has one customer, or three or more customers or it can be blank. The same with the CPN field.
I think I need a way to determine how many “/” or customers are in the Customer field and how many CPN’s are in the CPN field. Then write it to a string variable and then write the string variable to the temp table. But not sure how to do this or if this would be the correct solution.
There must be an easy way to do this.
Please help.
Thanks,
Ben
Parts Table Example:
PartNo | Customer | CPN |
1678 | Yen/Stick/Pearson | Yen: 2321*Stick: 52344J/45*Pearson: Hy45 |
1679 | Carson | Cy8911 |
1680 | ||
1681 | Harry's |
In the example above, there are three customers that use the partNo 1678 separated by “/”, and the CPN is separated by an asterisk “*”
I want to create a temp table that looks like this:
PartNo | Customer | CPN |
1678 | Yen | 2321 |
1678 | Stick | 52344J/45 |
1678 | Pearson | Hy45 |
I tried strVarCust1 = InStr(1, [strPartCustomer1], "/") to count the position before the first slash in vba with not much luck because the Parts table can have a customer field that has one customer, or three or more customers or it can be blank. The same with the CPN field.
I think I need a way to determine how many “/” or customers are in the Customer field and how many CPN’s are in the CPN field. Then write it to a string variable and then write the string variable to the temp table. But not sure how to do this or if this would be the correct solution.
There must be an easy way to do this.
Please help.
Thanks,
Ben