Require user input in a Union query?

[Pfeni]

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

[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....

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).

[Pfeni]

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....

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)

[null]

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...

Select  @startdate =
        (Select [Please enter start date]);

[Pfeni]

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...

[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

[Pfeni]

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:

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)

Select @startdate = [Please enter startdate: ]

But that gave me the error message you can read above.

[r937]

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

[Pfeni]

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

[Pfeni]

Same help with a Pass-Through query is appreciated...

[r937]

" ... where the dates are stored in the first record from a user"

sounds like you need a join

[Pfeni]

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?

[r937]

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

[Pfeni]

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

Declare @startdate = '20040101 00:00:00'

and simply exchange that row with a new one?

... currently very confused.