Help with weight info in db

[Robert Blackwell]

I have a db that has weight of a product in it. The problem is that the
values aren't just numbers.

ie.
7.8 ounces
3.2 pounds
not available

This is what is provided to me, and I've in turn handed over the db to a
company to build a website for us. They are saying they can't use the
weights because it isn't a field with JUST numbers and they want me to
convert the whole thing just to pounds.

So, I have no clue how to do this, because
1. I need to first strip the word ounces out and then convert that number to
pounds. Whlie I understand the math of converting ounces to pounds etc on
pen and paper but no clue with access.
2. then strip the word pounds out too

Is this something I could do using the query wizard?

 

[dlw]

assume field1 is the weight:

IIf(Right([field1],6)="pounds",Val([field1]),Val([field1])/16)

 

[Tom Wickerath]

Hi Robert,

They are saying they can't use the weights because it isn't a field
with JUST numbers and they want me to convert the whole thing
just to pounds.

This is a very simple conversion that this company *should* have been
capable of doing, without having to come back to you for assistance. I would
take this as a red flag that the person or persons assigned to work this
project in the company you have hired may not be up-to-snuff.

Basically, you need to break this multi-part field into two fields: Quantity
and UnitOfMeasure (if you want to use different units of measure, such as
ounces, pounds, etc). If you export the table to Excel, you can use Excel's
menu item Data > Text to Columns to split your data. I often times find this
easier than trying to write a query to do the job. Then it's just a matter of
applying the appropriate conversions if you really want to convert all
quantities into a single unit of measure. You can copy the result in a column
in Excel, and paste the value into a new column, which is not driven by a
formula. Then you can delete the column with the equations, and re-import the
table.

Tom
___________________________________________

:

I have a db that has weight of a product in it. The problem is that the
values aren't just numbers.

ie.
7.8 ounces
3.2 pounds
not available

This is what is provided to me, and I've in turn handed over the db to a
company to build a website for us. They are saying they can't use the
weights because it isn't a field with JUST numbers and they want me to
convert the whole thing just to pounds.

So, I have no clue how to do this, because
1. I need to first strip the word ounces out and then convert that number to
pounds. Whlie I understand the math of converting ounces to pounds etc on
pen and paper but no clue with access.
2. then strip the word pounds out too

Is this something I could do using the query wizard?

 

[Robert Blackwell]

Thanks I'll give that a shot too.