MS Graph, parameters, strored procedure

[hf]

Does anybody know how to pass parameters to the stored procedure (or in-line
function) that is used as row source for MS Graph in an Access Project form
???
There is no Input Parameter property on the MS Graph....
Thanks.

(e-mail address removed)

 

[Alex Dybenko]

Try to write in record source:

exec SP_Name Param1, Param2

 

[hf]

Unfortunately, it works only if I explicetely pass values to parameters, for example EXEC nameofstoredprocedure '01/01/2003','01/01/2006' etc...but I need to reference some controls from forms like EXEC nameofstoredprocedure @startdate=referencetocontrolfromform, @enddate=....etc. Of cource, it doesn't work, because you can't reference controls directly in query, as forms are placed on front end in Access Project, while query is executed in SQL Server...What is the solution for this problem ?

 

[Alex Dybenko]

Hi,
in this case you can construct record source at form load:

"EXEC nameofstoredprocedure '" &
format(referencetocontrolfromform,"yyyymmdd") & "'"

BTW - pls note that proper date format to pass to TSQL is 'yyyymmdd'

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


Unfortunately, it works only if I explicetely pass values to parameters, for
example EXEC nameofstoredprocedure '01/01/2003','01/01/2006' etc...but I
need to reference some controls from forms like EXEC nameofstoredprocedure
@startdate=referencetocontrolfromform, @enddate=....etc. Of cource, it
doesn't work, because you can't reference controls directly in query, as
forms are placed on front end in Access Project, while query is executed in
SQL Server...What is the solution for this problem ?

 

[Zlatko]

Hello.

I think that "FORMAT" is a function not recognized by SQL Server and that
should be replaced by "CONVERT "? Or, maybe, I didn't understood following
text from e-book "Access Projects with SQL Server":


SQL Server/MSDE does not have a FORMAT() function to format the
expression's output. To format your results, use the text and conversion
functions. In forms and reports you can also use the VBA formatting
instructions recognized by Access mdb databases.



Conversions Using CONVERT
The conversion function CONVERT is capable of more conversions than CAST. In
particular, the multiple possibilities for date and time conversions make
CONVERT indispensable, using this format:

CONVERT( data_type [ ( length ) ] , expression [ , style ] )Alex, can you
explain me whether I can use .mdb syntax when writing row source expression
that you proposed, or I have to use Transact SQL syntax ?What would be the
same expression using Convert instead of Format ? I also think that in
Access Project I can't use character & (see a table from e-book Access
Projects with SQL Server, below):
Table 16-5 SQL Server Counterparts for Characters and Operators
Description
Access
SQL Server

Concatenation operator
&
+

Date separator
#
'

Mod operator
mod
%

String separator
' or "
'

Wildcard for a character
?
_

Wildcard for several characters
*
%




SQL Server and MSDE use different wildcards from Access mdbs. Instead
of the asterisk (*) used by Access mdbs, SQL Server/MSDE uses a percent (%)
symbol and an underscore ( _ ) instead of a question mark (?). Refer to
Chapter 9, "Transact-SQL," for more information about variables and the LIKE
command.




Thanks.

 

[Alex Dybenko]

Hi Zlatko,

Yes, i case i mentioned - you have to write using Transact SQL syntax, all
SQL expressions in ADP should be on TSQL
regarding FORMAT() - there is no 100% replacement in TSQL. for date you can
use convert in most cases, else you have to write your own function

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Hello.

I think that "FORMAT" is a function not recognized by SQL Server and that
should be replaced by "CONVERT "? Or, maybe, I didn't understood following
text from e-book "Access Projects with SQL Server":


SQL Server/MSDE does not have a FORMAT() function to format the
expression's output. To format your results, use the text and conversion
functions. In forms and reports you can also use the VBA formatting
instructions recognized by Access mdb databases.



Conversions Using CONVERT
The conversion function CONVERT is capable of more conversions than CAST. In
particular, the multiple possibilities for date and time conversions make
CONVERT indispensable, using this format:

CONVERT( data_type [ ( length ) ] , expression [ , style ] )Alex, can you
explain me whether I can use .mdb syntax when writing row source expression
that you proposed, or I have to use Transact SQL syntax ?What would be the
same expression using Convert instead of Format ? I also think that in
Access Project I can't use character & (see a table from e-book Access
Projects with SQL Server, below):
Table 16-5 SQL Server Counterparts for Characters and Operators
Description
Access
SQL Server

Concatenation operator
&
+

Date separator
#
'

Mod operator
mod
%

String separator
' or "
'

Wildcard for a character
?
_

Wildcard for several characters
*
%




SQL Server and MSDE use different wildcards from Access mdbs. Instead
of the asterisk (*) used by Access mdbs, SQL Server/MSDE uses a percent (%)
symbol and an underscore ( _ ) instead of a question mark (?). Refer to
Chapter 9, "Transact-SQL," for more information about variables and the LIKE
command.




Thanks.

Hi,
in this case you can construct record source at form load:

"EXEC nameofstoredprocedure '" &
format(referencetocontrolfromform,"yyyymmdd") & "'"

BTW - pls note that proper date format to pass to TSQL is 'yyyymmdd'

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


Unfortunately, it works only if I explicetely pass values to parameters, for
example EXEC nameofstoredprocedure '01/01/2003','01/01/2006' etc...but I
need to reference some controls from forms like EXEC nameofstoredprocedure
@startdate=referencetocontrolfromform, @enddate=....etc. Of cource, it
doesn't work, because you can't reference controls directly in query, as
forms are placed on front end in Access Project, while query is executed in
SQL Server...What is the solution for this problem ?