Not there yet!
First of all because refid is numeric should that section be simply
...AND [refid] = [forms]![referees]![id] AND...
...AND [refid] = " & [forms]![referees]![id] & " AND...
Also in the dates section your example seems to not have enough closing parentheses - surely we need one to end the format function and one to end the iif function?
more help please
On Tuesday, 19 April 2016, 13:20, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
DCount("id","matches", "[Books] = 0 AND [Div] ='" & [Div] & "' AND [Refid]=" & [forms]![referees]![id] & " AND [mdate] BETWEEN IIf([date1] IS NULL,#2000-01-01#, #" & Format([date1], "yyyy-mm-dd") & "# AND IIf([date2] IS NULL,#2099-12-31#, #" & Format([date2], "yyyy-mm-dd") & "#")
It's clear from the date constants you provided that you're on a non-US machine. Date literals in SQL (which is what DCount generates from your parameters) must always be in US format in Access or some "universal" format such as I used above.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
On Apr 19, 2016, at 12:15 PM, email@example.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Posted by: Phil Knowles <firstname.lastname@example.org>
|Reply via web post||•||Reply to sender||•||Reply to group||•||Start a New Topic||•||Messages in this topic (3)|