prompt for query refresh criteria

[jenn]

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]" in the Value field of your Criteria box. When you run the query, a text 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 area where the data is returned and choose the Parameters option. From there you can tell MSQuery a location on the worksheet from where it can retrieve the response to this query prompt.

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

Hope this helps.

[jenn]

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" wrote:

>
> Set up your SQL to prompt you for the Date by putting "[Enter Date]" in
> the Value field of your Criteria box. When you run the query, a text
> 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 area
> where the data is returned and choose the Parameters option. From
> there you can tell MSQuery a location on the worksheet from where it
> can retrieve the response to this query prompt.
>
> After that, you simply change the Date parameter on the Excel worksheet
> itself prior to refreshing the MSQuery.
>
> Hope this helps.
>
>
> --
> taylorm
> ------------------------------------------------------------------------
> taylorm's Profile: http://www.excelforum.com/member.php...o&userid=28892
> View this thread: http://www.excelforum.com/showthread...hreadid=546838
>
>

[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).