DCount

[Leslie Isaacs]

Hello All

I have a module that needs to carry out one of two sets of actions,
depending on whether or not there are any records in a certain table with
given values in 2 particular fields. The table is called [x confirmed], and
the two fields to be tested are [practice] and [month name]. If there are
any records in [x confirmed] with value of [Forms]![frm x main]![prac name]
and [Forms]![frm x main]![month name] respectively in [practice] and [month
name] then I need the first set of actions, otherwise I need the second set
of actions.

The expression I have used in the module is:
If DCount("*", "[x confirmed]", "((practice='" & [Forms]![frm x main]![prac
name] & "')and([month name]='" & [Forms]![frm x main]![month name] & "'))")

0 Then ...

This generally seems to work fine, but today it went the wrong way for one
particular [practice] - i.e. in a situation where there were no
corresponding records in [x confirmed] the 'true case' set of actions were
carried out.

In order to look at the results being returned by the above expression I
pasted it into a text field on a form, to find that it returned a #error for
the situation in question: it returned a correct zero for other cases where
there were no corresponding records in [x confirmed].

So what is happening?!

I would be very grateful for any help.

Many thanks
Les

 

[Wayne Morgan]

What sort of values do you have for the Practice Name? By chance do any of
them have an apostrophe in them, such as O'Hare? If so, then you won't be
able to use single quotes around the value because the apostrophe gets
picked up as a single quote also and you then have unmatched quotes. If that
is the problem try changing

"((practice='" & [Forms]![frm x main]![prac name] & "')
to
"((practice=""" & [Forms]![frm x main]![prac name] & """)

That's 2 sets of 3 double quotes. Doubling up the quotes will put a single
double quote in the output of the string instead of the single quote as you
now have it. Another way of doing this is to concatenate in Chr(34) instead
of doubling up the quotes.

--
Wayne Morgan
Microsoft Access MVP

Hello All

I have a module that needs to carry out one of two sets of actions,
depending on whether or not there are any records in a certain table with
given values in 2 particular fields. The table is called [x confirmed], and
the two fields to be tested are [practice] and [month name]. If there are
any records in [x confirmed] with value of [Forms]![frm x main]![prac name]
and [Forms]![frm x main]![month name] respectively in [practice] and [month
name] then I need the first set of actions, otherwise I need the second set
of actions.

The expression I have used in the module is:
If DCount("*", "[x confirmed]", "((practice='" & [Forms]![frm x main]![prac
name] & "')and([month name]='" & [Forms]![frm x main]![month name] & "'))")

0 Then ...

This generally seems to work fine, but today it went the wrong way for one
particular [practice] - i.e. in a situation where there were no
corresponding records in [x confirmed] the 'true case' set of actions were
carried out.

In order to look at the results being returned by the above expression I
pasted it into a text field on a form, to find that it returned a #error for
the situation in question: it returned a correct zero for other cases where
there were no corresponding records in [x confirmed].

So what is happening?!

I would be very grateful for any help.

Many thanks
Les