Importing External Data using a variable
Gregory,
I'm assuming that PriceDate was assigned a value before CorrectFormatIs =
Format(PriceDate, "yyyy-mm-dd") & " 00:00:00" (the first line of the code
you posted).
Is PriceDate declared as a Date data type variable? Is the "DatetoCust"
field in the Access DB a Date data type or a Text data type?
At first glance, it appears that you don't have your concatenation for your
WHERE statement correct. Try changing:
"WHERE (`~TMPCLP221001`.DatetoCust= {ts'CorrectFormatIs'})"
to
"WHERE (`~TMPCLP221001`.DatetoCust= {ts'" & CorrectFormatIs & "'})"
(I'm not familiar with the "{ts'...'}" part of your WHERE statement. It
seems like it might be unnecessary, but it also makes me think that the
DatetoCust field is a text field.)
Also, if DatetoCust is a Date/time data type, you may need to surround your
date with #'s (pound/number signs). So you might try changing your WHERE
statement to:
"WHERE (`~TMPCLP221001`.DatetoCust= {ts'#" & CorrectFormatIs & "#'})"
or
"WHERE (`~TMPCLP221001`.DatetoCust= #" & CorrectFormatIs & "#)"
Don't quote me on this (I am not an expert), but I don't think that you need
to format it, just as long as it is a date data type.
Also, I THINK that you can eliminate all of the "Chr(13) & "" & Chr(10)", if
you wanted to; they are unnecessary. The SQL syntax does not require
statements to be on separate lines, it just looks for the key words:
SELECT, FROM, WHERE, ORDER BY, GROUP BY, etc... (I'm almost certain that
the space between Chr(13) and Chr (10) is unnecessary)
I hope some, if not all of this, helps,
Conan Kelly
wrote in message
oups.com...
Hi, I am trying to query an access database using "Import External
Data". I originally recorded the macro, and used an arbitrary date to
filter for the data in Access. Now I am trying to make it more dynamic
by allowing the user to enter any date, and passing that variable to
the macro. However, I have ran into the several problems. I thought it
was because the format of the date was wrong, however when I change the
format, it still gives me an error under:
.Refresh BackgroundQuery = False. I would greatly appreciate anyones
help. Here is the code:
CorrectFormatIs = Format(PriceDate, "yyyy-mm-dd") & " 00:00:00"
Range("H1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access
Database;DBQ=S:\Mid-Atlantic\PricingBoard\Board\DailyBoard.mdb;Default Dir=S:\Mid-Atlantic\PricingBoard\Board;Driv"
_
), Array("erId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=Range("H1"))
.CommandText = Array( _
"SELECT `~TMPCLP221001`.CoName, `~TMPCLP221001`.PriceType,
`~TMPCLP221001`.Size, `~TMPCLP221001`.ClosingProb,
`~TMPCLP221001`.DatetoCust, `~TMPCLP221001`.Product" & Chr(13) & "" &
Chr(10) & "FROM `S:\Mid-Atlantic\PricingBoard\Boa" _
, _
"rd\DailyBoard`.`~TMPCLP221001` `~TMPCLP221001`" & Chr(13) & ""
& Chr(10) & "WHERE (`~TMPCLP221001`.DatetoCust= {ts
'CorrectFormatIs'})" & Chr(13) & "" & Chr(10) & "ORDER BY
`~TMPCLP221001`.CoName" _
)
.Name = "Query from MS Access Database_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
|