DCOUNT intermittent #Error on report

[Mike H]

Hi, I am trying to sort out a problem for someone... They have a series of
reports based on queries that get all their data from one large table.

One report conains a series of text boxes, each using DCount in a
straightforward way.
The query chooses its data according to 2 dates and a client name which are
all in drop-downs on a form.
The query always works OK, regardless of what is selected in the form
The Report works fine regardless of what is selected in the form (so the
expressions in the text boxes are all correct)... EXCEPT that one client
causes #Error to appear in some of the text boxes. It does it for that
client regardless of which date range is set (even though the data for
January has no intersection with the data for March).

And to make it even weirder, one DCount counts all the data, and has no
problem; while the DCount next to it which counts a sub-set of that same
data displays #Error

I am lost for an explanation - any ideas?

P.S. They are using Access 2000 on a Windows XP machine.

Thank you

 

[Wayne Morgan]

If it's just the one client, I would suspect one of two things as the most
likely problem.

1) The client needs Office, Jet, or MDAC updates. These can be found here:
http://office.microsoft.com/en-us/officeupdate/default.aspx
http://msdn.microsoft.com/data/downloads/updates/default.aspx

2) There is a problem with one or more of the library references in Access
on that computer. For more information on that, see this link:
http://www.allenbrowne.com/ser-38.html

 

[Mike H]

Hi, by client, I meant a person who buys things from the company. When that
client's name is selected in the drop-down on the form there is an error,
regardless of which machine it's run on. Apologies about my ambiguity.

 

[John Spencer]

Are you using the Client's name in the DCOUNT?

If so, does this client have a apostrophe in the name (like O'Conner)? If
so and you are using an apostrophe in the DCount where clause, this could
cause the error. Same thing goes for quote marks " , but that is usually
less common in a name field.

 

[Wayne Morgan]

How many columns are in the Combo Box that you use to select the client?
What is the Bound Column? Is the Bound Column an ID field, not the client's
name? If so, does the field in the table that you're storing this selection
in (Control Source of the combo box) accept that type of data?

While the user is seeing the client's name and selecting that, data from
another column in the combo box may be what is being stored in the form's
table. This is normal, but you have to realize what is happening so that it
gets set up properly.