numeric value from text

[Guest]

I have a text field: size
03'11 x 06'08

I would like to get the total square feet in a numeric value.
In this case it would be: 26.15 (3.92 x 6.67)

Thank you for your help!

 

[Guest]

First, I would like to say that if at all possible, you should change your
table structure to have these values in separate fields.

The following are expression fields you can put in a query with the table
containing the dimensions. I am assuming that there is always an apostrophy
between feet and inches and that there is always an x between length and
width. The text field in my example is named Dimension.

LengthText: Left([Dimension],InStr([Dimension],"x")-1)

WidthText: Mid([Dimension],InStr([Dimension],"x")+1,10)

Length:
Left([LengthText],InStr([LengthText],"'")-1)*1+Mid([LengthText],InStr([LengthText],"'")+1,10)*1/12

Width:
Left([WidthText],InStr([WidthText],"'")-1)*1+Mid([WidthText],InStr([WidthText],"'")+1,10)*1/12

SqrFeet: [Length]*[Width]


Hope this helps.

Jackie

 

[Guest]

First, I would like to say that if at all possible, you should change your
table structure to have these values in separate fields.

The following are expression fields you can put in a query with the table
containing the dimensions. I am assuming that there is always an apostrophy
between feet and inches and that there is always an x between length and
width. The text field in my example is named Dimension.

LengthText: Left([Dimension],InStr([Dimension],"x")-1)

WidthText: Mid([Dimension],InStr([Dimension],"x")+1,10)

Length:
Left([LengthText],InStr([LengthText],"'")-1)*1+Mid([LengthText],InStr([LengthText],"'")+1,10)*1/12

Width:
Left([WidthText],InStr([WidthText],"'")-1)*1+Mid([WidthText],InStr([WidthText],"'")+1,10)*1/12

SqrFeet: [Length]*[Width]


Hope this helps.

Jackie

 

[Guest]

So the actual data in that text field reads: 03'11 x 06'08 ?

You will need to parse the data so you can do the calculation.
If these assumptions are correct, you can use the below function: first two
chars are feet, 5 and 6 chars are inches for width....10 and 11 chars are
feet, 13 and 14 chars are inches for height

Paste this into a query and you can see how I parsed each section out and
the last column is the sq ft calculation:

ELECT size, Left([size],2) AS widthfeet, Mid([size],4,2)/12 AS widthinch,
Mid([size],9,2) AS heightfeet, Mid([size],12,2)/12 AS heightinch,
Left([size],2)+Mid([size],4,2)/12 AS width,
Mid([size],9,2)+Mid([size],12,2)/12 AS height,
(Left([size],2)+Mid([size],4,2)/12)*(Mid([size],9,2)+Mid([size],12,2)/12) AS
SqFt
FROM Table1;

Table1 is whatever your table name is.