Converting various measurements to single format?

[natonstan]

I have a table containing a large amount of data, in this table there are measurements however they are not in a specific format, for example some may say 'feet' some 'ft' some even in 'mm' and 'cm', is there any way I could use SQL to create a converter of some kind so that I could get them to be the same format?

[CJ_London]

by same format do you mean same unit of measurement?

assuming this is the case, yes you can use sql to convert the existing data.

What this looks like depends on what your data looks like and how it is stored - one or two fields? So suggest provide some example data if you need further help - you'll also need the conversion factors to change to a common unit

[ssanfu]

Do you want to change the values or do you want to change the units descriptor?
You can use an update query to change the units descriptor.

You'll have to be careful that you don't change the units descriptor to the wrong descriptor; ie changing 100 mm to 100 cm.

[natonstan]

ideally I would like the measurements to all be in foot, there's is a single field, here's an example of what the list looks like:

.20 in
.3 in
.33 mm
.462 mm
.56 in
.7 mm
.72 in
.785 mm
.855 mm
.87 in
.96 mm

I need to basically have them converted to feet, I know there is 12inch to a foot, and 304.8mm to a foot, but I'm not sure how I would actually create some code to act as a converter.

[ssanfu]

The first step would be to have the measurements in one field and the units in another.

Then you can do the calculation to convert to feet. Why convert to feet? You are getting into some small numbers.

[natonstan]

The main reason I chose feet is because there's more values with 'ft' than anything, sure I could go with inches if it would make things easier.

[CJ_London]

to get the number part, use the val function

val(myField) will return '.86 in' as 0.86

then use the switch function to do the conversion - so something along the following lines to convert to inches

numfield: val(myfield)/switch(right(myfield,2)="in",1,right(myfield,2)="mm",25.4,right(myfield,2)="ft",1/12)

extend the switch function for other units.

personally I would use mm - you have a value which is .7mm which would convert to 0.027559 inches ...

or alternative keep your mixture of units, but in two fields and use a modified version of the above calculation to convert to a default/standard unit when required

[natonstan]

That's a HUGE help, that works perfectly thank you very much, now the only other issue I'm having is that there's a couple of fields where there's 3 measurements in the one field, such as 12inx12inx12in if that makes sense, I believe I need to create a loop so that the three can be separated?

[CJ_London]

that implies you need 3 fields - length, width, height/thickness

but no, you don't need a loop just a bit more complex calculation

the original val calc will get the first one

val(mid(myfield, instr(myfield,"x")+1)) will get the second one

val(mid(myfield, instrrev(myfield,"x")+1)) will get the third one

[natonstan]

I'm trying to run the above val calc with my data, and for some reason I'm getting a 'Enter Parameter Value' dialogue box, do you happen to know why this is happening? Here's the exact query I'm running:

SELECT
val(myfield)/switch(right(myfield,2)="in",1,right(myfield,2)="m m",25.4,right(myfield,2)="ft",1/12)
FROM tblExport;

[CJ_London]

is your field really called myField?

[orange]

It could be related to the space between the "m m" in your code???? doubtful
This query, once working, would display a series of numbers???

As ajax said, Is myfield the field name involved???

[natonstan]

The spacing in between the mm was causing the issue, I created a test table to try out the query and it seems to be working perfectly now, thanks guys.

[orange]

Steve, Ajax and I are happy to help. Glad you have it working

[natonstan]

Sorry to drag this back up again guys, I've managed to change the external table so that there are multiple fields:
PartNumber ManufacturerNumber Height Length Width

Obviously I'm using the Height, Length and Width fields, and again converting to inches, can anyone tell me how I would create a single query to convert values from all 3 fields, and then convert them? (The results can be in three fields also, it doesn't really matter)