Call Sub-query with Parameter

[shapper]

Hello,

Is it possible in Microsoft Access 2007 to call a sub-query and
sending its parameter?

Sub-query (UsersByType):
SELECT *
FROM USERS
WHERE USERS.TYPE=[@UserType];

And now on the query I want to call this table as follows:
SELECT *
FROM UsersByType AS ubt
WHERE ubt.UserType = "Administrator";

Obviously this does not work.

I tried other options like:
SELECT *
FROM UsersByType AS ubt("Administrator");

This does not work either.

Does anyone knows if this is possible in Microsoft Access 2007?

Thank You,
Miguel

 

[MGFoster]

Hello,

Is it possible in Microsoft Access 2007 to call a sub-query and
sending its parameter?

Sub-query (UsersByType):
SELECT *
FROM USERS
WHERE USERS.TYPE=[@UserType];

And now on the query I want to call this table as follows:
SELECT *
FROM UsersByType AS ubt
WHERE ubt.UserType = "Administrator";

Obviously this does not work.

I tried other options like:
SELECT *
FROM UsersByType AS ubt("Administrator");

This does not work either.

Does anyone knows if this is possible in Microsoft Access 2007?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're using an SQL Server parameter type (prefixed with @). Are you
using SQL Server as a back end (data tables)? If so you can use a
function in place of your so-called "sub-query": you are using it as a
derived table.

Ex:

CREATE FUNCTION dbo.UsersByType (@UserType VARCHAR(30))
RETURNS TABLE
AS
RETURN ( SELECT *
FROM USERS
WHERE USERS.TYPE = @UserType )

Then use it in your query like this:

SELECT *
FROM dbo.UsersByType("Administrator")

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSfo6V4echKqOuFEgEQLe2ACfalW2H9Mi+11A0gRC8idwcVIXC6YAnRBl
tfHqSVeLsQG/wb+zFf2Zfk/x
=MKpm
-----END PGP SIGNATURE-----

 

[shapper]

You're using an SQL Server parameter type (prefixed with @).  Are you
using SQL Server as a back end (data tables)?  

I am using Microsoft Access 2007.
When @ is used the parameter value is asked.
Am I missing something?

I need to make this in Access not in SQL Server.

 

[MGFoster]

I am using Microsoft Access 2007.
When @ is used the parameter value is asked.
Am I missing something?

I need to make this in Access not in SQL Server.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't have Acc2007, so I don't know if that is a new feature. In the
past the "@" was only used by SQL Server. Access query parameters did
not have that prefix.

Access can use SQL Server or JET (Joint Engine Technology) as the
database engine (the back-end [BE] where the tables are). You can tell
if Access is using SQL Server as part of an "Access Project" by the file
extension (the letters after the period on the file name). What is your
file's extension?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSf0YUoechKqOuFEgEQLSwgCg3+Bk4FdQysg3JbYJT+rJQIhgZg0An2kq
8gD5SpGhylBZ21lxoAwK9XL/
=Hb8P
-----END PGP SIGNATURE-----