SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Require user input in a Union query?

    How do you go about if you want to be able to acquire user input in a direct union query in Access? I have a code that follows:

    BEGIN
    SET NOCOUNT ON
    DECLARE @startdate varchar(20)

    SELECT @startdate = '20030401 00:00:00'

    If I want to be able to change the startdate according to the wishes of a user, how do I do that?

    /Pfeni

  2. #2
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In your query use brackets around the value you want the user to enter...a form should pop up that will take the users input....try this....
    Code:
    Begin
    set     noCount ON
    Declare E @startdate varchar(20)
    
    Select  @startdate = [Please enter start date];
    ...for the date though, I'm not quite sure how to get the user to enter the format you'd like (ie May 5 2003 vs 5/05/03 vs 2003-05-05 00:00:00.000).

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by null
    In your query use brackets around the value you want the user to enter...a form should pop up that will take the users input....try this....
    Code:
    Begin
    set     noCount ON
    Declare E @startdate varchar(20)
    
    Select  @startdate = [Please enter start date];
    Thanks for your reply!

    Although I already tried that... apparently that doesn't work in a direct union query (checking up tables on a network SQL database server). It works in other types of queries, like for example a Select query. Other suggestions? I get an erro message like this when I write as you suggested:

    ODBC: Call failed

    [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Please enter start date'. (#207)

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmmm, kinda confused...let me also throw this in though...

    Since the parameter query will only work w/a Select query, maybe nest a select query? Just throwing the idea out there...
    Code:
    Select  @startdate =
            (Select [Please enter start date]);

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    Select  @startdate =
            (Select [Please enter start date]);
    Same error as above... this was a though one!

    Have you worked any with Union queries that works directly to an SQL database? I could give you the whole code but we are talking 3 pages code here...

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    can you please clarify -- which database is the Access database, and is there some other database also involved?

    note:
    SQL = the language used by all databases
    SQL Server = a specific microsoft database
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    can you please clarify -- which database is the Access database, and is there some other database also involved?

    note:
    SQL = the language used by all databases
    SQL Server = a specific microsoft database
    Sorry about that, my union query is for an SQL server over our corporate network. The query creates two selection tables on the network SQL server. In the query there is two variables startdate and enddate, they are declared and selected in the query like this:
    Code:
    BEGIN
    SET NOCOUNT ON
    DECLARE @startdate varchar(20)
    DECLARE @enddate varchar(20)
     
    -- variabel data
    SELECT @startdate = '20040101 00:00:00'
    SELECT @enddate = '20040331 00:00:00'
    @startdate and @enddate is then later used to define the range of which records to be shown. But my problem is this. The dates on the records is not one of the columns presented in the result, but used temporarily on to the network SQL server when the query is runned. So I can't do a Select query based on the Union query to filter the records. Of course it could work if I included all the records ever created in the union query temporary databases, but then that file would be to big. That's why I want to be able to input the value myself. It works fine on a select query based on a linked table, but NOT on a union query asked directly to the SQL server. I thought I could write as suggested previously (as can be done in a select query)
    Code:
    Select @startdate = [Please enter startdate: ]
    But that gave me the error message you can read above.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yeah, you can't use Access prompting in a SQL Server query

    but you did say "How do you go about if you want to be able to acquire user input in a direct union query in Access?"

    that's why the confusion, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    yeah, you can't use Access prompting in a SQL Server query
    I figured that much, but how can I work around the problem? I thought I could create a local table where a user could input a startdate and an enddate and then in the union query declare the varibles to be linked to my local table. Say for example I create a table call wdatelocal and give this table two columns startdate and enddate, where the dates are stored in the first record from a user. How can I acquire theese data in my Union query??

    /Pfeni

  10. #10
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Same help with a Pass-Through query is appreciated...

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    " ... where the dates are stored in the first record from a user"

    sounds like you need a join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    sounds like you need a join
    The link on the query is an ODBC type, so it links to a network SQL server and retrieves its data from there. How do I link (with JOIN) a temporary existing selection criteria with a local table? It would be easier if the startdate and enddate was presented in the result of the query, then I just could do a selection query from the pass-through query... unfortunatly it isn't. How can I access my local tables in a ODBC linked query?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    if one table is local and the other is linked, any join query will be executed locally, in the jet engine

    there's no guarantee that the jet engine will read the local table and then use its date range test to filter the rows from the linked table

    it might just as easily return all linked rows first, and then attempt to do the join

    i think you need to find a way to place the date range test into a query which retrieves only from the linked table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Sweden
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    i think you need to find a way to place the date range test into a query which retrieves only from the linked table
    Interesting... if I actually had the link under Tables, in my Access database. The pass-through query creates two temporary dbs on the SQL server, and retrieves it's data from thoose. These dbs are erased afterwards.

    Let me try to explain a little more about the record layout. I'm working with books with a unique id nr, different actions can happen to one and the same id nr. Like i.e an order is placed on that book, an inventory is made on that book, the book is shipped and so on and so forth. The times and dates of theese actions are stored in the same place, call it whappeninglog.dttime. So what I want is to filter actions happening from a startdate and enddate.

    I was a little weary and said to my co-worker, why don't we just include whappeninglog.dttime as a column in the result... but since the result present one row/book and the whappeninglog.dttime can include 1000 of actions happening to that id nr, it would be undesired. The SQL question already filters the actions to a DECLARED startdate and enddate, with I must say a very, very long algorithm... I just want to alter the declared dates in the Pass-through query...

    I read somwhere that this can be done with a TSQL string that will promt the user... how is that done... can you somehow exchange a string in a Pass-through query (on an SQL level), can I search and find say

    Code:
    Declare @startdate = '20040101 00:00:00'
    and simply exchange that row with a new one?

    ... currently very confused.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •