Text and numbers

[Skibee]

I am very new to Access and don't know if the below is possible.

I have a table which has been downloaded into Access from other software
collecting data on stock.

One field is called containers and has data such as CX15, BX30, SX10 etc.
Another field is called number of trays and contains only numerical data.

I need to create a query that multiplies the number of trays by the
numerical component of the container field (ie 15, 30, 10) etc. I have used
the expression builder but only to perform straightforward calculations or
for date / time issues. Could anyone point me in the right direction?

Thanks for any help.

 

[Wayne-I-M]

As long as the number is always the last 2 digits to the right then its simple

Right([TableName]![containers],2)*[TableName]![number of trays]

If not it can get a little more complex.
change the TableName to what it really is

 

[Al Campagna]

Skibee,
We'll need a bit more info on the "logic" of your Containers data.
1. Is the numerical portion of the string ALWAYS 2 digits?
or
2. If No 1 is false... is there ALWAYS an "X" (and only one X)
before the numeric component?

Assuming number 1... and Container = "AX27"
= Val(Right(Container,2))
would yield a numeric value of 27... for use in your calculation.

Assuming number 2 and Container = "AX27"
=Val(Mid([Container],InStr([Container],"X")+1))
would yield a numeric value of 27... for use in your calculation.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Skibee]

Thank you both for taking the time to reply - and both solutions work!
also, thanks for actually explaining the logic behind the answers as
obviously this means I can apply the same principle in future on similar
issues.

Skibee,
We'll need a bit more info on the "logic" of your Containers data.
1. Is the numerical portion of the string ALWAYS 2 digits?
or
2. If No 1 is false... is there ALWAYS an "X" (and only one X)
before the numeric component?

Assuming number 1... and Container = "AX27"
= Val(Right(Container,2))
would yield a numeric value of 27... for use in your calculation.

Assuming number 2 and Container = "AX27"
=Val(Mid([Container],InStr([Container],"X")+1))
would yield a numeric value of 27... for use in your calculation.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I am very new to Access and don't know if the below is possible.

I have a table which has been downloaded into Access from other software
collecting data on stock.

One field is called containers and has data such as CX15, BX30, SX10 etc.
Another field is called number of trays and contains only numerical data.

I need to create a query that multiplies the number of trays by the
numerical component of the container field (ie 15, 30, 10) etc. I have
used
the expression builder but only to perform straightforward calculations or
for date / time issues. Could anyone point me in the right direction?

Thanks for any help.