Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parameter Query Format Issues | Excel Programming | |||
How to choose if I use a parameter or not in a parameter query | Excel Discussion (Misc queries) | |||
Format when sending a range as parameter to a custom function | Excel Programming | |||
format optional parameter | Excel Worksheet Functions | |||
format optional parameter | Excel Discussion (Misc queries) |