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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel is changing date format incorrectly 7
International Date formatting 4
Formatting from CopyFromRecordset 0
Date formatting 1
date format 6
VB Date Format 8
date comparison 2
Dates converted into text 5

Back
Top