Re: MVPs, please help (date range issue)

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 01/15/05 

Date: Sat, 15 Jan 2005 07:26:42 -0500

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.steele/SmartAccess.html) 

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"LPC" <LPC@discussions.microsoft.com> wrote in message 
news:F1692FBE-C1CC-483A-B2E4-C92E28E863E8@microsoft.com...
> 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" <LPC@discussions.microsoft.com> wrote in message
>> news:8473E4FF-FCB5-4BB0-BC25-F26A08AEDDB1@microsoft.com...
>> > PLEASE NOTE: THIS IS A RE-POST
>> >
>> > RE: 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
>> >
>> >
>> >
>> >
>>
>>
>>

Relevant Pages