Database query with parameters given in the sheet

[Ronny Louis]

Hi all,

I've created a new database query with inner joins and sorting, which gives
great results. But, there's only one thing I can't figure out on how to get
this thing working...

I'm using Office XP, and I try to make a sheet in which a user can specify a
"from" date and a "to" date, after which the query starts only in this
range. However, when I try to create the criteria, there's no way I can use
variables or named cells in any way.

Can anybody tell me a workaround for this matter?

Maybe this is something for Microsoft to pun in new versions in the future?

Kind regards,

Ronny Louis

P.S. Please respond in newsgroup, as Email is blocking all unknown
emailaddresses...

 

[Tom Ogilvy]

if you are building the string, you can insert variable values with a
construct like

vVar = "ef"
sStr = "abcd" & vVar & "ghij"

If you want the user prompted, you can use a parameter query. Don't know
how you are trying to access the data (ADO, DAO, ODBC, etc) so hard to say
anything specific.

 

[Ronny Louis]

Hi Tom,

This is how I create my new Database Query:

In excel :
- Filemenu : Data -> Import External Data -> New Database Query
- Choose Data Source : In here I select the DSN which I've added with
ODBC-Administrator which is using a Trusted Connection
- To make it simple I select a whole table in my query
- I leave the "Filter Data" empty
- Sort by t_item (to sort ascending on itemnumber)
- View data or edit query in Microsoft Query and I click on "Finish"
- I click on View -> SQL and see the following SQL-statement:
SELECT ttdssc010800.t_item, ttdssc010800.t_cuno, ttdssc010800.t_cpno,
ttdssc010800.t_dsca
FROM baandb.dbo.ttdssc010800 ttdssc010800
ORDER BY ttdssc010800.t_item
- I change this to be
SELECT ttdssc010800.t_item, ttdssc010800.t_cuno, ttdssc010800.t_cpno,
ttdssc010800.t_dsca
FROM baandb.dbo.ttdssc010800 ttdssc010800
WHERE ttdssc010800.t_item = ?parameter

And I get the following error message "Parameters are not allowed in queries
that can't be displayed graphically"

Any idea how to change this to be working correctly?

Thanks in advance,

Ronny Louis