VBA/SQL date variable question

[Will]

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

 

[Stephen Bullen]

Hi Will,

Criteria & "WHERE (MI_STATISTIC.ST_DATE={ts '"" & Nextdate""'})

Looks like the wrong sets of quotes. Try:

Criteria & "WHERE (MI_STATISTIC.ST_DATE={ts '" & Nextdate & "'})"

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev

 

[Will]

Stephen,

Thanks for the response I have tried your suggestion;

"WHERE (MI_STATISTIC.ST_DATE={ts '" & Nextdate & "'}) AND ANOTHER
CRITERION"

but I still get a type mismatch error when I try to run the code. For
clarity after the 'ts' I have written single quote followed immediately
by a double quote and prior to the '}' I have written a double quote
followed by a single quote.In the short term I have written a macro
which updates cells that feed query paramaters but I would like to
understand how to use the variable directly within the SQL. This
obviously uses slightly less code.

Regards

Will