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

  • Thread starter Thread starter Rachel
  • Start date Start date
R

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
 
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&
 
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)


Sylvain Lafontaine said:
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)


Rachel said:
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
Click to expand...
Click to expand...
 
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,
Click to expand...
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
Click to expand...
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'")
Click to expand...
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.
Click to expand...
My sp is:
Click to expand...
SELECT Client_ID,[Quit Date], Quit, [Ethnic ID],Gender
FROM        tbl_Client
where [quit date] between  @s_date and @e_date
end
Click to expand...
My view just selects the same fields as above from the tbl_client
table
Click to expand...
This really is doing my head in, any help would be greatly appreciated
Click to expand...
Rachel- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks, I will give it a go
R
 
Back
Top