User-defined dates in an ADO Access to Excel import?

[Sugabat]

Hi,

I've tacked together a macro to extract data from multiple tables in an Access db into excel using multiple plain text SQL queries. As the required dates will change each time I run this i'd like to use an InputBox or somesuch to call in the dates into this query - instead of manually pasting them in. Anyone know how I can do this? I've tried using the InputBox and call functions in VBA but am thoroughly stuck.

Thanks for your help!

Joe.

Here's a single qry sample of the VBA i'm using for illustration:

Public Sub ImportDKeysFromAccess()
'Imports data from Access db using the specified SQL queries

Dim rsData As ADODB.Recordset
Dim lOffset As Long
Dim szConnect As String
Dim szSQL As String
Dim objField As ADODB.Field

'Connection string
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=\\...;"

'Imports CO2 store info, 34 stores <<< IMPORTANT: Change the date to capture the most recent 7 or 8 days >>>
szSQL = "SELECT DailyKeysNew.Date AS Expr1, Sum(DailyKeysNew.[Store Ct]) AS StoreNum, Avg([DailyKeysNew.CY Avg]) AS CO2Sales, Avg([DailyKeysNew.PY Avg]) AS PYCO2SalesAvg, Avg([DailyKeysNew.CY Ord Ct]) AS CO2Orders, Sum([DailyKeysNew.CY Ord Ct]) AS CYOrd, Avg([DailyKeysNew.PY Ord Ct]) AS PYOrders, Sum([DailyKeysNew.PY Ord Ct]) AS PYOrd, (CYOrd-PYOrd)/PYOrd AS CO2PCYA, Sum([DailyKeysNew.CY Avg]) AS CYavg, CYavg/CYOrd AS CO2ATS, Sum([DailyKeysNew.PY Avg]) AS PYavg, PYavg/PYOrd AS PYATS, (CO2ATS-PYATS)/PYATS AS CO2ATPCYA " & _
"FROM DailyKeysNew " & _
"WHERE (((DailyKeysNew.Store) In ('7568','7569','7600','7601','7602','7608','7612','7620','7642','7643','7647','7649','7653','7656','7657','7658','7659','7661','7662','7663','7667','7668','7674','7677','7693','7559','7607','7619','7679','7566','7574','7640','7645','7688'))) " & _
"GROUP BY DailyKeysNew.Date " & _
"HAVING (((DailyKeysNew.Date) >= #8/7/2006#)) " & _
"ORDER BY DailyKeysNew.Date DESC"

Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

If Not rsData.EOF Then
With Sheet1.Range("A1")
For Each objField In rsData.Fields
.Offset(0, lOffset).Value = objField.Name
lOffset = lOffset + 1
Next objField
.Resize(1, rsData.Fields.Count).Font.Bold = True
End With

Sheet1.Range("A2").CopyFromRecordset rsData

Else
MsgBox "Error: No records returned.", vbCritical
End If

rsData.Close
Set rsData = Nothing


End Sub

 

[SkipVought]

Hi,

I often do something like this. This calculated the current week's MONDAY date...

HAVING (((DailyKeysNew.Date) >= [b]INT((Date-2)/7)*7+2[/b]  ))

Skip,

_[red][/red]