Selasa, 19 April 2016

Re: [MS_AccessPros] Dcount in a Query



Try this:

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 
(Paris, France)

On Apr 19, 2016, at 12:15 PM, [MS_Access_Professionals] <> wrote:


I seem to struggle with the criteria syntax in the Dcount function used in a query!

I have a fairly complicated query but I will try to condense it down to the minimum required to answer my question

In this query I have one table called Matches and the fields in the table I am interested in are called






It is a query grouped by Div and I want to include all the records for a given refid and between entered dates (Date1 and Date2)

But then for that set of records I want to count the number of records where Books has a certain value (say 0) for each of the grouped Divs

The first bit is not an issue but my Dcount to achieve the 2nd bit is confusing

I am using Dcount("id","matches", "Criteria")

where I want "Criteria" to do the following 

[Books] = 0 and

[Div] ='" & [Div] & "' and                (this is to give me the count per grouping)

[Refid]=[forms]![referees]![id] and

[mdate] between Iif([date1] is null,#01/01/2000#,[date1])

            and        Iif([date2] is null,#31/12/2099#,[date2])

it is the syntax for the date bit that I am struggling with.

If anyone can help to clarify this I would appreciate it.




Posted by: John Viescas <>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)



Tidak ada komentar:

Posting Komentar