SQL prompt for input

Discussion in 'Microsoft Access Queries' started by Kenneth H. Young, Mar 15, 2004.

  1. 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
     
    Kenneth H. Young, Mar 15, 2004
    #1
    1. Advertisements

  2. 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, Mar 15, 2004
    #2
    1. Advertisements

  3. 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" <> wrote in message
    news:d7a701c40acf$24e9d9f0$...
    > 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
    > >
    > >
    > >.
    > >
     
    Kenneth H. Young, Mar 17, 2004
    #3
  4. 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" <>

    wrote in message
    >news:d7a701c40acf$24e9d9f0$...
    >> 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, Mar 17, 2004
    #4
  5. Kenneth H. Young

    Yuan Shao Guest

    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.
     
    Yuan Shao, Mar 18, 2004
    #5
  6. 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" <> wrote in message
    news:bf1f01c40c2e$dd62dfd0$...
    > 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" <>

    > wrote in message
    > >news:d7a701c40acf$24e9d9f0$...
    > >> 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
    > >> >
    > >> >
    > >> >.
    > >> >

    > >
    > >
    > >.
    > >
     
    Kenneth H. Young, Mar 18, 2004
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    0
    Views:
    329
    Guest
    Mar 18, 2004
  2. Guest

    Prompt User for Input to Query

    Guest, Apr 23, 2004, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    440
    Lynn Trapp
    Apr 23, 2004
  3. David Perkins

    Is Access SQL FROM Clause different from MS SQL Server SQL?

    David Perkins, Oct 20, 2004, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    407
    Tom Ellison
    Oct 20, 2004
  4. Guest
    Replies:
    2
    Views:
    312
    Guest
    Nov 2, 2004
  5. mcl
    Replies:
    1
    Views:
    534
    MGFoster
    Feb 28, 2008
Loading...

Share This Page