Re: [MS_AccessPros] Dcount in a Query

 

Hi John

Not there yet!

First of all because refid is numeric should that section be simply

...AND [refid] = [forms]![referees]![id] AND...

rather than 

...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

cheers

Phil

On Tuesday, 19 April 2016, 13:20, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

Phil-

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 

http://www.viescas.com/ 

(Paris, France)

On Apr 19, 2016, at 12:15 PM, pdk444444@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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: Phil Knowles <pdk444444@yahoo.co.uk>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

---

Upgrade your account with the latest Yahoo Mail app

Get organized with the fast and easy-to-use Yahoo Mail app. Upgrade today!

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___