parameter format

[Stephen]

Hi Folks,

I have developed a nifty little workbook that opens on a schedule, runs a
query against a SQL db, massages the data to remove zero's and add some math
functions, creates a clean (no macros) copy and emails it off to sales guys
at four in the morning.

It works like a charm with the exception of my data parameter... the format
in my db is yyyy-mm-dd. I have a cell in my workbook which is = TODAY() and
I can create a custom format for the cell as yyyy-mm-dd but it doesn't seem
to work when I run the file.

I'm not sure where to start troublshooting this so any suggestions would be
greatly appreciated.

TIA!

 

[Paul]

Unfortunately, despite the display format, the underlying data in the cell
remains the serial number of the date.

You could try adding a formula to a different cell like so :

if cell A1 is =TODAY()

Make the formula in A2
=year("A1")&"-"&month("A1")&text(Day("A1"),"00")

Then use A2 as your reference point.

 

[Sam Wilson]

Hi,

When I run a SQL query from excel VBA I have to force the date format:

Select * from tbl1 where mydate = 'mm/dd/yyyy'

So I usually end up witha ststement like

strSql = "Select * from tbl1 where mydate = ' " &
format({date},"mm/dd/yyyy") & " ' "

(there are extra spaces inside the single quotes there so that you can see
teh single quotes.

Sam

 

[Stephen]

I see where you're going with this but when I test your formula I get a
#value error.

 

[Stephen]

that's it... perfect! Just what I was looking for.

Thanks very much!