adp report error when using dcount in control source of text box

[Rachel]

Hi,

I keep getting an error message in my adp report when I try to use a
stored procedure as the domain in a dcount formula , however when i
use a view instead it works fine

This is the dcount that works:
=DCount("[Client_ID]","vw_ClientDetails"," [Gender] = 'male' AND
[Ethnic ID] = '1'")
This is the one that doesn't:
=DCount("[Client_ID]","sp_EthnicReport"," [Gender] = 'male' AND
[Ethnic ID] = '1'")

When i execute the sp_ethnicreport it works fine but when i enter it
into the control source (as dcount) of a report it doesn't. The reason
I want to use the sp and not the view is because the sp has date
parameters. So if i use the view instead it just brings all data
back.

My sp is:

SELECT Client_ID,[Quit Date], Quit, [Ethnic ID],Gender
FROM tbl_Client
where [quit date] between @s_date and @e_date
end

My view just selects the same fields as above from the tbl_client
table

This really is doing my head in, any help would be greatly appreciated

Rachel

 

[Sylvain Lafontaine]

You cannot use a sp with DCount because this function apply a server filter
on the domain to restrict its output and a server filter cannot be used
directly on a stored procedure (you can only if you are defining a linked
server and a view on this SP on the linked server.

For ADP, you should really use the standard ADO Command object to make such
calls. There have been numerous examples in the past in this newsgroup on
how to use a Command object from ADP:

http://groups.google.ca/group/micro...er/search?q=command+object&start=0&scoring=d&

 

[Sylvain Lafontaine]

I forgot to mention that you shouldn't use the "sp_" prefix; as it has a
special meaning for SQL-Server and should be reserved for system stored
procedures.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

You cannot use a sp with DCount because this function apply a server
filter on the domain to restrict its output and a server filter cannot be
used directly on a stored procedure (you can only if you are defining a
linked server and a view on this SP on the linked server.

For ADP, you should really use the standard ADO Command object to make
such calls. There have been numerous examples in the past in this
newsgroup on how to use a Command object from ADP:

http://groups.google.ca/group/micro...er/search?q=command+object&start=0&scoring=d&

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Hi,

I keep getting an error message in my adp report when I try to use a
stored procedure as the domain in a dcount formula , however when i
use a view instead it works fine

This is the dcount that works:
=DCount("[Client_ID]","vw_ClientDetails"," [Gender] = 'male' AND
[Ethnic ID] = '1'")
This is the one that doesn't:
=DCount("[Client_ID]","sp_EthnicReport"," [Gender] = 'male' AND
[Ethnic ID] = '1'")

When i execute the sp_ethnicreport it works fine but when i enter it
into the control source (as dcount) of a report it doesn't. The reason
I want to use the sp and not the view is because the sp has date
parameters. So if i use the view instead it just brings all data
back.

My sp is:

SELECT Client_ID,[Quit Date], Quit, [Ethnic ID],Gender
FROM tbl_Client
where [quit date] between @s_date and @e_date
end

My view just selects the same fields as above from the tbl_client
table

This really is doing my head in, any help would be greatly appreciated

Rachel

 

[Rachel]

You cannot use a sp with DCount because this function apply a server filter
on the domain to restrict its output and a server filter cannot be used
directly on a stored procedure (you can only if you are defining a linked
server and a view on this SP on the linked server.

For ADP, you should really use the standard ADO Command object to make such
calls.  There have been numerous examples in the past in this newsgroup on
how to use a Command object from ADP:

http://groups.google.ca/group/microsoft.public.access.adp.sqlserver/s...

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Hi,

I keep getting an error message in my adp report when I try to use a
stored procedure as the domain in a dcount formula , however when i
use a view instead it works fine

This is the dcount that works:
=DCount("[Client_ID]","vw_ClientDetails"," [Gender] = 'male' AND
[Ethnic ID] = '1'")
This is the one that doesn't:
=DCount("[Client_ID]","sp_EthnicReport"," [Gender] = 'male' AND
[Ethnic ID] = '1'")

When i execute the sp_ethnicreport it works fine but when i enter it
into the control source (as dcount) of a report it doesn't. The reason
I want to use the sp and not the view is because the sp has date
parameters. So if i use the view instead it just brings all data
back.

My sp is:

SELECT Client_ID,[Quit Date], Quit, [Ethnic ID],Gender
FROM        tbl_Client
where [quit date] between  @s_date and @e_date
end

My view just selects the same fields as above from the tbl_client
table

This really is doing my head in, any help would be greatly appreciated

Rachel- Hide quoted text -

- Show quoted text -

Thanks, I will give it a go
R