SQL prompt for input

[Kenneth H. Young]

How do I make an SQL query as such

SELECT HardwareID, DeviceName, HardwareAddress, DevBldgNumber,
DevRoomNumber
FROM dbo.Hardware

prompt the user for the 'devicename'?

I am using Access 2003 and the database is on a SQL 2000 server.

Thanks

 

[Kevin Sprinkel]

Two approaches:

1. A parameter query.
SELECT HardwareID, [Enter Device Name] AS DeviceName,
HardwareAddress, DevBldgNumber, DevRoomNumber
FROM dbo.Hardware

2. Display an unbound form with a combo box whose
rowsource is set to the table of device names. Include a
command button to open the query. In the query's Criteria
row, for the DeviceName field, specify the criteria:

=Forms!yourunboundformname!yourcomboboxname

HTH
Kevin Sprinkel

 

[Kenneth H. Young]

Kevin I can't seem to get this to work.

SELECT HardwareID, [Enter Device Name] AS DeviceName,
HardwareAddress, DevBldgNumber, DevRoomNumber
FROM dbo.Hardware

This is a .adp project running off of a SQL 2000 server. When I add the
above I get an erro (ADO error: Invalid column name 'Enter Device Name').

Thanks for the assistance.

Two approaches:

1. A parameter query.
SELECT HardwareID, [Enter Device Name] AS DeviceName,
HardwareAddress, DevBldgNumber, DevRoomNumber
FROM dbo.Hardware

2. Display an unbound form with a combo box whose
rowsource is set to the table of device names. Include a
command button to open the query. In the query's Criteria
row, for the DeviceName field, specify the criteria:

=Forms!yourunboundformname!yourcomboboxname

HTH
Kevin Sprinkel

-----Original Message-----
How do I make an SQL query as such

SELECT HardwareID, DeviceName, HardwareAddress, DevBldgNumber, DevRoomNumber
FROM dbo.Hardware

prompt the user for the 'devicename'?

I am using Access 2003 and the database is on a SQL 2000 server.

Thanks


.

 

[Kevin Sprinkel]

Sorry I couldn't help. This must be unique to Access' SQL
implementation. Perhaps you can repost.

Good luck.

-----Original Message-----
Kevin I can't seem to get this to work.

SELECT HardwareID, [Enter Device Name] AS DeviceName,
HardwareAddress, DevBldgNumber, DevRoomNumber
FROM dbo.Hardware

This is a .adp project running off of a SQL 2000 server. When I add the
above I get an erro (ADO error: Invalid column name 'Enter Device Name').

Thanks for the assistance.

Two approaches:

1. A parameter query.
SELECT HardwareID, [Enter Device Name] AS DeviceName,
HardwareAddress, DevBldgNumber, DevRoomNumber
FROM dbo.Hardware

2. Display an unbound form with a combo box whose
rowsource is set to the table of device names. Include a
command button to open the query. In the query's Criteria
row, for the DeviceName field, specify the criteria:

=Forms!yourunboundformname!yourcomboboxname

HTH
Kevin Sprinkel

-----Original Message-----
How do I make an SQL query as such

SELECT HardwareID, DeviceName, HardwareAddress, DevBldgNumber, DevRoomNumber
FROM dbo.Hardware

prompt the user for the 'devicename'?

I am using Access 2003 and the database is on a SQL

2000
server.

Thanks


.

.

 

[Yuan Shao]

Hello Kenneth,

I learned that you want to prompt the user to input the parameters for the
parameter query in Access Project. Based on my experience, you can create a
stored procedure with parameters in Access Project to meet your
requirements.

For example:

ALTER procedure testPro
@CusotmerID varchar(20)
as
select * from customers
where CustomerId = @CusotmerID
return

When you double click this Stored Procedure, the prompt dialog will pop-up
and you can get the required recordset after inputting the parameter.

I am looking forward to hearing from you soon.
Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Kenneth H. Young]

OK I have figured it out by examining some of the queries in the
Northwind SQL database (I guess I should have done this research first).
The query must be created as a 'Stored Procedure' then it works.

Thanks for the assistance!

SELECT HardwareID, DeviceName, DevBldgNumber, DevRoomNumber
FROM dbo.Hardware
WHERE (DeviceName = @Enter_Device_Name)

NOTE: No spaces are allowed use underscores_.

Sorry I couldn't help. This must be unique to Access' SQL
implementation. Perhaps you can repost.

Good luck.

-----Original Message-----
Kevin I can't seem to get this to work.

SELECT HardwareID, [Enter Device Name] AS DeviceName,
HardwareAddress, DevBldgNumber, DevRoomNumber
FROM dbo.Hardware

This is a .adp project running off of a SQL 2000 server. When I add the
above I get an erro (ADO error: Invalid column name 'Enter Device Name').

Thanks for the assistance.

Two approaches:

1. A parameter query.
SELECT HardwareID, [Enter Device Name] AS DeviceName,
HardwareAddress, DevBldgNumber, DevRoomNumber
FROM dbo.Hardware

2. Display an unbound form with a combo box whose
rowsource is set to the table of device names. Include a
command button to open the query. In the query's Criteria
row, for the DeviceName field, specify the criteria:

=Forms!yourunboundformname!yourcomboboxname

HTH
Kevin Sprinkel

-----Original Message-----
How do I make an SQL query as such

SELECT HardwareID, DeviceName, HardwareAddress,
DevBldgNumber, DevRoomNumber
FROM dbo.Hardware

prompt the user for the 'devicename'?

I am using Access 2003 and the database is on a SQL 2000
server.

Thanks


.

.