help for the syntax challenged

[jlute]

YIKES! Trying to simplify the "Can't be calculated." part of this
mess:

IDCubecuin: IIf([IDLUOM]="ga.","Can't be
calculated.",IIf([IDLUOM]="Micron","Can't be
calculated.",IIf([IDLUOM]="Mil","Can't be
calculated.",IIf([IDLUOM]="Other (Specify)","Can't be
calculated.",IIf([IDLUOM]="in.",
[IDLength]*[IDWidth]*[IDHeight],IIf([IDLUOM]="ft.",
[IDLength]*[IDWidth]*[IDHeight]*1728,IIf([IDLUOM]="yd.",
[IDLength]*[IDWidth]*[IDHeight]*46656,IIf([IDLUOM]="mm",
[IDLength]*[IDWidth]*[IDHeight]*0.000061023744095,IIf([IDLUOM]="cm",
[IDLength]*[IDWidth]*[IDHeight]*0.061023744,IIf([IDLUOM]="dm",
[IDLength]*[IDWidth]*[IDHeight]*61.0237441,IIf([IDLUOM]="m",
[IDLength]*[IDWidth]*[IDHeight]*61023.7441)))))))))))

I tried this:

IDCubecuin: IIf([IDLUOM]="ga." Or "Micron" Or "Mil" Or "Other
(Specify)","Can't be calculated.",IIf([IDLUOM]="in.",
[IDLength]*[IDWidth]*[IDHeight],IIf([IDLUOM]="ft.",
[IDLength]*[IDWidth]*[IDHeight]*1728,IIf([IDLUOM]="yd.",
[IDLength]*[IDWidth]*[IDHeight]*46656,IIf([IDLUOM]="mm",
[IDLength]*[IDWidth]*[IDHeight]*0.000061023744095,IIf([IDLUOM]="cm",
[IDLength]*[IDWidth]*[IDHeight]*0.061023744,IIf([IDLUOM]="dm",
[IDLength]*[IDWidth]*[IDHeight]*61.0237441,IIf([IDLUOM]="m",
[IDLength]*[IDWidth]*[IDHeight]*61023.7441)))))))))))

But it returns "Can't be calculated." for every record.

Any way to simplify?

THANKS!

 

[John Spencer]

IDCubeCuIn: IIF(IDLOUM IN ("ga.","Micron","Mil","Other (Specify)")
,"Can't be Calculated"
, IIF(IDLOUM = "In.",[IDLength]*[IDWidth]*[IDHeight]
, IIf([IDLUOM]="ft.",[IDLength]*[IDWidth]*[IDHeight]*1728))

Expand the above to include all your other options

Although I would simplify my life, by adding a table with the units of measure
and the conversion factor and join IDLOUM to the table and grab the conversion
factor. This has the advantage of allowing you to add additional units (if
needed) and adjusting the conversion factor easily (if needed). Of course,
you may already have the needed table containing the units of measure and just
need to add the conversion factor field to it.

MeasurementConversion (table)
UnitType (text field)
ConversionFactor (number field Type: Double)

Leave conversion null for those UnitType that can't be calculated
Then with that table added to your query and with UnitType joined to the
IDLOUM field your expression becomes

IDCubeIn: IIF([ConversionFactor] is null,"Can't be Calculated",
ConversionFactor * [IDLength]*[IDWidth]*[IDHeight])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[jlute]

Thanks, John!

I actually do have the table and conversion factors! Works like a
champ with reports however adding it to the Record Source of my
subform results in an un-updatable subform. I actually posted about
this yesterday in a thread titled "Lookup".

I need to keep the ball rolling so I opted for this more "clumsy"
solution.

IDCubeCuIn: IIF(IDLOUM IN ("ga.","Micron","Mil","Other (Specify)")
,"Can't be Calculated"
, IIF(IDLOUM = "In.",[IDLength]*[IDWidth]*[IDHeight]
, IIf([IDLUOM]="ft.",[IDLength]*[IDWidth]*[IDHeight]*1728))

Expand the above to include all your other options

Although I would simplify my life, by adding a table with the units of measure
and the conversion factor and join IDLOUM to the table and grab the conversion
factor.  This has the advantage of allowing you to add additional units(if
needed) and adjusting the conversion factor easily (if needed).  Of course,
you may already have the needed table containing the units of measure andjust
need to add the conversion factor field to it.

MeasurementConversion (table)
UnitType (text field)
ConversionFactor (number field Type: Double)

Leave conversion null for those UnitType that can't be calculated
Then with that table added to your query and with UnitType joined to the
IDLOUM field your expression becomes

IDCubeIn: IIF([ConversionFactor] is null,"Can't be Calculated",
ConversionFactor * [IDLength]*[IDWidth]*[IDHeight])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

YIKES! Trying to simplify the "Can't be calculated." part of this
mess:

IDCubecuin: IIf([IDLUOM]="ga.","Can't be
calculated.",IIf([IDLUOM]="Micron","Can't be
calculated.",IIf([IDLUOM]="Mil","Can't be
calculated.",IIf([IDLUOM]="Other (Specify)","Can't be
calculated.",IIf([IDLUOM]="in.",
[IDLength]*[IDWidth]*[IDHeight],IIf([IDLUOM]="ft.",
[IDLength]*[IDWidth]*[IDHeight]*1728,IIf([IDLUOM]="yd.",
[IDLength]*[IDWidth]*[IDHeight]*46656,IIf([IDLUOM]="mm",
[IDLength]*[IDWidth]*[IDHeight]*0.000061023744095,IIf([IDLUOM]="cm",
[IDLength]*[IDWidth]*[IDHeight]*0.061023744,IIf([IDLUOM]="dm",
[IDLength]*[IDWidth]*[IDHeight]*61.0237441,IIf([IDLUOM]="m",
[IDLength]*[IDWidth]*[IDHeight]*61023.7441)))))))))))

I tried this:

IDCubecuin: IIf([IDLUOM]="ga." Or "Micron" Or "Mil" Or "Other
(Specify)","Can't be calculated.",IIf([IDLUOM]="in.",
[IDLength]*[IDWidth]*[IDHeight],IIf([IDLUOM]="ft.",
[IDLength]*[IDWidth]*[IDHeight]*1728,IIf([IDLUOM]="yd.",
[IDLength]*[IDWidth]*[IDHeight]*46656,IIf([IDLUOM]="mm",
[IDLength]*[IDWidth]*[IDHeight]*0.000061023744095,IIf([IDLUOM]="cm",
[IDLength]*[IDWidth]*[IDHeight]*0.061023744,IIf([IDLUOM]="dm",
[IDLength]*[IDWidth]*[IDHeight]*61.0237441,IIf([IDLUOM]="m",
[IDLength]*[IDWidth]*[IDHeight]*61023.7441)))))))))))

But it returns "Can't be calculated." for every record.

Any way to simplify?

THANKS!- Hide quoted text -

- Show quoted text -

 

[John Spencer]

How about using an expression like the following (which may be slower than
your current solution).

IdCubeCuIn:
IIF(DLookup("ConversionFactor","Conversiontable","UnitType='" & IDLOUM
&"'")=Null,"Can't Be Calculated",
DLookup("ConversionFactor","Conversiontable","UnitType='" & IDLOUM
&"'")*[IDLength]*[IDWidth]*[IDHeight]))

Or even using the format property of the control in the subform to show "Can't
be calculated" for null values.

On the control displaying this calculation, you might be able to use:

Control Source: = DLookup("ConversionFactor","Conversiontable","UnitType='" &
IDLOUM &"'")*[IDLength]*[IDWidth]*[IDHeight])

Format: 0;0;0;"Can't be calculated"


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks, John!

I actually do have the table and conversion factors! Works like a
champ with reports however adding it to the Record Source of my
subform results in an un-updatable subform. I actually posted about
this yesterday in a thread titled "Lookup".

I need to keep the ball rolling so I opted for this more "clumsy"
solution.

IDCubeCuIn: IIF(IDLOUM IN ("ga.","Micron","Mil","Other (Specify)")
,"Can't be Calculated"
, IIF(IDLOUM = "In.",[IDLength]*[IDWidth]*[IDHeight]
, IIf([IDLUOM]="ft.",[IDLength]*[IDWidth]*[IDHeight]*1728))

Expand the above to include all your other options

Although I would simplify my life, by adding a table with the units of measure
and the conversion factor and join IDLOUM to the table and grab the conversion
factor. This has the advantage of allowing you to add additional units (if
needed) and adjusting the conversion factor easily (if needed). Of course,
you may already have the needed table containing the units of measure and just
need to add the conversion factor field to it.

MeasurementConversion (table)
UnitType (text field)
ConversionFactor (number field Type: Double)

Leave conversion null for those UnitType that can't be calculated
Then with that table added to your query and with UnitType joined to the
IDLOUM field your expression becomes

IDCubeIn: IIF([ConversionFactor] is null,"Can't be Calculated",
ConversionFactor * [IDLength]*[IDWidth]*[IDHeight])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

YIKES! Trying to simplify the "Can't be calculated." part of this
mess:
IDCubecuin: IIf([IDLUOM]="ga.","Can't be
calculated.",IIf([IDLUOM]="Micron","Can't be
calculated.",IIf([IDLUOM]="Mil","Can't be
calculated.",IIf([IDLUOM]="Other (Specify)","Can't be
calculated.",IIf([IDLUOM]="in.",
[IDLength]*[IDWidth]*[IDHeight],IIf([IDLUOM]="ft.",
[IDLength]*[IDWidth]*[IDHeight]*1728,IIf([IDLUOM]="yd.",
[IDLength]*[IDWidth]*[IDHeight]*46656,IIf([IDLUOM]="mm",
[IDLength]*[IDWidth]*[IDHeight]*0.000061023744095,IIf([IDLUOM]="cm",
[IDLength]*[IDWidth]*[IDHeight]*0.061023744,IIf([IDLUOM]="dm",
[IDLength]*[IDWidth]*[IDHeight]*61.0237441,IIf([IDLUOM]="m",
[IDLength]*[IDWidth]*[IDHeight]*61023.7441)))))))))))
I tried this:
IDCubecuin: IIf([IDLUOM]="ga." Or "Micron" Or "Mil" Or "Other
(Specify)","Can't be calculated.",IIf([IDLUOM]="in.",
[IDLength]*[IDWidth]*[IDHeight],IIf([IDLUOM]="ft.",
[IDLength]*[IDWidth]*[IDHeight]*1728,IIf([IDLUOM]="yd.",
[IDLength]*[IDWidth]*[IDHeight]*46656,IIf([IDLUOM]="mm",
[IDLength]*[IDWidth]*[IDHeight]*0.000061023744095,IIf([IDLUOM]="cm",
[IDLength]*[IDWidth]*[IDHeight]*0.061023744,IIf([IDLUOM]="dm",
[IDLength]*[IDWidth]*[IDHeight]*61.0237441,IIf([IDLUOM]="m",
[IDLength]*[IDWidth]*[IDHeight]*61023.7441)))))))))))
But it returns "Can't be calculated." for every record.
Any way to simplify?
THANKS!- Hide quoted text -

- Show quoted text -

 

[jlute]

Wow! Thanks, John!

I gave this a try:
IdCubeCuIn: IIf(DLookUp("cuinConvFactor","tblUOMLength","UnitType='" &
[IDCubeUOM] & "'")=Null,"Can't
calculate",DLookUp("cuinConvFactor","tblUOMLength","UnitType='" &
[IDCubeUOM] & "'")*[IDLength]*[IDWidth]*[IDHeight])

This returns #Error. Not sure if I added the table name and conversion
facotr fields properly...? I'll keep tinkering...

How about using an expression like the following (which may be slower than
your current solution).

IdCubeCuIn:
IIF(DLookup("ConversionFactor","Conversiontable","UnitType='" & IDLOUM
&"'")=Null,"Can't Be Calculated",
DLookup("ConversionFactor","Conversiontable","UnitType='" & IDLOUM
&"'")*[IDLength]*[IDWidth]*[IDHeight]))

Or even using the format property of the control in the subform to show "Can't
be calculated" for null values.

On the control displaying this calculation, you might be able to use:

Control Source: = DLookup("ConversionFactor","Conversiontable","UnitType='" &
IDLOUM &"'")*[IDLength]*[IDWidth]*[IDHeight])

Format: 0;0;0;"Can't be calculated"

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks, John!

I actually do have the table and conversion factors! Works like a
champ with reports however adding it to the Record Source of my
subform results in an un-updatable subform. I actually posted about
this yesterday in a thread titled "Lookup".

I need to keep the ball rolling so I opted for this more "clumsy"
solution.

IDCubeCuIn: IIF(IDLOUM IN ("ga.","Micron","Mil","Other (Specify)")
,"Can't be Calculated"
, IIF(IDLOUM = "In.",[IDLength]*[IDWidth]*[IDHeight]
, IIf([IDLUOM]="ft.",[IDLength]*[IDWidth]*[IDHeight]*1728))
Expand the above to include all your other options
Although I would simplify my life, by adding a table with the units ofmeasure
and the conversion factor and join IDLOUM to the table and grab the conversion
factor.  This has the advantage of allowing you to add additional units (if
needed) and adjusting the conversion factor easily (if needed).  Of course,
you may already have the needed table containing the units of measure and just
need to add the conversion factor field to it.
MeasurementConversion (table)
UnitType (text field)
ConversionFactor (number field Type: Double)
Leave conversion null for those UnitType that can't be calculated
Then with that table added to your query and with UnitType joined to the
IDLOUM field your expression becomes
IDCubeIn: IIF([ConversionFactor] is null,"Can't be Calculated",
ConversionFactor * [IDLength]*[IDWidth]*[IDHeight])
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
(e-mail address removed) wrote:
YIKES! Trying to simplify the "Can't be calculated." part of this
mess:
IDCubecuin: IIf([IDLUOM]="ga.","Can't be
calculated.",IIf([IDLUOM]="Micron","Can't be
calculated.",IIf([IDLUOM]="Mil","Can't be
calculated.",IIf([IDLUOM]="Other (Specify)","Can't be
calculated.",IIf([IDLUOM]="in.",
[IDLength]*[IDWidth]*[IDHeight],IIf([IDLUOM]="ft.",
[IDLength]*[IDWidth]*[IDHeight]*1728,IIf([IDLUOM]="yd.",
[IDLength]*[IDWidth]*[IDHeight]*46656,IIf([IDLUOM]="mm",
[IDLength]*[IDWidth]*[IDHeight]*0.000061023744095,IIf([IDLUOM]="cm",
[IDLength]*[IDWidth]*[IDHeight]*0.061023744,IIf([IDLUOM]="dm",
[IDLength]*[IDWidth]*[IDHeight]*61.0237441,IIf([IDLUOM]="m",
[IDLength]*[IDWidth]*[IDHeight]*61023.7441)))))))))))
I tried this:
IDCubecuin: IIf([IDLUOM]="ga." Or "Micron" Or "Mil" Or "Other
(Specify)","Can't be calculated.",IIf([IDLUOM]="in.",
[IDLength]*[IDWidth]*[IDHeight],IIf([IDLUOM]="ft.",
[IDLength]*[IDWidth]*[IDHeight]*1728,IIf([IDLUOM]="yd.",
[IDLength]*[IDWidth]*[IDHeight]*46656,IIf([IDLUOM]="mm",
[IDLength]*[IDWidth]*[IDHeight]*0.000061023744095,IIf([IDLUOM]="cm",
[IDLength]*[IDWidth]*[IDHeight]*0.061023744,IIf([IDLUOM]="dm",
[IDLength]*[IDWidth]*[IDHeight]*61.0237441,IIf([IDLUOM]="m",
[IDLength]*[IDWidth]*[IDHeight]*61023.7441)))))))))))
But it returns "Can't be calculated." for every record.
Any way to simplify?
THANKS!- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[John Spencer]

My error: the conditional clause should read IS NULL not = Null

IdCubeCuIn: IIf(DLookUp("cuinConvFactor","tblUOMLength","UnitType='" &
[IDCubeUOM] & "'") IS Null,"Can't calculate",
DLookUp("cuinConvFactor","tblUOMLength", "UnitType='" & [IDCubeUOM] &
"'")*[IDLength]*[IDWidth]*[IDHeight])

However that should not return error, so there must be something else
going on in the expresson. For testing purposes, you might try to break
this down.

See if the following expression works.

IIf(DLookUp("cuinConvFactor","tblUOMLength","UnitType='" &
[IDCubeUOM] & "'") IS Null,"No Match","Match")

If so, then try adding the other bits. If not, then check field names,
and tablenames.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Wow! Thanks, John!

I gave this a try:
IdCubeCuIn: IIf(DLookUp("cuinConvFactor","tblUOMLength","UnitType='" &
[IDCubeUOM] & "'")=Null,"Can't
calculate",DLookUp("cuinConvFactor","tblUOMLength","UnitType='" &
[IDCubeUOM] & "'")*[IDLength]*[IDWidth]*[IDHeight])

This returns #Error. Not sure if I added the table name and conversion
facotr fields properly...? I'll keep tinkering...

How about using an expression like the following (which may be slower than
your current solution).

IdCubeCuIn:
IIF(DLookup("ConversionFactor","Conversiontable","UnitType='" & IDLOUM
&"'")=Null,"Can't Be Calculated",
DLookup("ConversionFactor","Conversiontable","UnitType='" & IDLOUM
&"'")*[IDLength]*[IDWidth]*[IDHeight]))

 

[jlute]

Ack! I didn't see the Is Null, either.

Well, I tried both again and still #Error. I looked really hard at
everything and there are no table/field name goofs.

I just noticed something. I'm following the syntax logic up until
"UnitType". What exactly is this? A fieldname assumption...?

My error: the conditional clause should read IS NULL not = Null

IdCubeCuIn: IIf(DLookUp("cuinConvFactor","tblUOMLength","UnitType='" &
[IDCubeUOM] & "'") IS Null,"Can't calculate",
DLookUp("cuinConvFactor","tblUOMLength", "UnitType='" & [IDCubeUOM] &
"'")*[IDLength]*[IDWidth]*[IDHeight])

However that should not return error, so there must be something else
going on in the expresson.  For testing purposes, you might try to break
this down.

See if the following expression works.

IIf(DLookUp("cuinConvFactor","tblUOMLength","UnitType='" &
[IDCubeUOM] & "'") IS Null,"No Match","Match")

If so, then try adding the other bits.  If not, then check field names,
and tablenames.

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2008
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================

Wow! Thanks, John!

I gave this a try:
IdCubeCuIn: IIf(DLookUp("cuinConvFactor","tblUOMLength","UnitType='" &
[IDCubeUOM] & "'")=Null,"Can't
calculate",DLookUp("cuinConvFactor","tblUOMLength","UnitType='" &
[IDCubeUOM] & "'")*[IDLength]*[IDWidth]*[IDHeight])

This returns #Error. Not sure if I added the table name and conversion
facotr fields properly...? I'll keep tinkering...

How about using an expression like the following (which may be slower than
your current solution).
IdCubeCuIn:
IIF(DLookup("ConversionFactor","Conversiontable","UnitType='" & IDLOUM
&"'")=Null,"Can't Be Calculated",
DLookup("ConversionFactor","Conversiontable","UnitType='" & IDLOUM
&"'")*[IDLength]*[IDWidth]*[IDHeight]))- Hide quoted text -

- Show quoted text -

 

[John Spencer]

UnitType would be the name of the field in the table tblUOMLength that
contains the type of unit you are converting to - In, Ft, MM, Yd, etc.

You must have at least two fields in the table
One specifying the UnitType (in, ft, yd, etc)
and a second one specifying the conversion factor (1, 1728, 46656, etc)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Ack! I didn't see the Is Null, either.

Well, I tried both again and still #Error. I looked really hard at
everything and there are no table/field name goofs.

I just noticed something. I'm following the syntax logic up until
"UnitType". What exactly is this? A fieldname assumption...?

My error: the conditional clause should read IS NULL not = Null

IdCubeCuIn: IIf(DLookUp("cuinConvFactor","tblUOMLength","UnitType='" &
[IDCubeUOM] & "'") IS Null,"Can't calculate",
DLookUp("cuinConvFactor","tblUOMLength", "UnitType='" & [IDCubeUOM] &
"'")*[IDLength]*[IDWidth]*[IDHeight])

However that should not return error, so there must be something else
going on in the expresson. For testing purposes, you might try to break
this down.

See if the following expression works.

IIf(DLookUp("cuinConvFactor","tblUOMLength","UnitType='" &
[IDCubeUOM] & "'") IS Null,"No Match","Match")

If so, then try adding the other bits. If not, then check field names,
and tablenames.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Wow! Thanks, John!
I gave this a try:
IdCubeCuIn: IIf(DLookUp("cuinConvFactor","tblUOMLength","UnitType='" &
[IDCubeUOM] & "'")=Null,"Can't
calculate",DLookUp("cuinConvFactor","tblUOMLength","UnitType='" &
[IDCubeUOM] & "'")*[IDLength]*[IDWidth]*[IDHeight])
This returns #Error. Not sure if I added the table name and conversion
facotr fields properly...? I'll keep tinkering...
How about using an expression like the following (which may be slower than
your current solution).
IdCubeCuIn:
IIF(DLookup("ConversionFactor","Conversiontable","UnitType='" & IDLOUM
&"'")=Null,"Can't Be Calculated",
DLookup("ConversionFactor","Conversiontable","UnitType='" & IDLOUM
&"'")*[IDLength]*[IDWidth]*[IDHeight]))- Hide quoted text -

- Show quoted text -

 

[JohnLute]

Well, I'm feeling a bit dense. I made the appropriate correction and guess
what? Yup. It works! Sorry for the confusion.

Well, it seems NOT to slow things down. In fact, it seems the same speed if
not faster. Now for the twist. In my application I'm recording 3-dimensional
objects that may have L x W x H or may NOT have a Length but rather a
Diameter x H. To simplify I've created LxWxH fields and a combobox/field used
to "label" Width as either "ID Width" or "ID Diameter". I've placed
validation codes in the form so that when ID Diameter is chosen certain
events trigger to assure that there will be no ID Length and vice-versa. An
"ID Diameter" requires multiplying Width with itself and then with Height in
order to arrive at a cube. Therefore:

IDCubecuin: IIf(DLookUp("cuinConvFactor","tblUOMLength","txtUOMLength='" &
[IDCubeUOM] & "'") Is Null,"Can't calculate.",IIf([IDWDia]="ID
Diameter",Round(DLookUp("cuinConvFactor","tblUOMLength","txtUOMLength='" &
[IDCubeUOM] & "'")*[IDWidth]*[IDWidth]*[IDHeight],4),IIf([IDWDia]="ID
Width",Round(DLookUp("cuinConvFactor","tblUOMLength","txtUOMLength='" &
[IDCubeUOM] & "'")*[IDLength]*[IDWidth]*[IDHeight],4))))

This approach is infinitely better than my previous design in which I
created two fields: IDCubecuinL (calculated cube given an "ID Width") and
IDCubecuinW (calculated cube given an "ID Diameter"). I've pasted IDCubecuinL
below - IDCubecuinW was obviously similar.

IDCubecuinL: IIf([IDWDia]="ID Width",IIf([IDLUOM] In
("ga.","Micron","Mil","Other (Specify)"),"Can't
calculate.",IIf([IDLUOM]="in.",[IDLength]*[IDWidth]*[IDHeight],IIf([IDLUOM]="ft.",[IDLength]*[IDWidth]*[IDHeight]*1728,IIf([IDLUOM]="yd.",[IDLength]*[IDWidth]*[IDHeight]*46656,IIf([IDLUOM]="mm",[IDLength]*[IDWidth]*[IDHeight]*0.000061023744095,IIf([IDLUOM]="cm",[IDLength]*[IDWidth]*[IDHeight]*0.061023744,IIf([IDLUOM]="dm",[IDLength]*[IDWidth]*[IDHeight]*61.0237441,IIf([IDLUOM]="m",[IDLength]*[IDWidth]*[IDHeight]*61023.7441)))))))))

Cube was displayed via:
IDCubecuin: IIf([IDCubecuinL]="Can't
calculate.",[IDCubecuinL],IIf([IDCubecuinW]="Can't
calculate.",[IDCubecuinW],Nz(Round([IDCubecuinL],4),Nz(Round([IDCubecuinW],4)))))

With your help I've shaved this down considerably! Now my form is much
"cleaner" and more logical. I hated the idea of having those conversion
factors hard coded in there!

Thanks, John! You've made my week!

--
www.Marzetti.com

UnitType would be the name of the field in the table tblUOMLength that
contains the type of unit you are converting to - In, Ft, MM, Yd, etc.

You must have at least two fields in the table
One specifying the UnitType (in, ft, yd, etc)
and a second one specifying the conversion factor (1, 1728, 46656, etc)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Ack! I didn't see the Is Null, either.

Well, I tried both again and still #Error. I looked really hard at
everything and there are no table/field name goofs.

I just noticed something. I'm following the syntax logic up until
"UnitType". What exactly is this? A fieldname assumption...?

My error: the conditional clause should read IS NULL not = Null

IdCubeCuIn: IIf(DLookUp("cuinConvFactor","tblUOMLength","UnitType='" &
[IDCubeUOM] & "'") IS Null,"Can't calculate",
DLookUp("cuinConvFactor","tblUOMLength", "UnitType='" & [IDCubeUOM] &
"'")*[IDLength]*[IDWidth]*[IDHeight])

However that should not return error, so there must be something else
going on in the expresson. For testing purposes, you might try to break
this down.

See if the following expression works.

IIf(DLookUp("cuinConvFactor","tblUOMLength","UnitType='" &
[IDCubeUOM] & "'") IS Null,"No Match","Match")

If so, then try adding the other bits. If not, then check field names,
and tablenames.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================



(e-mail address removed) wrote:
Wow! Thanks, John!
I gave this a try:
IdCubeCuIn: IIf(DLookUp("cuinConvFactor","tblUOMLength","UnitType='" &
[IDCubeUOM] & "'")=Null,"Can't
calculate",DLookUp("cuinConvFactor","tblUOMLength","UnitType='" &
[IDCubeUOM] & "'")*[IDLength]*[IDWidth]*[IDHeight])
This returns #Error. Not sure if I added the table name and conversion
facotr fields properly...? I'll keep tinkering...
How about using an expression like the following (which may be slower than
your current solution).
IdCubeCuIn:
IIF(DLookup("ConversionFactor","Conversiontable","UnitType='" & IDLOUM
&"'")=Null,"Can't Be Calculated",
DLookup("ConversionFactor","Conversiontable","UnitType='" & IDLOUM
&"'")*[IDLength]*[IDWidth]*[IDHeight]))- Hide quoted text -
- Show quoted text -