Jumat, 22 April 2016

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



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


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, pdk444444@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> 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: 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!



Tidak ada komentar:

Posting Komentar