VBA/SQL date variable problem
 

Hello,


I want to write some code which


a) looks at a list of dates in Excel then determines the date of the
next day
b) uses the result of a) to refresh some queries
c) performs calculations on the result of b) and returns data to a
specified sheet


I have no problem with a) and c) I am struggling with the SQL part of
b).


When I run the code I get an Runtime error 13 - type mismatch, so I
guess the format of the variable is not correct. I have tried declaring



Nextdate (my variable) as a string and a date with a format type of
that given when I use a macro recording to generate the code.


Here is the macro generated SQL which runs:
With Selection.QueryTable
Criteria & "WHERE (MI_STATISTIC.ST_DATE={ts '2005-08-13 00:00:" _
, _
"00'}) AND More Criteria
.Refresh BackgroundQuery:=False
End With


Here is what I have edited it to with VB dim and Format statements
which doesn't run:
Dim Nextdate As Date
Dim Nonblank As Long


Sheets("Graphs").Select
Nonblank = Application.WorksheetFunction.**CountA(Range("c:c" ))
Cells(Nonblank, 3).Select
ActiveCell.Copy
ActiveCell(2, 1).Select
ActiveSheet.Paste
Nextdate = ActiveCell.Value
Nextdate = Format(Nextdate, "yyyy-mm-dd hh:mm:ss")
Sheets("HH Recircs").Select
Range("D4").Select
With Selection.QueryTable
Criteria & "WHERE (MI_STATISTIC.ST_DATE={ts '"" & Nextdate""'})



AND More Criteria
.Refresh BackgroundQuery:=False
End With


Can anyone point me in the right direction?


Thanks


Will