Can't get one date result - trying this code

[Ruegen]

I have a form which opens a report and displays all schools that have a date field filled with dates between dates on unbound text boxes on a form.

I also have a dcount which should pull the same results as the report and count them

=DCount("NewSchoolsID","tblSchools",Not IsNull([CatalogueLabelPrinted]) And "CatalogueLabelPrinted >= #" & [BeforeDate] & "# and <= #" & [AfterDate] & "#")



I tried "between" but that didn't work for dates between current/picked same date and current/picked same date

I get 0 instead of the count that the report has made so I tried >= but I get too many results.

The idea is that the field has to be not null and within the date period on the form. Any suggestions?

[June7]

Try:

=DCount("NewSchoolsID", "tblSchools", "Not IsNull([CatalogueLabelPrinted]) And CatalogueLabelPrinted Between #" & [BeforeDate] & "# And #" & [AfterDate] & "#")

or

=DCount("NewSchoolsID", "tblSchools", "Not IsNull([CatalogueLabelPrinted]) And CatalogueLabelPrinted >=" & [BeforeDate] & "# And CatalogueLabelPrinted <= #" & [AfterDate] & "#")

[Ruegen]

Try:

=DCount("NewSchoolsID", "tblSchools", "Not IsNull([CatalogueLabelPrinted]) And CatalogueLabelPrinted Between #" & [BeforeDate] & "# And #" & [AfterDate] & "#")

or

=DCount("NewSchoolsID", "tblSchools", "Not IsNull([CatalogueLabelPrinted]) And CatalogueLabelPrinted >=" & [BeforeDate] & "# And CatalogueLabelPrinted <= #" & [AfterDate] & "#")

I used

=DCount("NewSchoolsID","tblSchools","Not IsNull([CatalogueLabelPrinted]) And CatalogueLabelPrinted >=#" & [BeforeDate] & "# And CatalogueLabelPrinted <= #" & [AfterDate] & "#")

However I get 0

[Ruegen]

Attachment 14305

how the report works

[amrut]

Does this works ?

=DCount("NewSchoolsID","tblSchools","Not IsNull([CatalogueLabelPrinted]) And CatalogueLabelPrinted >=#" & Format([BeforeDate],"mm\/dd\/yyyy") & "# And CatalogueLabelPrinted <= #" & Format([AfterDate],"mm\/dd\/yyyy") & "#")

[Ruegen]

Does this works ?

=DCount("NewSchoolsID","tblSchools","Not IsNull([CatalogueLabelPrinted]) And CatalogueLabelPrinted >=#" & Format([BeforeDate],"mm\/dd\/yyyy") & "# And CatalogueLabelPrinted <= #" & Format([AfterDate],"mm\/dd\/yyyy") & "#")

I'm not sure why and how formatting the date fields would work, can you explain why? I'll give it a go either way.

[amrut]

I am always used to follow dd/mm/yyyy format. While working on my first database, in case of VBA for forms and expressions in text boxes , the aggregate functions returned wrong results. Formatting them in the way shown, yielded correct results.
Quote from http://allenbrowne.com/ser-36.html. :
Regardless or your regional settings, Access expects these literal dates to be in the American format, e.g. #12/31/1999#.

[June7]

I get 'invalid attachment' error on your attachment link.

[lfpm062010]

Is the field [CatalogueLabelPrinted] contains date and time?

If you have time stamp then looking for [BeforeDate] and [AfterDate] with the same value will not return any result. For example: [BeforeDate] and [AfterDate] both equal #10/01/2013# 'MM/DD/YYYY
To get result with time stamp then you have to use [BeforeDate] = #10/01/2013 00:00:00# and [AfterDate] = #10/01/2013 23:59:59#.

[Ruegen]

I am always used to follow dd/mm/yyyy format. While working on my first database, in case of VBA for forms and expressions in text boxes , the aggregate functions returned wrong results. Formatting them in the way shown, yielded correct results.
Quote from http://allenbrowne.com/ser-36.html. :
Regardless or your regional settings, Access expects these literal dates to be in the American format, e.g. #12/31/1999#.

Interesting. That fixed the problem! will have to be aware of that in the future!!

Thank you all for the help!