query expression too complex

G

Guest

I know the following expression is a little wild but it's within the
character limit and as far as I can tell I haven't made any syntax errors
however when I try to execute it the "Expression is too complex" error
returns.

Have I done something wrong or have I pushed Access too far?

Thanks!!!

OFCapml: IIf([OFCapUOM]="fl.
oz.",[OFCap]*29.573529563,IIf([OFCapUOM]="pt.",[OFCap]*473.176473,IIf([OFCapUOM]="qt.",[OFCap]*946.352946,IIf([OFCapUOM]="gal.",[OFCap]*3785.411784,IIf([OFCapUOM]="bbl.",[OFCap]*119240.471196,IIf([OFCapUOM]="cu.
in.",[OFCap]*16.387064,IIf([OFCapUOM]="cu.
ft.",[OFCap]*28316.846592,IIf([OFCapUOM]="cu.
yd.",[OFCap]*764554.857984,IIf([OFCapUOM]="µ",[OFCap]*0.001,IIf([OFCapUOM]="ml",[OFCap]*1,IIf([OFCapUOM]="cl",[OFCap]*10,IIf([OFCapUOM]="dl",[OFCap]*100,IIf([OFCapUOM]="l",[OFCap]*1000,IIf([OFCapUOM]="cu
mm",[OFCap]*0.001,IIf([OFCapUOM]="cu cm",[OFCap]*1,IIf([OFCapUOM]="cu
m",[OFCap]*1000000,IIf([OFCapUOM]="oz.",[OFCap]*28.349523125,IIf([OFCapUOM]="lb.",[OFCap]*453.59237,IIf([OFCapUOM]="T.",[OFCap]*907184.74,IIf([OFCapUOM]="mg",[OFCap]*0.001,IIf([OFCapUOM]="g",[OFCap]*1,IIf([OFCapUOM]="kg",[OFCap]*1000,IIf([OFCapUOM]="MT",[OFCap]*1000000)))))))))))))))))))))))
 
M

Marshall Barton

JohnLute said:
I know the following expression is a little wild but it's within the
character limit and as far as I can tell I haven't made any syntax errors
however when I try to execute it the "Expression is too complex" error
returns.

Have I done something wrong or have I pushed Access too far?

Thanks!!!

OFCapml: IIf([OFCapUOM]="fl.
oz.",[OFCap]*29.573529563,IIf([OFCapUOM]="pt.",[OFCap]*473.176473,IIf([OFCapUOM]="qt.",[OFCap]*946.352946,IIf([OFCapUOM]="gal.",[OFCap]*3785.411784,IIf([OFCapUOM]="bbl.",[OFCap]*119240.471196,IIf([OFCapUOM]="cu.
in.",[OFCap]*16.387064,IIf([OFCapUOM]="cu.
ft.",[OFCap]*28316.846592,IIf([OFCapUOM]="cu.
yd.",[OFCap]*764554.857984,IIf([OFCapUOM]="µ",[OFCap]*0.001,IIf([OFCapUOM]="ml",[OFCap]*1,IIf([OFCapUOM]="cl",[OFCap]*10,IIf([OFCapUOM]="dl",[OFCap]*100,IIf([OFCapUOM]="l",[OFCap]*1000,IIf([OFCapUOM]="cu
mm",[OFCap]*0.001,IIf([OFCapUOM]="cu cm",[OFCap]*1,IIf([OFCapUOM]="cu
m",[OFCap]*1000000,IIf([OFCapUOM]="oz.",[OFCap]*28.349523125,IIf([OFCapUOM]="lb.",[OFCap]*453.59237,IIf([OFCapUOM]="T.",[OFCap]*907184.74,IIf([OFCapUOM]="mg",[OFCap]*0.001,IIf([OFCapUOM]="g",[OFCap]*1,IIf([OFCapUOM]="kg",[OFCap]*1000,IIf([OFCapUOM]="MT",[OFCap]*1000000)))))))))))))))))))))))
Click to expand...


I don't know, it's too difficult to count the commas and
parenthesis :-(

An alternative that's at least sane could be:

OFCap * Switch(OFCapUOM="fl. oz.",29.573529563,
OFCapUOM="pt.",473.176473, OFCapUOM]="qt.",946.352946,
. . .

However, a better way would be to create a table with the
conversion factors:

table ConversionFactors
Unit Factor
fl. oz. 29.573529563,
pt. 473.176473
qt. 946.352946
. . .
and join that table to your data table. Then you can just
use the Factor field in your query.
 
K

Ken Snell \(MVP\)

Let us suggest a different way to do this, based on what you've posted so
far.

Create a new table in your database; name it tblUOMConversions. Add these
fields:
UOMConvID PrimaryKey
OFCapUOM field that holds identification of UOM (text field)
ConvUOM field that holds the UOM identification to which
you want to convert (text field)
ConvValue field that holds the conversion value (make it
a Double number field)

I assume that the value of ConvUOM would be ml if the "new" unit should be
milliliters (per your post). However, note that you would add one record for
each unique combination of UOMs (starting and ending) to go into the
OFCapUOM and ConvUOM fields respectively.

Now change your expression for this calculated field to this:

OFCapml: Nz(DLookup("ConvValue", "tblUOMConversions", "[OFCapUOM]='" &
[OFCapUOM] & "' And ConvUOM='ml'"),0)*[OFCap]


Now, let me also state that you could redesign the query to use outer join
from your original table to this tblUOMConversions table, and then you
wouldn't need to use the slower DLookup function in an expression. But, to
suggest how that would be done, we'd need to know more about your actual SQL
statement of the current query, whether there are other conversions that
you're needing (say, to fl oz, pt, etc.).
 
P

Pieter Wijnen

Sometimes I'm glad that I live in a metric world <g>

Pieter

Ken Snell (MVP) said:
Let us suggest a different way to do this, based on what you've posted so
far.

Create a new table in your database; name it tblUOMConversions. Add these
fields:
UOMConvID PrimaryKey
OFCapUOM field that holds identification of UOM (text
field)
ConvUOM field that holds the UOM identification to which
you want to convert (text field)
ConvValue field that holds the conversion value (make it
a Double number field)

I assume that the value of ConvUOM would be ml if the "new" unit should be
milliliters (per your post). However, note that you would add one record
for each unique combination of UOMs (starting and ending) to go into the
OFCapUOM and ConvUOM fields respectively.

Now change your expression for this calculated field to this:

OFCapml: Nz(DLookup("ConvValue", "tblUOMConversions", "[OFCapUOM]='" &
[OFCapUOM] & "' And ConvUOM='ml'"),0)*[OFCap]


Now, let me also state that you could redesign the query to use outer join
from your original table to this tblUOMConversions table, and then you
wouldn't need to use the slower DLookup function in an expression. But, to
suggest how that would be done, we'd need to know more about your actual
SQL statement of the current query, whether there are other conversions
that you're needing (say, to fl oz, pt, etc.).

--

Ken Snell
<MS ACCESS MVP>




JohnLute said:
I know the following expression is a little wild but it's within the
character limit and as far as I can tell I haven't made any syntax errors
however when I try to execute it the "Expression is too complex" error
returns.

Have I done something wrong or have I pushed Access too far?

Thanks!!!

OFCapml: IIf([OFCapUOM]="fl.
oz.",[OFCap]*29.573529563,IIf([OFCapUOM]="pt.",[OFCap]*473.176473,IIf([OFCapUOM]="qt.",[OFCap]*946.352946,IIf([OFCapUOM]="gal.",[OFCap]*3785.411784,IIf([OFCapUOM]="bbl.",[OFCap]*119240.471196,IIf([OFCapUOM]="cu.
in.",[OFCap]*16.387064,IIf([OFCapUOM]="cu.
ft.",[OFCap]*28316.846592,IIf([OFCapUOM]="cu.
yd.",[OFCap]*764554.857984,IIf([OFCapUOM]="µ",[OFCap]*0.001,IIf([OFCapUOM]="ml",[OFCap]*1,IIf([OFCapUOM]="cl",[OFCap]*10,IIf([OFCapUOM]="dl",[OFCap]*100,IIf([OFCapUOM]="l",[OFCap]*1000,IIf([OFCapUOM]="cu
mm",[OFCap]*0.001,IIf([OFCapUOM]="cu cm",[OFCap]*1,IIf([OFCapUOM]="cu
m",[OFCap]*1000000,IIf([OFCapUOM]="oz.",[OFCap]*28.349523125,IIf([OFCapUOM]="lb.",[OFCap]*453.59237,IIf([OFCapUOM]="T.",[OFCap]*907184.74,IIf([OFCapUOM]="mg",[OFCap]*0.001,IIf([OFCapUOM]="g",[OFCap]*1,IIf([OFCapUOM]="kg",[OFCap]*1000,IIf([OFCapUOM]="MT",[OFCap]*1000000)))))))))))))))))))))))
Click to expand...
Click to expand...
 
G

Guest

Wow! What a couple great suggestions. Ken's was a good one, too however I
went with yours because I already have the table in place - just needed to
add a conversion factor field.

If you're curious I ended up creating two queries. The first:

SELECT tblProfiles.txtProfileID,
[tblPKBTGLPhysicalAttributes].[OverflowCapacity] & "" &
[tblPKBTPLPhysicalAttributes].[OverflowCapacity] & "" &
[tblPKCPPhysicalAttributes].[OverflowCapacity] & "" &
[tblPKDRPhysicalAttributes].[OverflowCapacity] & "" &
[tblPKPKMSPhysicalAttributes].[OverflowCapacity] & "" &
[tblPKTHPhysicalAttributes].[OverflowCapacity] AS OFCap,
[tblPKBTGLPhysicalAttributes].[OCUOM] & "" &
[tblPKBTPLPhysicalAttributes].[OCUOM] & "" &
[tblPKCPPhysicalAttributes].[OCUOM] & "" &
[tblPKDRPhysicalAttributes].[OCUOM] & "" &
[tblPKPKMSPhysicalAttributes].[OCUOM] & "" &
[tblPKTHPhysicalAttributes].[OCUOM] AS OFCapUOM
FROM (((((tblProfiles LEFT JOIN tblPKBTGLPhysicalAttributes ON
tblProfiles.txtProfileID = tblPKBTGLPhysicalAttributes.txtProfileID) LEFT
JOIN tblPKBTPLPhysicalAttributes ON tblProfiles.txtProfileID =
tblPKBTPLPhysicalAttributes.txtProfileID) LEFT JOIN tblPKCPPhysicalAttributes
ON tblProfiles.txtProfileID = tblPKCPPhysicalAttributes.txtProfileID) LEFT
JOIN tblPKDRPhysicalAttributes ON tblProfiles.txtProfileID =
tblPKDRPhysicalAttributes.txtProfileID) LEFT JOIN tblPKPKMSPhysicalAttributes
ON tblProfiles.txtProfileID = tblPKPKMSPhysicalAttributes.txtProfileID) LEFT
JOIN tblPKTHPhysicalAttributes ON tblProfiles.txtProfileID =
tblPKTHPhysicalAttributes.txtProfileID
ORDER BY [tblPKBTGLPhysicalAttributes].[OverflowCapacity] & "" &
[tblPKBTPLPhysicalAttributes].[OverflowCapacity] & "" &
[tblPKCPPhysicalAttributes].[OverflowCapacity] & "" &
[tblPKDRPhysicalAttributes].[OverflowCapacity] & "" &
[tblPKPKMSPhysicalAttributes].[OverflowCapacity] & "" &
[tblPKTHPhysicalAttributes].[OverflowCapacity] DESC;

And the second:

SELECT qryPKsContainerOFCaps.txtProfileID, qryPKsContainerOFCaps.OFCap,
qryPKsContainerOFCaps.OFCapUOM, [OFCap]*[mlConvFactor] AS mlConv
FROM qryPKsContainerOFCaps INNER JOIN tblUOMVolumeLiquidMass ON
qryPKsContainerOFCaps.OFCapUOM = tblUOMVolumeLiquidMass.txtUOMVolumeLiquid;

It's working just dandy. Thanks for your help! I never thought of using the
existing table. I created the table as it's used throughout my database and I
thought it was the best way to keep things consistent. Never imagined it
would come in handy as a lookup in this manner.

--
www.Marzetti.com


Marshall Barton said:
JohnLute said:
I know the following expression is a little wild but it's within the
character limit and as far as I can tell I haven't made any syntax errors
however when I try to execute it the "Expression is too complex" error
returns.

Have I done something wrong or have I pushed Access too far?

Thanks!!!

OFCapml: IIf([OFCapUOM]="fl.
oz.",[OFCap]*29.573529563,IIf([OFCapUOM]="pt.",[OFCap]*473.176473,IIf([OFCapUOM]="qt.",[OFCap]*946.352946,IIf([OFCapUOM]="gal.",[OFCap]*3785.411784,IIf([OFCapUOM]="bbl.",[OFCap]*119240.471196,IIf([OFCapUOM]="cu.
in.",[OFCap]*16.387064,IIf([OFCapUOM]="cu.
ft.",[OFCap]*28316.846592,IIf([OFCapUOM]="cu.
yd.",[OFCap]*764554.857984,IIf([OFCapUOM]="µ",[OFCap]*0.001,IIf([OFCapUOM]="ml",[OFCap]*1,IIf([OFCapUOM]="cl",[OFCap]*10,IIf([OFCapUOM]="dl",[OFCap]*100,IIf([OFCapUOM]="l",[OFCap]*1000,IIf([OFCapUOM]="cu
mm",[OFCap]*0.001,IIf([OFCapUOM]="cu cm",[OFCap]*1,IIf([OFCapUOM]="cu
m",[OFCap]*1000000,IIf([OFCapUOM]="oz.",[OFCap]*28.349523125,IIf([OFCapUOM]="lb.",[OFCap]*453.59237,IIf([OFCapUOM]="T.",[OFCap]*907184.74,IIf([OFCapUOM]="mg",[OFCap]*0.001,IIf([OFCapUOM]="g",[OFCap]*1,IIf([OFCapUOM]="kg",[OFCap]*1000,IIf([OFCapUOM]="MT",[OFCap]*1000000)))))))))))))))))))))))
Click to expand...


I don't know, it's too difficult to count the commas and
parenthesis :-(

An alternative that's at least sane could be:

OFCap * Switch(OFCapUOM="fl. oz.",29.573529563,
OFCapUOM="pt.",473.176473, OFCapUOM]="qt.",946.352946,
. . .

However, a better way would be to create a table with the
conversion factors:

table ConversionFactors
Unit Factor
fl. oz. 29.573529563,
pt. 473.176473
qt. 946.352946
. . .
and join that table to your data table. Then you can just
use the Factor field in your query.
Click to expand...
 
G

Guest

Thanks, Ken! This is a great idea. I went with Marshall's suggestion however
as I already had a table to work with. I'll definitely file your idea for
future reference!

--
www.Marzetti.com


Ken Snell (MVP) said:
Let us suggest a different way to do this, based on what you've posted so
far.

Create a new table in your database; name it tblUOMConversions. Add these
fields:
UOMConvID PrimaryKey
OFCapUOM field that holds identification of UOM (text field)
ConvUOM field that holds the UOM identification to which
you want to convert (text field)
ConvValue field that holds the conversion value (make it
a Double number field)

I assume that the value of ConvUOM would be ml if the "new" unit should be
milliliters (per your post). However, note that you would add one record for
each unique combination of UOMs (starting and ending) to go into the
OFCapUOM and ConvUOM fields respectively.

Now change your expression for this calculated field to this:

OFCapml: Nz(DLookup("ConvValue", "tblUOMConversions", "[OFCapUOM]='" &
[OFCapUOM] & "' And ConvUOM='ml'"),0)*[OFCap]


Now, let me also state that you could redesign the query to use outer join
from your original table to this tblUOMConversions table, and then you
wouldn't need to use the slower DLookup function in an expression. But, to
suggest how that would be done, we'd need to know more about your actual SQL
statement of the current query, whether there are other conversions that
you're needing (say, to fl oz, pt, etc.).

--

Ken Snell
<MS ACCESS MVP>




JohnLute said:
I know the following expression is a little wild but it's within the
character limit and as far as I can tell I haven't made any syntax errors
however when I try to execute it the "Expression is too complex" error
returns.

Have I done something wrong or have I pushed Access too far?

Thanks!!!

OFCapml: IIf([OFCapUOM]="fl.
oz.",[OFCap]*29.573529563,IIf([OFCapUOM]="pt.",[OFCap]*473.176473,IIf([OFCapUOM]="qt.",[OFCap]*946.352946,IIf([OFCapUOM]="gal.",[OFCap]*3785.411784,IIf([OFCapUOM]="bbl.",[OFCap]*119240.471196,IIf([OFCapUOM]="cu.
in.",[OFCap]*16.387064,IIf([OFCapUOM]="cu.
ft.",[OFCap]*28316.846592,IIf([OFCapUOM]="cu.
yd.",[OFCap]*764554.857984,IIf([OFCapUOM]="µ",[OFCap]*0.001,IIf([OFCapUOM]="ml",[OFCap]*1,IIf([OFCapUOM]="cl",[OFCap]*10,IIf([OFCapUOM]="dl",[OFCap]*100,IIf([OFCapUOM]="l",[OFCap]*1000,IIf([OFCapUOM]="cu
mm",[OFCap]*0.001,IIf([OFCapUOM]="cu cm",[OFCap]*1,IIf([OFCapUOM]="cu
m",[OFCap]*1000000,IIf([OFCapUOM]="oz.",[OFCap]*28.349523125,IIf([OFCapUOM]="lb.",[OFCap]*453.59237,IIf([OFCapUOM]="T.",[OFCap]*907184.74,IIf([OFCapUOM]="mg",[OFCap]*0.001,IIf([OFCapUOM]="g",[OFCap]*1,IIf([OFCapUOM]="kg",[OFCap]*1000,IIf([OFCapUOM]="MT",[OFCap]*1000000)))))))))))))))))))))))
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please Help! The Expression you entered is too complex 0

Top