prompt for query refresh criteria

[Guest]

can i do something to get a popup for entering a date range for a query to
refresh with? I have a query and I often change the > date for it and then
refresh. I do this by
Data
Import External
Modify

and change the info in the SQL box.

is there a faster way...
would like pop up "Greater than? "

 

[taylorm]

Set up your SQL to prompt you for the Date by putting "[Enter Date]" i
the Value field of your Criteria box. When you run the query, a tex
box that says "Enter Date" will pop up for you to key the date.

Then when you get back to Excel, you can right-click inside the are
where the data is returned and choose the Parameters option. Fro
there you can tell MSQuery a location on the worksheet from where i
can retrieve the response to this query prompt.

After that, you simply change the Date parameter on the Excel workshee
itself prior to refreshing the MSQuery.

Hope this helps

 

[Guest]

thanks...
it's beautiful...
but it didn't work...

I believe I have a date format issue...
my table dates exist as 2005-10-25
When I write queries with excel I use single quotes. do I need to put the
quote in my critera field. Can I put the > sign in the criteria field as well?

 

[taylorm]

Assuming that the date that you showed as an example (2005-10-25) is
actually a text field and not a date field (because I don't think
that's a valid Access date format), the you need to format the
parameter cell on your spreadsheet as text and enter it exactly as you
had in your example (2005-10-25). I created a test database with the
date field being a text field and it worked for me.

Another option you have is to check the "Prompt for value using the
following string" option and it will pop up a text box for you to key
the date into when you Refresh the query. Again, you would need to
type it in that format (2005-10-25).