how do you use a function in a view (access project / adp)

[Bob]

running access 2k adp/project as front-end; and sql-server 7 as back-
end.

I would like to create my own special function that I can then
reference from a view.

how do I do this?
a function defined in a module, is apparently, not usable from a view.

ie:
public function myFn( x as integer )
myFn = x * 3.141 + 212
end function

select *, myFn( [myTable.xvalue] ) as xAnswer from myTable


TIA

 

[Duane Hookom]

Create the function in SQL Server.

 

[Daryl S]

Bob -

To return a value from a function, you need to specify the type of value
returned in the call (I chose Double in the example below):

public function myFn( x as integer ) As Double
myFn = x * 3.141 + 212
end function

 

[Bob]

Create the function in SQL Server.

--
Duane Hookom
Microsoft Access MVP

running access 2k adp/project as front-end; and sql-server 7 as back-
end.

I would like to create my own special function that I can then
reference from a view.

how do I do this?
a function defined in a module, is apparently, not usable from a view.

ie:
public function myFn( x as integer )
myFn = x * 3.141 + 212
end function

select *, myFn( [myTable.xvalue] ) as xAnswer from myTable

TIA
.

TX to both Duane & Daryl for your replies...
only 1 problem with doing a function - sql 7 does not support
functions...

I was hoping there might be some way to emulate this with maybe
procedures or some other method; but I haven't been able to figure out
how....

in order to use procedures, I would need to use an exec stmt within a
select in a view, and I'm not sure that can be done.

Bob

 

[Duane Hookom]

Can't you use something like:

select *, xvalue * 3.141 + 212 as xAnswer from myTable

--
Duane Hookom
Microsoft Access MVP

Create the function in SQL Server.

--
Duane Hookom
Microsoft Access MVP

running access 2k adp/project as front-end; and sql-server 7 as back-
end.

I would like to create my own special function that I can then
reference from a view.

how do I do this?
a function defined in a module, is apparently, not usable from a view.

ie:
public function myFn( x as integer )
myFn = x * 3.141 + 212
end function

select *, myFn( [myTable.xvalue] ) as xAnswer from myTable

TIA
.

TX to both Duane & Daryl for your replies...
only 1 problem with doing a function - sql 7 does not support
functions...

I was hoping there might be some way to emulate this with maybe
procedures or some other method; but I haven't been able to figure out
how....

in order to use procedures, I would need to use an exec stmt within a
select in a view, and I'm not sure that can be done.

Bob
.

 

[Bob]

absolutely.

that technique works just fine.
problem is - the actual calculation is VERY complex, and is used in
multiple views.
so maintaining it individually in several places would be a nightmare.
additionally, because of the complexity and length of the calculation
- what I would like to do in 1 view, must be spread across many views
- it's a real mess.

a function-like solution is the only one that makes any sense.

and I think that all comes down to whether or not procedural exec's
can be used within select statements....
unless there's another approach I haven't thought of yet....

TX again...

Can't you use something like:

select *, xvalue * 3.141 + 212 as xAnswer from myTable

--
Duane Hookom
Microsoft Access MVP

Create the function in SQL Server.
--
Duane Hookom
Microsoft Access MVP
:
running access 2k adp/project as front-end; and sql-server 7 as back-
end.
I would like to create my own special function that I can then
reference from a view.
how do I do this?
a function defined in a module, is apparently, not usable from a view.
ie:
public function myFn( x as integer )
myFn = x * 3.141 + 212
end function
select *, myFn( [myTable.xvalue] ) as xAnswer from myTable
TIA
.

TX to both Duane & Daryl for your replies...
only 1 problem with doing a function - sql 7 does not support
functions...

I was hoping there might be some way to emulate this with maybe
procedures or some other method; but I haven't been able to figure out
how....

in order to use procedures, I would need to use an exec stmt within a
select in a view, and I'm not sure that can be done.

Bob
.

 

[Bob]

absolutely.

that technique works just fine.
problem is - the actual calculation is VERY complex, and is used in
multiple views.
so maintaining it individually in several places would be a nightmare.
additionally, because of the complexity and length of the calculation
- what I would like to do in 1 view, must be spread across many views
- it's a real mess.

a function-like solution is the only one that makes any sense.

and I think that all comes down to whether or not procedural exec's
can be used within select statements....
unless there's another approach I haven't thought of yet....

TX again...

Can't you use something like:

select *, xvalue * 3.141 + 212 as xAnswer from myTable

--
Duane Hookom
Microsoft Access MVP

Create the function in SQL Server.
--
Duane Hookom
Microsoft Access MVP
:
running access 2k adp/project as front-end; and sql-server 7 as back-
end.
I would like to create my own special function that I can then
reference from a view.
how do I do this?
a function defined in a module, is apparently, not usable from a view.
ie:
public function myFn( x as integer )
myFn = x * 3.141 + 212
end function
select *, myFn( [myTable.xvalue] ) as xAnswer from myTable
TIA
.

TX to both Duane & Daryl for your replies...
only 1 problem with doing a function - sql 7 does not support
functions...

I was hoping there might be some way to emulate this with maybe
procedures or some other method; but I haven't been able to figure out
how....

in order to use procedures, I would need to use an exec stmt within a
select in a view, and I'm not sure that can be done.

Bob
.

 

[Duane Hookom]

I'm out of suggestions since this is an adp connected to an older version of
SQL Server :-(
--
Duane Hookom
Microsoft Access MVP

absolutely.

that technique works just fine.
problem is - the actual calculation is VERY complex, and is used in
multiple views.
so maintaining it individually in several places would be a nightmare.
additionally, because of the complexity and length of the calculation
- what I would like to do in 1 view, must be spread across many views
- it's a real mess.

a function-like solution is the only one that makes any sense.

and I think that all comes down to whether or not procedural exec's
can be used within select statements....
unless there's another approach I haven't thought of yet....

TX again...

Can't you use something like:

select *, xvalue * 3.141 + 212 as xAnswer from myTable

--
Duane Hookom
Microsoft Access MVP

On Nov 19, 12:56 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
Create the function in SQL Server.

:
running access 2k adp/project as front-end; and sql-server 7 as back-
end.

I would like to create my own special function that I can then
reference from a view.

how do I do this?
a function defined in a module, is apparently, not usable from a view.

ie:
public function myFn( x as integer )
myFn = x * 3.141 + 212
end function

select *, myFn( [myTable.xvalue] ) as xAnswer from myTable

TIA
.

TX to both Duane & Daryl for your replies...
only 1 problem with doing a function - sql 7 does not support
functions...

I was hoping there might be some way to emulate this with maybe
procedures or some other method; but I haven't been able to figure out
how....

in order to use procedures, I would need to use an exec stmt within a
select in a view, and I'm not sure that can be done.

Bob
.

.