Unpredictable results in calculations

[Tom Ventouris]

I have an unbound form with 9 calculated fields which display business
indicators. Some calculations are DSum from tables in the DB while others
are calculated from these (DSUM) values on the form.

The form can be opened from a Menu and from command buttons on two menu
forms.

When the form is opened from one of the menu forms, no problem. The problem
is that when I open the form from the Menu or the other menu from, one field
displays #Error. This particular field is calculated from two other fields
on the same form. (No DSUM on this one).

I placed a command button to requery the "#Error" field and it works, but
only after two or three clicks.
I thought this might be caused by the Tab Order of the fields and re-ordered
these in the order that they are required for the calculations - this did
not fix the problem.
The only other objects open are the unbound menu forms.

Any suggestions for this "strange but true" issue?

 

[Allen Browne]

Several things could be happening.

1. Data Types
==========
Sometimes Access is unable to figure out the intended data types. If this
text box has a Control Source of:
=[Text0] + [Text1]
where Text0 and Text1 contain numeric calculations, set the Format property
of all 3 text boxes to:
General Number
so Access knows their data type.

In some cases, you may need to typecast the expression too. Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Incorrect delimiters can also be a factor here.

2. Timing/dependencies
=================
If Form1 is bound to a query, and the query contains something (calculated
field, criteria, ...) that reads a value from Form1, you have a
chicken'n'egg situation. The query can't decide on the values to return to
the form until the form is loaded, and the form can't load until it gets the
records from the query.

There are many other dependencies also, between main form and subform,
between one form and another, or even circular dependencies between controls
on the one form.

(The Tab Order of the controls is generally not a factor in these
dependencies.)

3. Expressions that are conditionally invalid
===============================
Some expressions are valid in some cases, but not others. For example, if
you use:
=DSum("Amount", "Table1", "CategoryID = " & [CategoryID])
that works fine if there is a value in the CategoryID field. However, when
CategoryID is null, the 3rd argument resolves to just:
CategoryID =
which is clearly invalid. To avoid, this, use Nz() to supply a value for
null, e.g.:
=DSum("Amount", "Table1", "CategoryID = " & Nz([CategoryID],0))

4. Problem elsewhere
===============
Once one calculated control fails, Access gives up and doesn't try to
calculate the others. So the ones it did earlier show a result, while all
the ones it did not get you yet fail with #Error.

It is therefore possible to see #Error in a control that has no problem at
all. The problem may be with a completely different control, even one that
is not visible (so you are likely to forget it could be a factor.)

5. Bad name
=========
If the name of a field or control is a reserved word or something ambiguous,
it can work in some circumstances and fail in others. Here's a list of names
to check:
http://allenbrowne.com/AppIssueBadWord.html

As well as that list, if the control has the same name as a field, but is
bound to something else, Access gets confused.

 

[Tom Ventouris]

Thanks Allen
This is a comprehensive response, thank you.
It's running off the printer now. Some time to check this and I will post
back with the solution.

Several things could be happening.

1. Data Types
==========
Sometimes Access is unable to figure out the intended data types. If this
text box has a Control Source of:
=[Text0] + [Text1]
where Text0 and Text1 contain numeric calculations, set the Format
property of all 3 text boxes to:
General Number
so Access knows their data type.

In some cases, you may need to typecast the expression too. Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Incorrect delimiters can also be a factor here.

2. Timing/dependencies
=================
If Form1 is bound to a query, and the query contains something (calculated
field, criteria, ...) that reads a value from Form1, you have a
chicken'n'egg situation. The query can't decide on the values to return to
the form until the form is loaded, and the form can't load until it gets
the records from the query.

There are many other dependencies also, between main form and subform,
between one form and another, or even circular dependencies between
controls on the one form.

(The Tab Order of the controls is generally not a factor in these
dependencies.)

3. Expressions that are conditionally invalid
===============================
Some expressions are valid in some cases, but not others. For example, if
you use:
=DSum("Amount", "Table1", "CategoryID = " & [CategoryID])
that works fine if there is a value in the CategoryID field. However, when
CategoryID is null, the 3rd argument resolves to just:
CategoryID =
which is clearly invalid. To avoid, this, use Nz() to supply a value for
null, e.g.:
=DSum("Amount", "Table1", "CategoryID = " & Nz([CategoryID],0))

4. Problem elsewhere
===============
Once one calculated control fails, Access gives up and doesn't try to
calculate the others. So the ones it did earlier show a result, while all
the ones it did not get you yet fail with #Error.

It is therefore possible to see #Error in a control that has no problem at
all. The problem may be with a completely different control, even one that
is not visible (so you are likely to forget it could be a factor.)

5. Bad name
=========
If the name of a field or control is a reserved word or something
ambiguous, it can work in some circumstances and fail in others. Here's a
list of names to check:
http://allenbrowne.com/AppIssueBadWord.html

As well as that list, if the control has the same name as a field, but is
bound to something else, Access gets confused.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an unbound form with 9 calculated fields which display business
indicators. Some calculations are DSum from tables in the DB while others
are calculated from these (DSUM) values on the form.

The form can be opened from a Menu and from command buttons on two menu
forms.

When the form is opened from one of the menu forms, no problem. The
problem is that when I open the form from the Menu or the other menu
from, one field displays #Error. This particular field is calculated from
two other fields on the same form. (No DSUM on this one).

I placed a command button to requery the "#Error" field and it works, but
only after two or three clicks.
I thought this might be caused by the Tab Order of the fields and
re-ordered these in the order that they are required for the
calculations - this did not fix the problem.
The only other objects open are the unbound menu forms.

Any suggestions for this "strange but true" issue?

 

[Tom Ventouris]

I Have checked your suggestions..

1. Data Types: All set to currency. Changed to General Number but no effect.

2. Timing/Dependencies - Some fields are based on queries, herein maybe the
problem, which appears to be solved by requering the fields.
Perhaps also indicated by the inconsistency of the error.

3. Expressions that may be conditionally invalid - Only one expression used,
Table and Field names change, but the rest is all the same. Example:
=DSum("[txtTotal]","[tblExpenses]","[txtExpenseDate] Between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")
No Null values in the dates or other fields.

4. Problem Elsewhere: Always a possibility, but it has not manifested itself
anywhere else.

5. Bad Name: I have the March 2007 release of your Issue Checker which I
use regularly, thank you.

Is there any way to check the timing? I could just live with requering the
form when errors appear.

Several things could be happening.

1. Data Types
==========
Sometimes Access is unable to figure out the intended data types. If this
text box has a Control Source of:
=[Text0] + [Text1]
where Text0 and Text1 contain numeric calculations, set the Format
property of all 3 text boxes to:
General Number
so Access knows their data type.

In some cases, you may need to typecast the expression too. Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Incorrect delimiters can also be a factor here.

2. Timing/dependencies
=================
If Form1 is bound to a query, and the query contains something (calculated
field, criteria, ...) that reads a value from Form1, you have a
chicken'n'egg situation. The query can't decide on the values to return to
the form until the form is loaded, and the form can't load until it gets
the records from the query.

There are many other dependencies also, between main form and subform,
between one form and another, or even circular dependencies between
controls on the one form.

(The Tab Order of the controls is generally not a factor in these
dependencies.)

3. Expressions that are conditionally invalid
===============================
Some expressions are valid in some cases, but not others. For example, if
you use:
=DSum("Amount", "Table1", "CategoryID = " & [CategoryID])
that works fine if there is a value in the CategoryID field. However, when
CategoryID is null, the 3rd argument resolves to just:
CategoryID =
which is clearly invalid. To avoid, this, use Nz() to supply a value for
null, e.g.:
=DSum("Amount", "Table1", "CategoryID = " & Nz([CategoryID],0))

4. Problem elsewhere
===============
Once one calculated control fails, Access gives up and doesn't try to
calculate the others. So the ones it did earlier show a result, while all
the ones it did not get you yet fail with #Error.

It is therefore possible to see #Error in a control that has no problem at
all. The problem may be with a completely different control, even one that
is not visible (so you are likely to forget it could be a factor.)

5. Bad name
=========
If the name of a field or control is a reserved word or something
ambiguous, it can work in some circumstances and fail in others. Here's a
list of names to check:
http://allenbrowne.com/AppIssueBadWord.html

As well as that list, if the control has the same name as a field, but is
bound to something else, Access gets confused.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an unbound form with 9 calculated fields which display business
indicators. Some calculations are DSum from tables in the DB while others
are calculated from these (DSUM) values on the form.

The form can be opened from a Menu and from command buttons on two menu
forms.

When the form is opened from one of the menu forms, no problem. The
problem is that when I open the form from the Menu or the other menu
from, one field displays #Error. This particular field is calculated from
two other fields on the same form. (No DSUM on this one).

I placed a command button to requery the "#Error" field and it works, but
only after two or three clicks.
I thought this might be caused by the Tab Order of the fields and
re-ordered these in the order that they are required for the
calculations - this did not fix the problem.
The only other objects open are the unbound menu forms.

Any suggestions for this "strange but true" issue?

 

[Allen Browne]

Replies in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I Have checked your suggestions..

1. Data Types: All set to currency. Changed to General Number but no
effect.

Currency would be fine. (It's enough to define that the value is numeric.)

2. Timing/Dependencies - Some fields are based on queries, herein maybe
the problem, which appears to be solved by requering the fields.
Perhaps also indicated by the inconsistency of the error.

You might test whether it makes any difference to temporarily eliminate from
the query any expression of this nature:
[Forms].[Form1].[Text0]

3. Expressions that may be conditionally invalid - Only one expression
used, Table and Field names change, but the rest is all the same. Example:
=DSum("[txtTotal]","[tblExpenses]","[txtExpenseDate] Between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")
No Null values in the dates or other fields.

As you say, that should not cause a problem.

4. Problem Elsewhere: Always a possibility, but it has not manifested
itself anywhere else.

5. Bad Name: I have the March 2007 release of your Issue Checker which I
use regularly, thank you.

Is there any way to check the timing? I could just live with requering the
form when errors appear.

The fact that requerying (eventually) fixes it suggests that it could be a
matter of timing. There are so many factors here though, and it can get
involved if you are doing things in events.

The most suspect ones are where the query contains a reference to the form,
or anything in the Current event of the form/subform. Any conditional
formatting can also be suspect.

Hope that's of some use.

Several things could be happening.

1. Data Types
==========
Sometimes Access is unable to figure out the intended data types. If this
text box has a Control Source of:
=[Text0] + [Text1]
where Text0 and Text1 contain numeric calculations, set the Format
property of all 3 text boxes to:
General Number
so Access knows their data type.

In some cases, you may need to typecast the expression too. Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Incorrect delimiters can also be a factor here.

2. Timing/dependencies
=================
If Form1 is bound to a query, and the query contains something
(calculated field, criteria, ...) that reads a value from Form1, you have
a chicken'n'egg situation. The query can't decide on the values to return
to the form until the form is loaded, and the form can't load until it
gets the records from the query.

There are many other dependencies also, between main form and subform,
between one form and another, or even circular dependencies between
controls on the one form.

(The Tab Order of the controls is generally not a factor in these
dependencies.)

3. Expressions that are conditionally invalid
===============================
Some expressions are valid in some cases, but not others. For example, if
you use:
=DSum("Amount", "Table1", "CategoryID = " & [CategoryID])
that works fine if there is a value in the CategoryID field. However,
when CategoryID is null, the 3rd argument resolves to just:
CategoryID =
which is clearly invalid. To avoid, this, use Nz() to supply a value for
null, e.g.:
=DSum("Amount", "Table1", "CategoryID = " & Nz([CategoryID],0))

4. Problem elsewhere
===============
Once one calculated control fails, Access gives up and doesn't try to
calculate the others. So the ones it did earlier show a result, while all
the ones it did not get you yet fail with #Error.

It is therefore possible to see #Error in a control that has no problem
at all. The problem may be with a completely different control, even one
that is not visible (so you are likely to forget it could be a factor.)

5. Bad name
=========
If the name of a field or control is a reserved word or something
ambiguous, it can work in some circumstances and fail in others. Here's a
list of names to check:
http://allenbrowne.com/AppIssueBadWord.html

As well as that list, if the control has the same name as a field, but is
bound to something else, Access gets confused.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an unbound form with 9 calculated fields which display business
indicators. Some calculations are DSum from tables in the DB while others
are calculated from these (DSUM) values on the form.

The form can be opened from a Menu and from command buttons on two menu
forms.

When the form is opened from one of the menu forms, no problem. The
problem is that when I open the form from the Menu or the other menu
from, one field displays #Error. This particular field is calculated
from two other fields on the same form. (No DSUM on this one).

I placed a command button to requery the "#Error" field and it works,
but only after two or three clicks.
I thought this might be caused by the Tab Order of the fields and
re-ordered these in the order that they are required for the
calculations - this did not fix the problem.
The only other objects open are the unbound menu forms.

Any suggestions for this "strange but true" issue?

 

[Tom Ventouris]

Thank you once again.
I have eliminated expressions such as [Forms].[Form1].[Text0] and
eliminated most of the problem with it.
The #Error now appears, only occasionally, in one field.

This is a minor detail in a form that presents the Month to Date business
indicators from 9 tables
We are happy to have it working despite this minor irritation.

I have created a command button to requery the problem field if it returns
an error.
I considered requerying through the form's timer event if the #error shows
up, but this would present bigger
problems if the error persisted - continuous loop of requiring.

I have also created a report with the same data but do not see any problems
there.
If I have any complaints, I will remove the form and let the user view data
on the report

Thanks again for your help.

Replies in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I Have checked your suggestions..

1. Data Types: All set to currency. Changed to General Number but no
effect.

Currency would be fine. (It's enough to define that the value is numeric.)

2. Timing/Dependencies - Some fields are based on queries, herein maybe
the problem, which appears to be solved by requering the fields.
Perhaps also indicated by the inconsistency of the error.

You might test whether it makes any difference to temporarily eliminate
from the query any expression of this nature:
[Forms].[Form1].[Text0]

3. Expressions that may be conditionally invalid - Only one expression
used, Table and Field names change, but the rest is all the same.
Example:
=DSum("[txtTotal]","[tblExpenses]","[txtExpenseDate] Between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")
No Null values in the dates or other fields.

As you say, that should not cause a problem.

4. Problem Elsewhere: Always a possibility, but it has not manifested
itself anywhere else.

5. Bad Name: I have the March 2007 release of your Issue Checker which I
use regularly, thank you.

Is there any way to check the timing? I could just live with requering
the form when errors appear.

The fact that requerying (eventually) fixes it suggests that it could be a
matter of timing. There are so many factors here though, and it can get
involved if you are doing things in events.

The most suspect ones are where the query contains a reference to the
form, or anything in the Current event of the form/subform. Any
conditional formatting can also be suspect.

Hope that's of some use.

Several things could be happening.

1. Data Types
==========
Sometimes Access is unable to figure out the intended data types. If
this text box has a Control Source of:
=[Text0] + [Text1]
where Text0 and Text1 contain numeric calculations, set the Format
property of all 3 text boxes to:
General Number
so Access knows their data type.

In some cases, you may need to typecast the expression too. Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Incorrect delimiters can also be a factor here.

2. Timing/dependencies
=================
If Form1 is bound to a query, and the query contains something
(calculated field, criteria, ...) that reads a value from Form1, you
have a chicken'n'egg situation. The query can't decide on the values to
return to the form until the form is loaded, and the form can't load
until it gets the records from the query.

There are many other dependencies also, between main form and subform,
between one form and another, or even circular dependencies between
controls on the one form.

(The Tab Order of the controls is generally not a factor in these
dependencies.)

3. Expressions that are conditionally invalid
===============================
Some expressions are valid in some cases, but not others. For example,
if you use:
=DSum("Amount", "Table1", "CategoryID = " & [CategoryID])
that works fine if there is a value in the CategoryID field. However,
when CategoryID is null, the 3rd argument resolves to just:
CategoryID =
which is clearly invalid. To avoid, this, use Nz() to supply a value for
null, e.g.:
=DSum("Amount", "Table1", "CategoryID = " & Nz([CategoryID],0))

4. Problem elsewhere
===============
Once one calculated control fails, Access gives up and doesn't try to
calculate the others. So the ones it did earlier show a result, while
all the ones it did not get you yet fail with #Error.

It is therefore possible to see #Error in a control that has no problem
at all. The problem may be with a completely different control, even one
that is not visible (so you are likely to forget it could be a factor.)

5. Bad name
=========
If the name of a field or control is a reserved word or something
ambiguous, it can work in some circumstances and fail in others. Here's
a list of names to check:
http://allenbrowne.com/AppIssueBadWord.html

As well as that list, if the control has the same name as a field, but
is bound to something else, Access gets confused.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an unbound form with 9 calculated fields which display business
indicators. Some calculations are DSum from tables in the DB while
others are calculated from these (DSUM) values on the form.

The form can be opened from a Menu and from command buttons on two menu
forms.

When the form is opened from one of the menu forms, no problem. The
problem is that when I open the form from the Menu or the other menu
from, one field displays #Error. This particular field is calculated
from two other fields on the same form. (No DSUM on this one).

I placed a command button to requery the "#Error" field and it works,
but only after two or three clicks.
I thought this might be caused by the Tab Order of the fields and
re-ordered these in the order that they are required for the
calculations - this did not fix the problem.
The only other objects open are the unbound menu forms.

Any suggestions for this "strange but true" issue?

 

[aaron.kempf]

wait it appears only occassionaly?

aren't you guys tired of using a flaky database engine?
SQL Server supports RELIABLE RESULTS

Thank you once again.
I have eliminated expressions such as [Forms].[Form1].[Text0] and
eliminated most of the problem with it.
The #Error now appears, only occasionally, in one field.

This is a minor detail in a form that presents the Month to Date business
indicators from 9 tables
We are happy to have it working despite this minor irritation.

I have created a command button to requery the problem field if it returns
an error.
I considered requerying through the form's timer event if the #error shows
up, but this would present bigger
problems if the error persisted - continuous loop of requiring.

I have also created a report with the same data but do not see any problems
there.
If I have any complaints, I will remove the form and let the user view data
on the report

Thanks again for your help.

Replies in-line.

Currency would be fine. (It's enough to define that the value is numeric.)

You might test whether it makes any difference to temporarily eliminate
from the query any expression of this nature:
[Forms].[Form1].[Text0]

3. Expressions that may be conditionally invalid - Only one expression
used, Table and Field names change, but the rest is all the same.
Example:
=DSum("[txtTotal]","[tblExpenses]","[txtExpenseDate] Between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)")
No Null values in the dates or other fields.

As you say, that should not cause a problem.

The fact that requerying (eventually) fixes it suggests that it could be a
matter of timing. There are so many factors here though, and it can get
involved if you are doing things in events.

The most suspect ones are where the query contains a reference to the
form, or anything in the Current event of the form/subform. Any
conditional formatting can also be suspect.

Hope that's of some use.

Several things could be happening.
1. Data Types
==========
Sometimes Access is unable to figure out the intended data types. If
this text box has a Control Source of:
=[Text0] + [Text1]
where Text0 and Text1 contain numeric calculations, set the Format
property of all 3 text boxes to:
General Number
so Access knows their data type.
In some cases, you may need to typecast the expression too. Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
Incorrect delimiters can also be a factor here.
2. Timing/dependencies
=================
If Form1 is bound to a query, and the query contains something
(calculated field, criteria, ...) that reads a value from Form1, you
have a chicken'n'egg situation. The query can't decide on the values to
return to the form until the form is loaded, and the form can't load
until it gets the records from the query.
There are many other dependencies also, between main form and subform,
between one form and another, or even circular dependencies between
controls on the one form.
(The Tab Order of the controls is generally not a factor in these
dependencies.)
3. Expressions that are conditionally invalid
===============================
Some expressions are valid in some cases, but not others. For example,
if you use:
=DSum("Amount", "Table1", "CategoryID = " & [CategoryID])
that works fine if there is a value in the CategoryID field. However,
when CategoryID is null, the 3rd argument resolves to just:
CategoryID =
which is clearly invalid. To avoid, this, use Nz() to supply a value for
null, e.g.:
=DSum("Amount", "Table1", "CategoryID = " & Nz([CategoryID],0))
4. Problem elsewhere
===============
Once one calculated control fails, Access gives up and doesn't try to
calculate the others. So the ones it did earlier show a result, while
all the ones it did not get you yet fail with #Error.
It is therefore possible to see #Error in a control that has no problem
at all. The problem may be with a completely different control, even one
that is not visible (so you are likely to forget it could be a factor.)
5. Bad name
=========
If the name of a field or control is a reserved word or something
ambiguous, it can work in some circumstances and fail in others. Here's
a list of names to check:
http://allenbrowne.com/AppIssueBadWord.html
As well as that list, if the control has the same name as a field, but
is bound to something else, Access gets confused.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
I have an unbound form with 9 calculated fields which display business
indicators. Some calculations are DSum from tables in the DB while
others are calculated from these (DSUM) values on the form.
The form can be opened from a Menu and from command buttons on two menu
forms.
When the form is opened from one of the menu forms, no problem. The
problem is that when I open the form from the Menu or the other menu
from, one field displays #Error. This particular field is calculated
from two other fields on the same form. (No DSUM on this one).
I placed a command button to requery the "#Error" field and it works,
but only after two or three clicks.
I thought this might be caused by the Tab Order of the fields and
re-ordered these in the order that they are required for the
calculations - this did not fix the problem.
The only other objects open are the unbound menu forms.
Any suggestions for this "strange but true" issue?