Using Dcount to count number of records matching a date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Phil - 11 Nov 2006 11:11 GMT

Hello
I am trying to use Dcount in a text box on an unbound form to count the
number of records that match criteria for a given date like this:

=DCount("[Field]","This Table","[Field] = 'This String' and [Start
Date] ># 23/3/2001#")

Looking for occurrences of 'This String' in the 'Field' of 'This
Table' that have a Start Date > than 23/3/2001. So fa so good and this
works OK. What I would like to do is replace the date (#23/3/2001#)
with a Combo box control that has the date in it. Something like this:

=DCount("[Field]","This Table","[Field] = 'This String' and [Start
Date] >[CboStartDate]")

No matter how much I try and change the end part I can't get it to
work. Can anybody tell me if this is possible and if so how to do it?
Thanks in anticipation.

Reply to this Message

Allen Browne - 11 Nov 2006 11:21 GMT

Concatenate the values into the 3rd string:

=DCount("*", "This Table", "([Field] = 'This String') And ([Start Date] > "
& Format([CboStartDate], "\#mm/dd/yyyy\#") & ")")

It is important to use the US date format in the string. For more info on
that, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

Signature

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.

> Hello
> I am trying to use Dcount in a text box on an unbound form to count the
[quoted text clipped - 14 lines]
> work. Can anybody tell me if this is possible and if so how to do it?
> Thanks in anticipation.

Reply to this Message

Phil - 11 Nov 2006 11:33 GMT

> Concatenate the values into the 3rd string:
>
[quoted text clipped - 5 lines]
> at:
> http://allenbrowne.com/ser-36.html

Thanks very much for the quick response, I was eager to try it out! I
now get a dialog box pop up telling me this: 'Extra ) in query
expression ([Field] = 'This String') And ([Start Date] > " Any ideas???
Thanks again

--

Reply to this Message

Douglas J. Steele - 11 Nov 2006 12:09 GMT

>> Concatenate the values into the 3rd string:
>>
[quoted text clipped - 9 lines]
> now get a dialog box pop up telling me this: 'Extra ) in query
> expression ([Field] = 'This String') And ([Start Date] > " Any ideas???

You sure you typed exactly what Allen had? His parentheses appear to be
correct.

Copy-and-paste exactly what you've got.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

Reply to this Message

Phil - 11 Nov 2006 13:24 GMT

> > > Concatenate the values into the 3rd string:
> > >
[quoted text clipped - 15 lines]
>
> Copy-and-paste exactly what you've got.

Ok I did that - discovered the problem was with the Combobox in that it
did not have a value in it when I pasted the expression into the record
source. Thanks very much for the help.

--

Reply to this Message