Selasa, 19 April 2016

[MS_AccessPros] Dcount in a Query

 

Hi


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

Id

Div

Refid

Books

Mdate


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.


Phil

                       







__._,_.___

Posted by: pdk444444@yahoo.co.uk
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)

.

__,_._,___

Tidak ada komentar:

Posting Komentar