Parameter Query

[Guest]

I’m using sql 2000 and Access 2003. Ok, in Access to create a Parameter Query
you simply type in the fields criteria something like: [Enter a Trip No] and
you’re prompted to enter a Trip No.

With sql I’m trying to create the same type of Parameter Query that will ask
the user to input a Trip No. I’ve looked and tried everything to get this to
work without any luck.

What do I need to do to get this to work?

Thanks,

Paul

 

[Sylvain Lafontaine]

In what context do you want to use this Parameter Query? With SQL-Server,
queries are running on the SQL-Server side and at this stage, it's too late
for asking for the value of a parameter. So if you are using an EXECUTE
commands or an ADO object or something like that, this won't work.

However, if you use a Stored Procedure with parameters (and no default
value) as the source of a bound form and you don't explicit these parameters
in the InputParameters property, Access will ask for these values before
sending the request to the SQL-Server.

Also, if you use something like « select * from Organismes where IdOrganisme
= ? or IdOrganisme = ? » as the Record Source and « [Enter first ID], [Enter
second ID] » as the Input Parameters property, Access will ask for these ID
and replace ? with them.

Of course, a better course of action would be to link these input parameters
to controls on your form. For exemple, with a stored procedure with one
parameter of type int that you want to be tied to a text box on the form and
one string parameter that you want to be prompted for, you could write for
the InputParameters property:

IdOrganisme int = Forms!MyForm!TextBox_IdOrganisme, IdText char = [Enter the
text value]