DCount between dates

[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,

 

[Allen Browne]

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.

 

[Douglas J. Steele]

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)

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.

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,

 

[redFred]

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.

 

[redFred]

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.