Change in year format in queries

[mgoliveira]

Hey

I put together a database last year, with some queries that ask the user for month and year, in the format "MMM-YY". Until last year it was possible to enter "Apr-01", for example, and the query would work. Out of the sudden, this query is only accepting "Apr-2001"
(following the same previous example) to return a valid value, it is so strange...

I tried changing the year in the internal PC date to 2001, and the query was working again accepting only "01" instead of "2001". I tried to enter again the format "MMM-YY" in the properties of my query and the field in the corresponding table, but even so it won't work.

Does anyone know how to fix this ?

Thanks a lot !!!!!

 

[peachmelba]

mgoliveira

Hi

I have had this problem before in access. I think basically what happened for me was that 04/01 is taken as 04/01/01.

Way around it is to write a funtion that takes the 04/01 and makes it 01/04/2001 (a full date). Just to let you know that Access actually stores dates as numbers i.e. 37244 is 19/12/2001, time is done the same 19/12/2001 12:33:45 equals 37244.5234375

Regs

Peachmelba

 

[mgoliveira]

Thanks, Peachmelba. Could you give me a hint on how to write this function to convert 04/01 in a full date ? I am not a technical user and am not experienced in programming in Access, although I have some knowledge of very very basic stuff in VB...

If you have the time to tell me the way, I would really appreciate. Thanks again

 

[peachmelba]

mgoliveria

Suggest you have a look here may help you with lots of things

http://www.mvps.org/access

regs

peachmelba

 

[raskew]

Look at the DateValue function in the help file.

Theoretically, since 1932 we could submit: ? DateValue("12/99&quot and it would return 12/1/99. Reason being is that it was not ambiguous, i.e. "99" could only represent a year, not a day. Likewise for "00".

During year 2001, we could submit DateValue("11/01&quot and it would return 11/01/01. Although transparent, our submission was, in fact, ambiguous, since "01" could represent the first day of the month or the year 2001. It appeared to work only because (as stated in the help file), "… If the year part of date is omitted, DateValue uses the current year from your computer's system date.

Now that we're in 2002, if we submit DateValue("11/01&quot, the system returns 11/01/02-02 being the current year from the computer's system date.

The solution is fairly simple-modify your application(s) to accept "mmm-yyyy" rather than "mmm-yy" (or, if you're using short-date format, "mm/yyyy" versus "mm/yy&quot.

Within the past few days, the problem hit me like a ton of bricks. In many of my applications I'd used a function to populate a combo-box which allowed the user to specify a particular month (current month and previous 11 months):

Function dateloop() As String
' This procedures creates a string of month/year designators
' to be used as a combo-box's value list

Dim datehold As Date, strSQL As String
Dim n As Integer, i As Integer

n = 11
strSQL = ""

For i = 0 To n
datehold = DateSerial(year(Date), Month(Date) - i, 1)
strSQL = strSQL & Format(datehold, "mm&quot & "/"
strSQL = strSQL & Format(datehold, "yy&quot & "; " 'the line to fix
Next i

dateloop = strSQL

End Function

It was only necessary to change one line ('the line to fix) to read
StrSQL = strSQL & Format(datehold, "yyyy&quot & ";"

Some of my tables contained field "strMoYr", a text field with length (5), e.g. 12/01. It was necessary to change length to (7), e.g. 12/2001. You'll know when that's necessary because you'll receive a field-length error.

Hopefully, this will provide a direction to take. Please post back with your problems/questions.

Best wishes,

Bob