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

Jen

Have a look here

http://www.dicks-clicks.com/excel/Ex...htm#Parameters

and here

http://www.dicks-clicks.com/excel/Ex....htm#ChangeSQL

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Jen" wrote in 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

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.

--
Regards,
Tom Ogilvy

"Jen" wrote in 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

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
.

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
.

.

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
.

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
.



.

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
.



.

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
.



.



.