Excel, ODBC query refresh with date range option

[ServerDude]

Hi All,

I have a spread sheetwith 7 Pivot Tables per sheet and about 10 sheets. It all works well but i would like to have a Macro that will allow me to input a date range then refresh all pivot tables to the same date range preferably by a nice user box that would pop up.

I am new to scripting so have been playing with the Macro recorder. I have a macro that will refresh 1 pivot table just to start with. If i edit the date in the macro to any day in the same month it is OK. If i change it to another month it fails with a;

Run-time ror '1004'

[Microsoft][ODBC Visual FoxPro Driver]Datetime field overflow

The Macro is;

Sub Query1()
'
' Query1 Macro
' Macro recorded 31/03/2011 by Jason Boull
'
'
Sheets("R004").Select
Range("AB1").Select
ActiveSheet.PivotTableWizard SourceType:=xlExternal, SourceData:=Array( _
"SELECT uthead.uh_account, uthead.uh_cashexp, uthead.uh_cash, uthead.uh_costprd, uthead.uh_cupchg, uthead.uh_date, uthead.uh_mref, uthead.uh_refund, uthead.uh_test, uthead.uh_visits, uthead.uh_oref, sn" _
, _
"ame.sn_name, uthead.uh_totstar, uthead.uh_totfin, umach.um_type" & Chr(13) & "" & Chr(10) & "FROM sname sname, umach umach, uthead uthead" & Chr(13) & "" & Chr(10) & "WHERE sname.sn_account = uthead.uh_account AND uthead.uh_mref = umach.um_ref AND ((uthead" _
, _
".uh_date>={d '2011-04-01'} And uthead.uh_date<={d '2011-04-31'}) AND (uthead.uh_account<>'CYM001'))" _
), Connection:=Array(Array( _
"ODBC;DSN=Vendman;UID=;;SourceDB=H:\Opera II V6\Data\Cymraeg\COMP_C.DBC;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machi" _
), Array("ne;Null=Yes;Deleted=Yes;"))
ActiveSheet.PivotTables("HDMData1").PivotFields("uh_oref").CurrentPage = "R004"
End Sub

I am happy with adding more pivot tables later i just need help with the user box and date range selection. If it is important, the applicable date field is "uthead.uh_date".

Thanks in advance.