DCount between dates

  • Thread starter Thread starter redFred
  • Start date Start date
R

redFred

I wish to use DCount to count records whose RFDate is between two dates
provided by a selector form. My control source follows:

=DCount("[RFDate]","[tbl 2 Job]","[RFDate] = & "Between
#"&[Forms]![9frmSelectorDate]![txtStartDate]&"# And
#"&[Forms]![9frmSelectorDate]![txtEndDate]&"#")

Is this code correct? I cannot find an example of DCount using Between in
the select criteria, but have cobbled this together. I am afraid I don't
understand it enough or else my hammer just isn't big enough.

Assuming code is not right, what is correct code and what does it mean?

Thanks,
 
That should work provided:
a) both text boxes contain a valid date;
b) your date format is US;
c) you don't have a timing lag between when Access calculates this and when
you need it.
 
Sorry, Allen, but you didn't look closely enough. <g>

=DCount("[RFDate]","[tbl 2 Job]","[RFDate] = & "Between
#"&[Forms]![9frmSelectorDate]![txtStartDate]&"# And
#"&[Forms]![9frmSelectorDate]![txtEndDate]&"#")

The equal sign is incorrect, as is the quoting.

=DCount("[RFDate]","[tbl 2 Job]","[RFDate] Between
#"&[Forms]![9frmSelectorDate]![txtStartDate]&"# And
#"&[Forms]![9frmSelectorDate]![txtEndDate]&"#")

although (as you know from your point 2) it would be better as

=DCount("[RFDate]","[tbl 2 Job]","[RFDate] Between " & _
Format([Forms]![9frmSelectorDate]![txtStartDate], "\#yyyy\-mm\-dd\#") & _
" And " & _
Format([Forms]![9frmSelectorDate]![txtEndDate], "\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Allen Browne said:
That should work provided:
a) both text boxes contain a valid date;
b) your date format is US;
c) you don't have a timing lag between when Access calculates this and
when you need it.

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

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

redFred said:
I wish to use DCount to count records whose RFDate is between two dates
provided by a selector form. My control source follows:

=DCount("[RFDate]","[tbl 2 Job]","[RFDate] = & "Between
#"&[Forms]![9frmSelectorDate]![txtStartDate]&"# And
#"&[Forms]![9frmSelectorDate]![txtEndDate]&"#")

Is this code correct? I cannot find an example of DCount using Between
in
the select criteria, but have cobbled this together. I am afraid I don't
understand it enough or else my hammer just isn't big enough.

Assuming code is not right, what is correct code and what does it mean?

Thanks,
Click to expand...
Click to expand...
 
Thanks to you both!

Douglas, I tried the proper code (with formatting) but it didn't work. I
then tried the simpler one as this will never have other than US date format.
No joy there either.

So, I placed [txtStartDate] and [txtEndDate] on the report and simplified
the code and realized I should be counting [EnterDate].

=DCount("[EnterDate]","[tbl 2 Job]","[RFDate] Between #" & [txtStartDate] &
"# _And #" & [txtEndDate] & "#")

This latest code version and the "US" only version both return all the
records in the table...the date is not selected.

Is this a timing issue as Allen warns about? What kind of thins can I do to
resolve that?

Appreciate greatly the help.
 
The codes work. No surprise, you guys know what you are doing.

A good night's sleep and I could clearly see that RFDate is not in that
table. Duh!

Thanks for the timely and educating response.
 
Back
Top