Prev: Running a query against a password protected spreadsheet
Next: Converting a Date
From: inungh on 19 Mar 2010 07:41
TRANSFORM Count(MyQuery.ID) as CountOfID
SELECT MyQuery.Code, DCount("*" , "tblActivityDetails" ,
[ActivityDate] >= #3/14/2010# and [ActivityDate] <= #3/20/2010# and
[ActivityID] = tblActivityDetails.ActivityID) AS Total,
Count(MyQuery.ID) AS [Total Elements]

I have above DCount in my crosstab query. It seems that crosstab does
not like DCount in the query.

The Dcount works in a select query.

any information is great appreciated,
From: Duane Hookom on 19 Mar 2010 08:34
I haven't had issues with DCount() in crosstabs. Try correct the syntax to:
DCount("*" , "tblActivityDetails" , "[ActivityDate] Between #3/14/2010# and
#3/20/2010# and ActivityID=" & [ActivityID] )
This assumes ActivityID is a numeric field in MyQuery.

--
Duane Hookom
MS Access MVP


"inungh" <inungh(a)gmail.com> wrote in message
news:b37d49e8-9227-4bb0-99ab-972b0f2f511f(a)m37g2000yqf.googlegroups.com...
> TRANSFORM Count(MyQuery.ID) as CountOfID
> SELECT MyQuery.Code, DCount("*" , "tblActivityDetails" ,
> [ActivityDate] >= #3/14/2010# and [ActivityDate] <= #3/20/2010# and
> [ActivityID] = tblActivityDetails.ActivityID) AS Total,
> Count(MyQuery.ID) AS [Total Elements]
>
> I have above DCount in my crosstab query. It seems that crosstab does
> not like DCount in the query.
>
> The Dcount works in a select query.
>
> any information is great appreciated,

From: inungh on 23 Mar 2010 13:46
On Mar 19, 8:34 am, "Duane Hookom" <duanehoo...(a)gmail.com> wrote:
> I haven't had issues with DCount() in crosstabs. Try correct the syntax to:
> DCount("*" , "tblActivityDetails" , "[ActivityDate] Between #3/14/2010# and
> #3/20/2010# and ActivityID=" & [ActivityID]  )
>  This assumes ActivityID is a numeric field in MyQuery.
>
> --
> Duane Hookom
> MS Access MVP
>
> "inungh" <inu...(a)gmail.com> wrote in message
>
> news:b37d49e8-9227-4bb0-99ab-972b0f2f511f(a)m37g2000yqf.googlegroups.com...
>
>
>
> > TRANSFORM Count(MyQuery.ID) as CountOfID
> > SELECT MyQuery.Code, DCount("*" , "tblActivityDetails" ,
> > [ActivityDate] >= #3/14/2010# and [ActivityDate] <= #3/20/2010# and
> > [ActivityID] = tblActivityDetails.ActivityID) AS Total,
> > Count(MyQuery.ID) AS [Total Elements]
>
> > I have above DCount in my crosstab query. It seems that crosstab does
> > not like DCount in the query.
>
> > The Dcount works in a select query.
>
> > any information is great appreciated,- Hide quoted text -
>
> - Show quoted text -

Thanks for helping,
Yes, DCount is working. The problem is the activityID is in the same
table of the query.
Are there any way to refer the ID field in the same query?

Thanks again,

From: Duane Hookom on 23 Mar 2010 20:25
What do you mean by the same query?

--
Duane Hookom
MS Access MVP


"inungh" <inungh(a)gmail.com> wrote in message
news:dc0705a8-b3cc-46d7-9690-3a8399aa4f27(a)g19g2000yqe.googlegroups.com...
> On Mar 19, 8:34 am, "Duane Hookom" <duanehoo...(a)gmail.com> wrote:
>> I haven't had issues with DCount() in crosstabs. Try correct the syntax
>> to:
>> DCount("*" , "tblActivityDetails" , "[ActivityDate] Between #3/14/2010#
>> and
>> #3/20/2010# and ActivityID=" & [ActivityID] )
>> This assumes ActivityID is a numeric field in MyQuery.
>>
>> --
>> Duane Hookom
>> MS Access MVP
>>
>> "inungh" <inu...(a)gmail.com> wrote in message
>>
>> news:b37d49e8-9227-4bb0-99ab-972b0f2f511f(a)m37g2000yqf.googlegroups.com...
>>
>>
>>
>> > TRANSFORM Count(MyQuery.ID) as CountOfID
>> > SELECT MyQuery.Code, DCount("*" , "tblActivityDetails" ,
>> > [ActivityDate] >= #3/14/2010# and [ActivityDate] <= #3/20/2010# and
>> > [ActivityID] = tblActivityDetails.ActivityID) AS Total,
>> > Count(MyQuery.ID) AS [Total Elements]
>>
>> > I have above DCount in my crosstab query. It seems that crosstab does
>> > not like DCount in the query.
>>
>> > The Dcount works in a select query.
>>
>> > any information is great appreciated,- Hide quoted text -
>>
>> - Show quoted text -
>
> Thanks for helping,
> Yes, DCount is working. The problem is the activityID is in the same
> table of the query.
> Are there any way to refer the ID field in the same query?
>
> Thanks again,
>
 | 
Pages: 1
Prev: Running a query against a password protected spreadsheet
Next: Converting a Date