If you mean you would like to put blank lines in the list in between the
subtotal groups, I have never tried it but I think it might cause problems if
you need to "undo" or revise your spreadsheet. Both the list used to
calculate the subtotals and the query results depend on the data being in a
continuous range, no blank lines or columns. Inserting blanks might mess it
all up. I only can think of a couple of alternatives:
1) Instead of a blank line, maybe just change the row height at the end of
the group and format the cell so the text is at the top - it would create a
space without actually inserting a blank line.
2) copy the query results (with the subtotals) and paste them into a new
range, maybe a new sheet, as values (or linked cells) so you can do whatever
you like without changing the original list.
The thing I can't help with much is finding which rows the groups end on - I
don't use subtotals much and not at all in code, so I don't know how to find
the groups and summary lines in VBA - you would need someone else to help you
there, I'm afraid.

K Dales

"Joshua Campbell" wrote:

I thought it would be something along those lines. After I converted it to
text, and pointed the parameters to these new fields, everything seems to
work like it should.

One more question about this spreadsheet that I'm making. After the query
is run, I calculate subtotals using Data-Subtotals. Is there a way to add
a extra line after the subtotal, before the next group begins?

Thanks.
Joshua



"K Dales" wrote in message
...
When you use the DateValue or Date functions, the result is a date
variable,
even if you use formatting to change how it appears on the sheet. So my
guess is that MSQuery is pulling it in as a date number (e.g. today is
38454) and that is why you are not getting the results you want. You will
need to force the values to go to MSQuery as text in the format you want,
you
can modify your formulas as follows:
=TEXT(DATEVALUE(B2 & "/15/"&B1),"yyyymmdd")
and
=TEXT(DATE(B1,(B2)+1,0),"yyyymmdd")
Note that this will create an actual text value for the cell - and the
downside is that if you have other formulas that refer to these cells,
those
formulas will no longer recognize them as dates! If that is the case, you
will probably need to keep the formulas the way you have them, but make
two
new cells the StartDate and EndDate for your query parameters, and in
those
cells bring in the dates and convert them to text like so:
for StartDate (assuming your existing formula is in cell A1)
=TEXT(A1,"yyyymmdd")
For EndDate (assuming your existing formula for this is in B1)
= TEXT(B1,"yyyymmdd")
I hope this finally solves your troubles!
K Dales


"Joshua Campbell" wrote:

Okay, I've got one cell where the user enters in the year in yyyy
format. I
have another cell where the user enters the month in mm format. In all
actuality, the first cell is a cell with "0000" text formatting, and the
second is in "00" text formatting.

I then have another cell to calculate the start date. The formula for
it is
=DATEVALUE(B2 & "/15/"&B1)
I then have the cell in yyyymmdd text formatting.

The formula to calculate the end date is as follows
=DATE(B1,(B2)+1,0)
I also have this in yyyymmdd text formatting.

I created the database query. Using start and end dates that I
populated,
the query returns data as it should. I then changed the parameters to
pull
from the above two formulas. However, the result comes back empty. Any
ideas?

Thanks.
Joshua



"K Dales" wrote in message
...
Just read the original post more carefully, "yyyymm" format is what
you
want.

"Joshua Campbell" wrote:

Thanks for the great reply. But, I guess I should have been a bit
more
clear as to where I was running into problems.

When I put in the parameters, I always get an error. It is probably
how
I
am doing it. See the example of the criteria for invdate below:
Between '20050315' And [DateRange] & '31'

I am trying to concatenate the returned value from DateRange and 31.
How
would I do this?

Thanks.
Joshua



"K Dales" wrote in message
...
First, set up cells to contain the dates that will be selected
(hide
them
if
desired). User input can be either directly on the worksheet,
some
InputBoxes, or a UserForm - just store/copy the results in the
cells
you
set
aside.

Now, go to data menu, Get External Data, New Database Query. It
should
prompt you for the data source and I assume/hope your database
will be
one
listed there. Choose the database driver, the file path (if
needed)
and
the
userID/password (if needed) to connect to the database. MSQuery
will
then
start and you can set up the query in there, either graphically a
la
Access
or type in the SQL directly - see MSQuery help if you need it.
For
your
date
range, use parameters (which are specified by using [] in the
criteria
grid;
e.g. under SALESDATE you would put the following: Between [Date1]
and
[Date2]).

When done return the data to Excel (via File menu or toolbar). It
will
prompt for Date1 and Date2; for now you can put anything in there.
When
you
get back to Excel the data will appear in a list. Right-click on
the
list
and choose Properties, then Parameters. You will see your Date1
and
Date2
parameters - set them to "Get the value from the following cell:"
and
point
them to the cells you designated on your sheet for the date
values.

Now you just need code to prompt the user for the new dates and
refresh
the
query (which would be something like this:
Sheets("Sheet1").Querytables(1).Refresh)

This is a very brief outline, but hope it helps. Look in Excel
help
or
MSDN
library for more info if needed.

"Joshua Campbell" wrote:

I need to have Excel run a database querry, prompting the user
for
information.

The SQL that would be used is something a lot like this:
SELECT * FROM table18 WHERE (salesdate BETWEEN N'20050315' AND
N'20050331')

I need to prompt the user for the year and month, preferrably
with
one
prompt, asking for the data in YYYYMM format, and then append
the
day
number
to what is entered. How would I accomplish this?

Thank you.
Joshua