Bingo , that worked

Thanks to all the Guy's who helped with this problem, it will allow me to
apply a variable to a whole series of ideas I have and safe time

Thanks again

"Chip Pearson" wrote in message
...
Get rid of the closing parenthesis after Value.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"John" wrote in message
...
Bob tried your format code but its getting a compile error
"Expected end of
statement" error at the ","


"Bob Phillips" wrote in
message
...
John,

Try formatting the date

FromDate = Format(Workbooks("Pull Gross Sales for 2005
Financial
Year.xls").Worksheets("Sheet2").Range("A1").Value) ,"yyyy-mm-dd")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John" wrote in message
...
I have a few questions the answers of which might explain
why my code is
not
working. First an outline of what I'm trying to do

Extract information from an MS Access database and pull in
to Excel.
I've
recorded the Macro and everything works great, except that
the 'from'
date
with regards to Sales information is hard coded and thus
inflexible, in
that
I wouldn't allow other users, who have to get this
information, dabble
with
the code itself

I've been told through this group that I should set a
variable and then
call
this through the relevant code line. When the information is
extracted
the
hard code for the relevant Sales date is in the following
format
"YYYY-MM-DD
hh:mm:ss". Now the cell which I want to reference my cell
against
visually
is formated DD/MM/YY

The error message I'm getting is a SQL debug on the line
below, but from
reading other usergroups it could be a formatting problem

.Refresh BackgroundQuery:=False



My relevant code is then set as follows


Dim FromDate As String
FromDate = Workbooks("Pull Gross Sales for 2005
Financial
Year.xls").Worksheets("Sheet2").Range("A1").Value


"WHERE (tblStoreTotals.SalesDate={ts '" & FromDate & "'})"


Now my questions

1. Does it matter that the source cell (Sheet2 A1) is typed
in as
DD/MM/YY
with no hh:mm:ss. I'm in Europe so thats how we type in
dates

2. Do I have to format a linked cell to A1 say B1 as
YYYY-MM-DD hh:mm:ss

3. Someone must have got a Data Import module working with a
variable
reference, any examples would be appreciated