Selasa, 19 April 2016

[MS_AccessPros] Dcount in a Query



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:
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)



Tidak ada komentar:

Posting Komentar