Multiplying field in query

[season]

I have a field that is called MH depth which list the depth in #ft'#in". I
need to convert this to inches, but do not know how to set up the query to do
this (bring out the numbers to multiply without the ' and " symbols). The
reason being is that the numbers can either have one or two numbers, ex.
10'3", 9'12", 2'2", 12'11" (four different setups possible). Does anyone
know how to set this up to bring the two numbers out seperately to then
multiply into inches. If someone could just get it into 2 columns, i can do
the multipling in a query. Thanks, Season

 

[fredg]

I have a field that is called MH depth which list the depth in #ft'#in". I
need to convert this to inches, but do not know how to set up the query to do
this (bring out the numbers to multiply without the ' and " symbols). The
reason being is that the numbers can either have one or two numbers, ex.
10'3", 9'12", 2'2", 12'11" (four different setups possible). Does anyone
know how to set this up to bring the two numbers out seperately to then
multiply into inches. If someone could just get it into 2 columns, i can do
the multipling in a query. Thanks, Season

How about just one column?

InInchesLeft([FieldName],InStr([FieldName],"'")-1) *12) +
Val(Mid([FieldName],InStr([FieldName],"'")+1)

 

[KARL DEWEY]

Try this --
SELECT YourDataTable.Depth, Val([Depth]) AS Feet,
IIf(Val(Right([Depth],3))>Val(Right([Depth],2)),Val(Right([Depth],3)),Val(Right([Depth],2))) AS Inches
FROM YourDataTable;

 

[Conan Kelly]

Season,

Is the data type of the "MH Depth" field/column text? IMO, that is a bad
design for the exact reason you are posting your question here. Are you the
person in charge of the design of this database? Can you change it? If you
can't get users to enter the length in decimal or fractional format, then
maybe you could use 2 different fields/columns: 1 for "Feet" and 1 for
"Inches". Then you could combine them together to look like [ft' in"] in a
query.

If that is what you are working towards, then try this SQL:

SELECT Table1.[MH Depth], Left([MH Depth],InStr([MH Depth],"'")-1) AS Feet,
Mid([MH Depth],InStr([MH Depth],"'")+1,InStr([MH Depth],Chr(34))-InStr([MH
Depth],"'")-1) AS Inches
FROM Table1;

(Make sure to change "Table1" to the name of your table.)


or enter these into the "Field:" row of you query design grid:

Feet: Left([MH Depth],InStr([MH Depth],"'")-1)

and

Inches: Mid([MH Depth],InStr([MH Depth],"'")+1,InStr([MH
Depth],Chr(34))-InStr([MH Depth],"'")-1)




I think that will do what you are asking.

What happens if the measurement is less than 1 foot? Will [0'5"] be entered
or will [5"] be entered. If the latter, these functions will not work for
you.

Also, what happens if letters are accidentally entered?

HTH,

Conan

 

[season]

it tells me that a ( has been left out. i tried to add one in a couple of
places, but i never got it to work properly. Thanks for your help, btw.

I have a field that is called MH depth which list the depth in #ft'#in". I
need to convert this to inches, but do not know how to set up the query to do
this (bring out the numbers to multiply without the ' and " symbols). The
reason being is that the numbers can either have one or two numbers, ex.
10'3", 9'12", 2'2", 12'11" (four different setups possible). Does anyone
know how to set this up to bring the two numbers out seperately to then
multiply into inches. If someone could just get it into 2 columns, i can do
the multipling in a query. Thanks, Season

How about just one column?

InInchesLeft([FieldName],InStr([FieldName],"'")-1) *12) +
Val(Mid([FieldName],InStr([FieldName],"'")+1)

 

[season]

I know that it is a bad design, but it is a big, complicated program where
they provide a couple of description fields amoung hundred of data fields
where they can enter whatever they want, and they just happen to put this
info in one of those. I have over 100 sites where i would have to calculate
this by hand or come up with a query to seperate it. I used your second
suggestion and that works (i guess i did not understand exactly how to use
the first because i never could get that set up properly). i will have to
scan for errors, but it still beats having to do all by hand, so thank you
very much. Oh, the measurement will never be less than a foot unless they
enter it wrong because we are talking about manhole depths which are never
that shallow, so that will not be a problem. Also, my last name happens to
be Kelly, by marriage though. Thanks again.

Season,

Is the data type of the "MH Depth" field/column text? IMO, that is a bad
design for the exact reason you are posting your question here. Are you the
person in charge of the design of this database? Can you change it? If you
can't get users to enter the length in decimal or fractional format, then
maybe you could use 2 different fields/columns: 1 for "Feet" and 1 for
"Inches". Then you could combine them together to look like [ft' in"] in a
query.

If that is what you are working towards, then try this SQL:

SELECT Table1.[MH Depth], Left([MH Depth],InStr([MH Depth],"'")-1) AS Feet,
Mid([MH Depth],InStr([MH Depth],"'")+1,InStr([MH Depth],Chr(34))-InStr([MH
Depth],"'")-1) AS Inches
FROM Table1;

(Make sure to change "Table1" to the name of your table.)


or enter these into the "Field:" row of you query design grid:

Feet: Left([MH Depth],InStr([MH Depth],"'")-1)

and

Inches: Mid([MH Depth],InStr([MH Depth],"'")+1,InStr([MH
Depth],Chr(34))-InStr([MH Depth],"'")-1)




I think that will do what you are asking.

What happens if the measurement is less than 1 foot? Will [0'5"] be entered
or will [5"] be entered. If the latter, these functions will not work for
you.

Also, what happens if letters are accidentally entered?

HTH,

Conan

I have a field that is called MH depth which list the depth in #ft'#in". I
need to convert this to inches, but do not know how to set up the query to
do
this (bring out the numbers to multiply without the ' and " symbols). The
reason being is that the numbers can either have one or two numbers, ex.
10'3", 9'12", 2'2", 12'11" (four different setups possible). Does anyone
know how to set this up to bring the two numbers out seperately to then
multiply into inches. If someone could just get it into 2 columns, i can
do
the multipling in a query. Thanks, Season

 

[fredg]

it tells me that a ( has been left out. i tried to add one in a couple of
places, but i never got it to work properly. Thanks for your help, btw.

I have a field that is called MH depth which list the depth in #ft'#in". I
need to convert this to inches, but do not know how to set up the query to do
this (bring out the numbers to multiply without the ' and " symbols). The
reason being is that the numbers can either have one or two numbers, ex.
10'3", 9'12", 2'2", 12'11" (four different setups possible). Does anyone
know how to set this up to bring the two numbers out seperately to then
multiply into inches. If someone could just get it into 2 columns, i can do
the multipling in a query. Thanks, Season

How about just one column?

InInchesLeft([FieldName],InStr([FieldName],"'")-1) *12) +
Val(Mid([FieldName],InStr([FieldName],"'")+1)

Sorry about that.
Yes, it's missing a close parenthesis at the very end.

InInches: (Left([FieldName],InStr([FieldName],"'")-1)*12) +
Val(Mid([FieldName],InStr([FieldName],"'")+1))

 

[Conan Kelly]

season,

I'm glad this worked for you.

FYI, the first option I listed is the SQL Syntax that Access automatically
generates whenever a query is designed. You can check the SQL of your
queries by clicking on the View menu > SQL View when you have the view open
in Design/Datasheet/etc... view.

If you post questions here often, many times it is helpful to see your SQL
script/syntax so we can be of better help. People will ask you to post your
SQL. Just switch to the SQL View and copy-n-paste your SQL here so we can
look at it.

If you inspect my 2 different solutions carefully, you will see that...

Feet: Left([MH Depth],InStr([MH Depth],"'")-1)

....in the 2nd solution translates to...

Left([MH Depth],InStr([MH Depth],"'")-1) AS Feet

....in the 1st one. And...

Inches: Mid([MH Depth],InStr([MH Depth],"'")+1,InStr([MH
Depth],Chr(34))-InStr([MH Depth],"'")-1)

....translates to...

Mid([MH Depth],InStr([MH Depth],"'")+1,InStr([MH Depth],Chr(34))-InStr([MH
Depth],"'")-1) AS Inches

If you know SQL syntax well enough (and are more comfortable typing rather
than clicking-n-dragging), then you could switch to SQL View and type
everything in to design a query. After you are done typing in the SQL and
you switch to Design View, Access will interpret the SQL and the query will
show up in Design View as if you created it with the mouse.

If you copied my 1st solution into the SQL View of a new query (replacing
everything already in there), changing table and field names appropriately,
then switched to Design View, Design View would display the query I came up
with.

HTH,

Conan

I know that it is a bad design, but it is a big, complicated program where
they provide a couple of description fields amoung hundred of data fields
where they can enter whatever they want, and they just happen to put this
info in one of those. I have over 100 sites where i would have to
calculate
this by hand or come up with a query to seperate it. I used your second
suggestion and that works (i guess i did not understand exactly how to use
the first because i never could get that set up properly). i will have to
scan for errors, but it still beats having to do all by hand, so thank you
very much. Oh, the measurement will never be less than a foot unless they
enter it wrong because we are talking about manhole depths which are never
that shallow, so that will not be a problem. Also, my last name happens
to
be Kelly, by marriage though. Thanks again.

Season,

Is the data type of the "MH Depth" field/column text? IMO, that is a bad
design for the exact reason you are posting your question here. Are you
the
person in charge of the design of this database? Can you change it? If
you
can't get users to enter the length in decimal or fractional format, then
maybe you could use 2 different fields/columns: 1 for "Feet" and 1 for
"Inches". Then you could combine them together to look like [ft' in"] in
a
query.

If that is what you are working towards, then try this SQL:

SELECT Table1.[MH Depth], Left([MH Depth],InStr([MH Depth],"'")-1) AS
Feet,
Mid([MH Depth],InStr([MH Depth],"'")+1,InStr([MH
Depth],Chr(34))-InStr([MH
Depth],"'")-1) AS Inches
FROM Table1;

(Make sure to change "Table1" to the name of your table.)


or enter these into the "Field:" row of you query design grid:

Feet: Left([MH Depth],InStr([MH Depth],"'")-1)

and

Inches: Mid([MH Depth],InStr([MH Depth],"'")+1,InStr([MH
Depth],Chr(34))-InStr([MH Depth],"'")-1)




I think that will do what you are asking.

What happens if the measurement is less than 1 foot? Will [0'5"] be
entered
or will [5"] be entered. If the latter, these functions will not work
for
you.

Also, what happens if letters are accidentally entered?

HTH,

Conan

I have a field that is called MH depth which list the depth in #ft'#in".
I
need to convert this to inches, but do not know how to set up the query
to
do
this (bring out the numbers to multiply without the ' and " symbols).
The
reason being is that the numbers can either have one or two numbers,
ex.
10'3", 9'12", 2'2", 12'11" (four different setups possible). Does
anyone
know how to set this up to bring the two numbers out seperately to then
multiply into inches. If someone could just get it into 2 columns, i
can
do
the multipling in a query. Thanks, Season