DCOUNT (Date fields) (1 Viewer)

[kacey8]

Hi guys,

Hoping you can help, I have a form in my database with a from date box and to date box. I am trying to count the number of entries in the table between certain dates.

Table name CANVAS_DATA
Field name LETTER_1_DATE
Form name Admin_Reports
Date from name txtfrom
Date to name txtto

I was hoping to use DCOUNT to get it working but I've not had much joy. Any suggestions?

 

[kacey8]

This is my current expression for D count but it returns all records.

=DCount("[Letter_1_Date]","[CANVAS_DATA]","[Letter_1_Date] Between
#" & [Forms]![Admin_Reports]![txtfrom] & "# And
#" & [Forms]![Admin_Reports]![txtto] & "#")

 

[CJ_London]

may not be the answer to your question, but you need to format the text dates into US format of mm/dd/yyyy - any day over 12 will be interpreted correctly (because there are only 12 month in the year) but any day <=12 will be interpreted as a month. The # characters tells sql to interpret the string in between as a date e.g.

1st Aug - shown as 01/08/2016 will be interpreted wrongly as 8th Jan

13th Aug - shown as 13/08/2016 will be interpreted correctly as 13th Aug


use

....."[Letter_1_Date] Between
#" & format([Forms]![Admin_Reports]![txtfrom],"mm/dd/yyyy") & "#....

 

[kacey8]

may not be the answer to your question,

Yes.... Yes it is, you beauty... Thanks buddy.