Using Dcount to count number of records matching a date

[Phil]

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.

 

[Allen Browne]

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

 

[Phil]

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

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

--

 

[Douglas J. Steele]

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

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???

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

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

 

[Phil]

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

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???

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

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.

--