Hi.
I know what you're thinking: If I just tweak John's suggestion a little
bit, I could give my users the ability to use a two-digit year and the query
won't assume that the two digits are the day of the month and assume the
current year. Here's what that "little tweak" would look like in order to
accommodate both two-digit and four-digit years:
SELECT *
FROM tblSales
WHERE ((SomeDate >= CDate(MID([Enter month and year:], 1, INSTR(1, [Enter
month and year:], " ")) & "1, " & IIF(INSTR(1, [Enter month and year:], " ")
= (LEN([Enter month and year:]) - 2), IIF (MID([Enter month and year:],
INSTR(1, [Enter month and year:], " ") + 1, 2) <= 50, "20", "19") &
MID([Enter month and year:], INSTR(1, [Enter month and year:], " ") + 1, 2),
MID([Enter month and year:], INSTR(1, [Enter month and year:], " ") + 1,
4)))) AND (SomeDate < DateAdd("m", 1, MID([Enter month and year:], 1,
INSTR(1, [Enter month and year:], " ")) & "1, " & IIF(INSTR(1, [Enter month
and year:], " ") = (LEN([Enter month and year:]) - 2), IIF (MID([Enter month
and year:], INSTR(1, [Enter month and year:], " ") + 1, 2) <= 50, "20", "19")
& MID([Enter month and year:], INSTR(1, [Enter month and year:], " ") + 1,
2), MID([Enter month and year:], INSTR(1, [Enter month and year:], " ") + 1,
4)))));
.... where tblSales is the name of the table and SomeDate is the name of the
field which contains the date within the month and year specified. This
query uses window bracketing to assume which century the two-digit year falls
into. This query is ugly and doubly difficult to maintain: it's hard to
make changes, and it uses window bracketing for the assumed year (which bit
many companies in the butt for Y2K) so is only good for the years 1951
through 2050.
The best advice you can receive is the advice John already gave you. Use
his suggestion for the WHERE clause and tell the users that they must use
four-digit years:
SELECT *
FROM tblSales
WHERE ((SomeDate >= CDate([Enter month and year:])) AND (SomeDate <
DateAdd("m", 1, [Enter month and year:])))
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
KAnoe said:
I have a report that the user would like to type in only the month and Year
in the following format: Jan 05. Then the report would show the report with
only dates for Jan 05. Only one month per report.
I know how to do a like query for just one date
Like [Enter the date for your report]
Thanks
