The point I was trying to make is that you must use mm/dd/yyyy date format
in your queries. Regardless of what your regional settings are, Access will
not recognize dates in dd/mm/yyyy format. The exception to that is when the
day in the date is greater than 12: Access will recognize 15/1/2005 as 15
Jan, 2005, because there is no 15th month. It will always, however,
recognize 12/1/2005 as 1 Dec, 2005, never as 12 Jan, 2005.
For more information about working with International Dates, see Allen
Browne's "International Dates in Access" at
http://members.iinet.net.au/~allenbrowne/ser-36.html or what I have in my
September 2003 Access Answers column for Pinnacle Publication's "Smart
Access" newsletter. (The column and accompanying database can be downloaded
for free at
http://members.rogers.com/douglas.j....artAccess.html)
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"LPC" wrote in message
...
Hi, could you please explain it in an easier way? I cannīt understand the
relationship between the need to have the DAY greater than 12 while the
answer is about MONTH. It sounds too confusing to me. Anyway, as I donīt
even
have the SQL written (that īs why I posted the question before), Iīd
appreciate your help in giving me a possible syntax (Iīm only accustomed
to
simple queries and macros, not VBA).
Thanks
LPC
"Douglas J. Steele" wrote:
One problem you may be having is that in SQL statements, Access will not
recognize dates in dd/mm/yyyy format unless the day is greater than 12
(since there isn't a 13th month).
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"LPC" wrote in message
...
PLEASE NOTE: THIS IS A RE-POST
how to retrieve a record that falls in a date range
I have 2 tables, one containing information about the staff of an
office
(names, address, tel, etc.), and the other one containing their salary
history (StaffID, salary, date of salary modification). The 2 tables
are
related by the field StaffID. Salaries for each staff are not fixed but
can
be modified with time. For example, in Table Staffs, I have 2 persons,
Mark
(ID=1) and Nelly (ID=2). In table Salary History, Mark īs salary starts
at
500 USD on 1-1-98, but on 1-6-99 has been increased to 600, and on
1-8-99,
modified again to 750 USD. Nelly, on the other hand, receives a salary
of
650
USD as of 15-7-1998, but from 1-1-2000, this has been increased to 820.
Suppose we have to calculate their salaries for the month of July 1999.
Clearly we see that Markīs salary for that month is of 600, and Nelly
īs
is
750 (starting point), but I do not know how to create an expression
that
can
tell Access to retrieve this record by date comparison (July 1999 is
greater
than 1-1-1998 and 1-6-1999, but smaller than 1-8-1999, so Access SHOULD
retrieve the record of 600 USD, corresponding to Mark īs 1-6-99 īs
salary
modification, which is the most RECENT modification for that date
range.
The
same logic is for Nelly īs salary). Please help me to give a solution
to
my
obstacle. Your valuable help would be much appreciated.
Luu Phuong Chi
Access beginner