parameter format
 

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!

parameter format
 

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.



"Stephen" wrote:

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!

parameter format
 

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" wrote:

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!

parameter format
 

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

"Paul" wrote:

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.



"Stephen" wrote:

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!

parameter format
 

Stephen

My fault - =year(A1)&"-"&month(A1)&"-"&text(Day(A1),"00")

(I got a bit carried away with my quotes)

"Stephen" wrote:

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

"Paul" wrote:

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.



"Stephen" wrote:

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!

parameter format
 

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

Thanks very much!

"Paul" wrote:

Stephen

My fault - =year(A1)&"-"&month(A1)&"-"&text(Day(A1),"00")

(I got a bit carried away with my quotes)

"Stephen" wrote:

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

"Paul" wrote:

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.



"Stephen" wrote:

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!