parameter format

  • Thread starter Thread starter Stephen
  • Start date Start date
S

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!
 
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.
 
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
 
I see where you're going with this but when I test your formula I get a
#value error.
 
Back
Top