Code Needed

[Guest]

I am created a database to calculate water usage in our plant. Up to the date I am currently entering, water usage was calculated based on a set formula. But now, the plant has a metering system in place. This means that exact amounts can be set and calculated. Therefore if properly recorded, I can use the meter setting instead of the assumption of formula usage.

WHen creating queries to summarize the data, how do I get the database calculate usage based on the entered value instead of the formula value, but if there is no entered value, calculate usage using the pre-entered formulas.

 

[Ken Snell]

Use a calculated field to give you the usage. Something like this:

Usg: IIf(Len([Usage] & "") > 0, [Usage], 12000 * MyWaterFormulaFunction())


--

Ken Snell
<MS ACCESS MVP>

I am created a database to calculate water usage in our plant. Up to the

date I am currently entering, water usage was calculated based on a set
formula. But now, the plant has a metering system in place. This means
that exact amounts can be set and calculated. Therefore if properly
recorded, I can use the meter setting instead of the assumption of formula
usage.

WHen creating queries to summarize the data, how do I get the database

calculate usage based on the entered value instead of the formula value, but
if there is no entered value, calculate usage using the pre-entered
formulas.

 

[Guest]

Wat is Len?, is the two I's in If a typo? I think I understand the remainder of this formula. If there is no entry it is not "", it is 0, do I put (& 0)?

Thank you

Use a calculated field to give you the usage. Something like this:

Usg: IIf(Len([Usage] & "") > 0, [Usage], 12000 * MyWaterFormulaFunction())


--

Ken Snell
<MS ACCESS MVP>

I am created a database to calculate water usage in our plant. Up to the

date I am currently entering, water usage was calculated based on a set
formula. But now, the plant has a metering system in place. This means
that exact amounts can be set and calculated. Therefore if properly
recorded, I can use the meter setting instead of the assumption of formula
usage.

WHen creating queries to summarize the data, how do I get the database

calculate usage based on the entered value instead of the formula value, but
if there is no entered value, calculate usage using the pre-entered
formulas.

 

[John Spencer (MVP)]

Pardon me.

Len is a VBA function that returns the LENgth of a string. So, Len("abc") will
return 3.

IIF is an operator(?) and it stands for Immediate IF.

Wat is Len?, is the two I's in If a typo? I think I understand the remainder of this formula. If there is no entry it is not "", it is 0, do I put (& 0)?

Thank you

Use a calculated field to give you the usage. Something like this:

Usg: IIf(Len([Usage] & "") > 0, [Usage], 12000 * MyWaterFormulaFunction())


--

Ken Snell
<MS ACCESS MVP>

I am created a database to calculate water usage in our plant. Up to the

date I am currently entering, water usage was calculated based on a set
formula. But now, the plant has a metering system in place. This means
that exact amounts can be set and calculated. Therefore if properly
recorded, I can use the meter setting instead of the assumption of formula
usage.

WHen creating queries to summarize the data, how do I get the database

calculate usage based on the entered value instead of the formula value, but
if there is no entered value, calculate usage using the pre-entered
formulas.

 

[Ken Snell]

John is correct (of course! ;-) ) on both counts.

 

[Ken Snell]

The expression that I typed should be used just as is....do not add the & 0.

--

Ken Snell
<MS ACCESS MVP>

Wat is Len?, is the two I's in If a typo? I think I understand the

remainder of this formula. If there is no entry it is not "", it is 0, do I
put (& 0)?

Thank you

Use a calculated field to give you the usage. Something like this:

Usg: IIf(Len([Usage] & "") > 0, [Usage], 12000 * MyWaterFormulaFunction())


--

Ken Snell
<MS ACCESS MVP>

I am created a database to calculate water usage in our plant. Up to

the
date I am currently entering, water usage was calculated based on a set
formula. But now, the plant has a metering system in place. This means
that exact amounts can be set and calculated. Therefore if properly
recorded, I can use the meter setting instead of the assumption of formula
usage.

WHen creating queries to summarize the data, how do I get the database

calculate usage based on the entered value instead of the formula value, but
if there is no entered value, calculate usage using the pre-entered
formulas.