K
kevin fay
I have seen a lot of postings on how to set up and use a
parameter in Microsoft Query, and also store the parameter
values in a cell in the Excel worksheet. This works very
well with regular numbers.
Dates seem to be much more of a challenge to a lot of
people. I found that a Microsoft Query against our Oracle
database required that dates be entered in the dd-mmm-yy
format (even though other formats were acceptable in a
query without parameters). Not a big problem once that was
understood.
But of course when a date like 25-dec-03 is entered into a
cell, Excel displays it that way . but stores it as 37980
(the number of days since 1/1/1900). Entering a single
quote in front of 25-dec-03 did not help either, as Excel
took the entry to be a label.
In researching the newsgroups, I found that my question
has arisen many times . and the variety of answers
provided were not exactly right. Finally, I found a
recommendation made by a Microsoft "MVP" in Oxford,
England about three years ago that got me very close.
Trimming down his answer, I found that the above date can
be stored in a cell as a date parameter if entered as
follows:
=TEXT(37980, "dd-mm-yy")
Not very ingenious, but an answer that has alluded many
people for a long time. To make things easier for the
user, I modified the entry to:
=TEXT(A1, "dd-mm-yy")
and I will tell the user to enter their date in any format
they choose into cell A1.
parameter in Microsoft Query, and also store the parameter
values in a cell in the Excel worksheet. This works very
well with regular numbers.
Dates seem to be much more of a challenge to a lot of
people. I found that a Microsoft Query against our Oracle
database required that dates be entered in the dd-mmm-yy
format (even though other formats were acceptable in a
query without parameters). Not a big problem once that was
understood.
But of course when a date like 25-dec-03 is entered into a
cell, Excel displays it that way . but stores it as 37980
(the number of days since 1/1/1900). Entering a single
quote in front of 25-dec-03 did not help either, as Excel
took the entry to be a label.
In researching the newsgroups, I found that my question
has arisen many times . and the variety of answers
provided were not exactly right. Finally, I found a
recommendation made by a Microsoft "MVP" in Oxford,
England about three years ago that got me very close.
Trimming down his answer, I found that the above date can
be stored in a cell as a date parameter if entered as
follows:
=TEXT(37980, "dd-mm-yy")
Not very ingenious, but an answer that has alluded many
people for a long time. To make things easier for the
user, I modified the entry to:
=TEXT(A1, "dd-mm-yy")
and I will tell the user to enter their date in any format
they choose into cell A1.