PC Review


Reply
Thread Tools Rate Thread

SQL prompt for input

 
 
Kenneth H. Young
Guest
Posts: n/a
 
      15th Mar 2004
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


 
Reply With Quote
 
 
 
 
Kevin Sprinkel
Guest
Posts: n/a
 
      15th Mar 2004
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
>
>
>.
>

 
Reply With Quote
 
 
 
 
Kenneth H. Young
Guest
Posts: n/a
 
      17th Mar 2004
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.


"Kevin Sprinkel" <(E-Mail Removed)> wrote in message
news:d7a701c40acf$24e9d9f0$(E-Mail Removed)...
> 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
> >
> >
> >.
> >



 
Reply With Quote
 
Kevin Sprinkel
Guest
Posts: n/a
 
      17th Mar 2004
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.
>
>
>"Kevin Sprinkel" <(E-Mail Removed)>

wrote in message
>news:d7a701c40acf$24e9d9f0$(E-Mail Removed)...
>> 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
>> >
>> >
>> >.
>> >

>
>
>.
>

 
Reply With Quote
 
Yuan Shao
Guest
Posts: n/a
 
      18th Mar 2004
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.

 
Reply With Quote
 
Kenneth H. Young
Guest
Posts: n/a
 
      18th Mar 2004
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_.



"Kevin Sprinkel" <(E-Mail Removed)> wrote in message
news:bf1f01c40c2e$dd62dfd0$(E-Mail Removed)...
> 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.
> >
> >
> >"Kevin Sprinkel" <(E-Mail Removed)>

> wrote in message
> >news:d7a701c40acf$24e9d9f0$(E-Mail Removed)...
> >> 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
> >> >
> >> >
> >> >.
> >> >

> >
> >
> >.
> >



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Is it possible to prompt for input and then use that input in pass through query? mcl Microsoft Access Queries 1 28th Feb 2008 06:13 AM
Prompt user for input and utilize that input =?Utf-8?B?bmlubmVy?= Microsoft Excel Worksheet Functions 2 28th Mar 2007 09:44 PM
Access time range query w/user input prompt and SQL datasource =?Utf-8?B?TVJGYW5ub24=?= Microsoft Access Queries 2 2nd Nov 2004 04:30 PM
Query skips input for Where clause if second table or query added or if prompt contains blanks in the prompt string =?Utf-8?B?SHVudGVy?= Microsoft Access Queries 0 18th Mar 2004 11:01 PM
Login prompt after prompt ms newsgrp Microsoft Windows 2000 File System 0 19th Jul 2003 06:58 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:46 PM.