dCount by date range

[Ruegen]

I have two text fields for date, one from date and one until date, on a form.



I then have an unbound searching a table

=DCount("NewSchoolsID","tblSchools")

I need the criteria to sort a few things

the field CatLabelPrinted is null

it is within the date range of those two text fields

[June7]

Maybe:

DCount("NewSchoolsID", "tblSchools", "CatLabelPrinted Is Null AND datefield BETWEEN #" & datefrom & "# AND #" & dateto & "#")

[Ruegen]

Maybe:

DCount("NewSchoolsID", "tblSchools", "CatLabelPrinted Is Null AND datefield BETWEEN #" & datefrom & "# AND #" & dateto & "#")

see I would have stupidly done it like so:

CatLabelPrinted Is Null AND "datefield BETWEEN" # & datefrom & # AND # & dateto & #")

I am still getting my head around concatenation.

You encompass the whole of the criteria in " " except the dates which need their own

I am guessing you have to use #" & datefrom & " # to let it know you want the value inside that field - why it can't be just [DateFrom] is because it isn't a field and unbound?

[June7]

Actually, I think the domain aggregate needs the delimiters regardless if control is bound. The WHERE CONDITION follows rules of SQL WHERE clause.

[Ruegen]

Ended up doing this

works perfectly many thanks June7.

I need to buy you a beer.

I ended up using this

=DCount("NewSchoolsID","tblSchools","not isnull(CatalogueLabelPrinted) AND CatalogueLabelPrinted BETWEEN #" & [BeforeDate] & "# AND #" & [AfterDate] & "#")