Posted to microsoft.public.excel.programming
|
|
Automating MS Query
Acutally, Dick's website (the link he posted) talked about
editing the SQL code, but, in any event, it doesn't seem
to work when the original query was built using the
Wizard. I had to rebuild the query directly in MS Query.
Thanks for the ideas!
-----Original Message-----
No one suggested editing the SQL code, but here is a
sample of querying the
NorthWind Employee table with a parameter query:
SELECT Employees.EmployeeID, Employees.LastName,
Employees.FirstName,
Employees.Title, Employees.TitleOfCourtesy,
Employees.BirthDate,
Employees.HireDate, Employees.Address, Employees.City,
Employees.Region,
Employees.PostalCode, Employees.Country,
Employees.HomePhone,
Employees.Extension, Employees.Photo, Employees.Notes,
Employees.ReportsTo
FROM `C:\Program Files\Microsoft
Office\Office\Samples\Northwind`.Employees
Employees
WHERE (Employees.EmployeeID=?)
Notice the Question mark in the Where clause.
To tie it to a cell, execute the query, go back to the
worksheet, right
click on the data table and select parameter. Click the
last box and
designate a cell that contains the value to query on
=Sheet1!$E$5
for example.
You can also check the box to refresh the query whenever
the value of the
cell changes.
Anyway, this works fine for me (xl2000).
--
Regards,
Tom Ogilvy
wrote in message
...
I'm not coding in VB, I'm using MS Query. When I built
the queries, I used the Get External Data/New Database
Query tool. Now when I want to edit the query, I right
click and say Edit Query. That's where I was trying to
edit the SQL code.
-----Original Message-----
It sounds like you are trying to do this in code rather
than manually as was
described.
In code, you wouldn't use a parameter query. You would
just use string
concatenation to put the appropriate value in the SQL
string.
Pseudo code:
sStr = "Select myTable.Field1 from MyTable Where
myTable.Field2 = " &
Worksheets("Sheet1").Range("A1").Value
--
Regards,
Tom Ogilvy
"Jen" wrote in
message
...
Thanks for the quick responses!
Unfortunately, neither of these ideas worked. When I
tried the ?, I got a syntax error message. When I
tried
the [enter date] idea, I got "invalid column
name 'enter
date'. I feel like I'm missing something stupidly
obvious. Any suggestions?
-----Original Message-----
Hello,
I have an Excel spreadsheet that uses MS Query to
pull
data from a SQL database. Each month the user has
to
go
in to each separate query (there are 12) to enter
the
appropriate date range. I'm looking for ideas on
how
to
automate this. Either have a pop-up box where they
enter
the date one time, or somehow have the queries
refresh
with the most current month end. I need some ideas
on
where to even begin. I know a little VBA, but only
from
recording my macros and then editing the code. I've
never
coded from scratch.
Any ideas would be great!
Thanks!
Jen
.
.
.
|