Automating MS Query

J

Jen

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
 
T

Tom Ogilvy

You can make your query a parameter query (in the MSQuery dialog, in the
menu go to Criteria - Add. ) In the values box for the date field, put in a
?

Now return the data to Excel. You should get a prompt for a date. Give it
a date.

When you are back in Excel, right click on the data and select parameter
from the Popup menu. Click the option button for Cell and then click in the
textbox, then put in = and click in the cell where you want to enter the
date.

Repeat for all you query tables. They can all refer to the same cell if
that is appropriate.
 
J

Jen

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?
 
G

Guest

I also tried editing the SQL directly (WHERE START_DATE
= ?) but I got the message

"Parameters are not allowed in queries that can't be
represented graphically"

HUH???

-----Original 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
.
Click to expand...
.
Click to expand...
 
T

Tom Ogilvy

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 said:
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
.
Click to expand...
Click to expand...
 
G

Guest

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

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
.
Click to expand...
Click to expand...


.
Click to expand...
 
T

Tom Ogilvy

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

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

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
.
Click to expand...


.
Click to expand...
Click to expand...
 
G

Guest

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

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

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
Click to expand...
to
go
in to each separate query (there are 12) to enter the
appropriate date range. I'm looking for ideas on
Click to expand...
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
.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Top