separate information

[lions1855]

I'm looking to develop an access query to separate this information,

Product Height: 19.00 mm [0.75 in]
Product Length: 29.00 mm [1.14 in]
Carton Quantity: 80
Shipping (Package) Weight: 0.03 kg [0.07 lb]
Product Width: 26.00 mm [1.02 in]
Battery Type: Alkaline
Voltage: DC 4.5V

in separate columns. Moreover, the line that contains Shipping (Package) Weight: 0.03 kg [0.07 lb], I need to extract just the 0.07 lb.

Can someone help?

You can send me information to probles@digistorages.com

 

[vbaInet]

How many records have you got with those values?

You don't need to the conversions as part of that field. The calculations can be done and displayed when needs be.

 

[lions1855]

There are about 2,200 products. Each product has a column called [Technical]. Each record has about the same technical specifications specified on my first submission.

 

[vbaInet]

So each product has its specs right. This will extract the spec part of the string you want:

Mid(Trim([Product Height]), InStr(1, [Product Height], "["))

Don't use spaces when naming your fields. Be careful when you rename them (that's if you decide to) because you would have to change all the objects that rely on it.

 

[lions1855]

Perhaps, I'm not to an expert, but when I tried to use your code, it gave me a box to enter information. Where should I be using your code.

Thanks

 

[vbaInet]

Where did you put it at first?

 

[lions1855]

I created a query. then, right next the column name, I place the code.

Technical: Mid(Trim([Product Height]),InStr(1,[Product Height],"["))

This did not work. Then, I tried using it under criteria. it did not work either.

 

[vbaInet]

So in that query all you want to sure is the conversion yes? You're not going to to show the whole field and show the conversion seperately?

 

[lions1855]

I was hoping to separate each of the technical descriptions. Each product has several technical descriptions. Separating the information it will allow me to export the information to my import/export tool.

Another important information, before getting the information to access, the product came on excel sheet. I notices after the end of each line, there was small symbol like a little small square. I'm asuming it is the symbol for tab or space between each of the product descriptions.

 

[vbaInet]

I was hoping to separate each of the technical descriptions. Each product has several technical descriptions. Separating the information it will allow me to export the information to my import/export tool.

Sounds like this a db that you bought? In that case I would advise not to change it unless you know what you're doing. Just use the code in your query as Criteria or put it in the control source of a text box (with an equal to operator preceding it).

Another important information, before getting the information to access, the product came on excel sheet. I notices after the end of each line, there was small symbol like a little small square. I'm asuming it is the symbol for tab or space between each of the product descriptions.

Yes, it's a tab.

 

[lions1855]

Going back to first question, I need to extract the weight of each product. There is a line on the product description that shows the weight in lb. I need to pull this information for shipping and handling calculations.

Can you help me on how to extract this information, alone.

Thanks

 

[vbaInet]

... Just use the code in your query as Criteria or put it in the control source of a text box (with an equal to operator preceding it)...

You may find that the answer was included in my last reply

Or you're still unsure how to execute this?

 

[lions1855]

Thank you for all your help!

However, after typing the information on my query, it works up to a certain point. Below, I have included an example of the query result.

[0.75 in]
Product Length: 29.00 mm [1.14 in]
Carton Quantity: 80
Shipping (Package) Weight: 0.03 kg [0.07 lb]
Product Width: 26.00 mm [1.02 in]
Battery Type: Alkaline
Voltage: DC 4.5V

As you can see, each one of this lines has [ symbol. So, it looks to me as if the lines are actual individual rows inside the database cell. All I need is to separate the peice of information that shows the product Shipping (Package) Weight.

In the above example, the only information I need is 0.07 lb which is used to calculate shipping charges.

 

[vbaInet]

If you want the lb part of Shipping Weight, you change "Product Height" to the exact name of your shipping weight field and you put the criteria as an ALIAS. See attached

LB_Weight: Mid(Trim([[COLOR=Red][B]Shipping_Weight[/B][/COLOR]]), InStr(1, [[COLOR=Red][B]Shipping_Weight[/B][/COLOR]], "["))

If that doesn't work then it means the lb part isn't being saved in your table or there's more to the way the field data is saved.

 

[lions1855]

Really, I do appreciate your input on this subject. Perhaps, I was not very clear at the begining.

The information share above,

Product Length: 29.00 mm [1.14 in]
Carton Quantity: 80
Shipping (Package) Weight: 0.03 kg [0.07 lb]
Product Width: 26.00 mm [1.02 in]
Battery Type: Alkaline
Voltage: DC 4.5V

All these features are inside one single cell, under the same column [Technical].

My question was how to separate from inside the cell, just the lb option. Your code given above no doubts will work if the colum was label Shipping Weight.

My situation is a little more complicated.

 

[vbaInet]

Well, you should have mentioned that from the start. Anyone looking at what you wrote would assume that Procuct Length was a field, Carton Quality was a field, Shipping (Package) Weight was a field, etc.

Why are they all in one field? Is it also how they are represented in the table as well?