none
Access 2007 bug(?) in CDate RRS feed

  • Question

  • Hi,

    my local setting for short date is d.M.rrrr.

    In MS Access debug window:

    ?CDate("15/1/2016")
    15.1.2016

    ?CDate("1/15/2016")
    15.1.2016

    Ie., I get same date with different strings! Weird, isn't it? Any idea how to solve this bug?

    Thank you very much in advance.


    P.S. My former account was Vladimir Cvajniga but I can't access it anymore, see http://social.msdn.microsoft.com/Forums/office/en-US/5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48/fatal-how-can-i-login-to-my-account?forum=Offtopic#5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48.


    Friday, April 29, 2016 10:34 AM

Answers

  • Microsoft isn't going to change this. Keep in mind that the IsDate function operates the same way: it will return True for "1/15/2016", regardless of your local settings.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, April 30, 2016 9:29 AM

All replies

  • This is not a bug, but VBA (or Windows) trying to be smart. Since the year has only 12 months, 15 cannot be a month number. So whether you pass "15/1/2016" or "1/15/2016", 15 is interpreted as the day and 1 as the month, regardless of your local date setting.

    Similarly, "2016/1/15" will be converted to 15.1.2016 too even though it does not conform to your local date setting.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, April 29, 2016 10:48 AM
  • It must NOT work that way!!! Any function could try to be smart? Eg. UCase("small") could return "BIG". It''s not normal.

    P.S. My former account was Vladimir Cvajniga but I can't access it anymore, see http://social.msdn.microsoft.com/Forums/office/en-US/5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48/fatal-how-can-i-login-to-my-account?forum=Offtopic#5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48.

    Friday, April 29, 2016 11:30 AM
  • As far as I know, recognizing dates is a Windows API function that tries to provide a best guess for any string input. For example, if your Windows is set to English, it will recognize "29 April" and "April 29, 2016" and "29-Apr-2016" etc.

    If you don't want this, you should avoid using CDate and write your own string-to-date function...


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, April 29, 2016 2:31 PM
  • I don't see what you are getting at here.  As 1/15/2016 is not a legitimate expression of a date in your system date format, as is also the case in mine (dd/mm/yyyy), what would you expect other than transposing the day and month?

    Ken Sheridan, Stafford, England

    Friday, April 29, 2016 4:24 PM
  • I would expect an error message instead of messing date input! Any record source which is based on a date / date interval may be messed that way. This is what happened here. CDate should respect local settings!!!

    P.S. Imagine transposing 1/2/2016 or any other combination where day < 13 and month < 13 and day <> month.

    P.P.S I have already removed CDate from my code but it's not a real solution for CDate "smart thinking".


    P.S. My former account was Vladimir Cvajniga but I can't access it anymore, see http://social.msdn.microsoft.com/Forums/office/en-US/5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48/fatal-how-can-i-login-to-my-account?forum=Offtopic#5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48.




    Saturday, April 30, 2016 3:46 AM
  • Microsoft isn't going to change this. Keep in mind that the IsDate function operates the same way: it will return True for "1/15/2016", regardless of your local settings.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, April 30, 2016 9:29 AM
  • Hi ,

    •  I get same date with different strings! Weird, isn't it? Any idea how to solve this bug?

            No, its not weird. it is the expected behavior of this function. you can say that it is understand by itself which date and which is month in the given string. it is always helpful for users if users enter the date in different format. it can understand it by itself and doesn't give any error.

    • you have asked that how to solve this bug?

           but here it is not a bug.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 2, 2016 5:50 AM
    Moderator
  • 2/3/2016

    3/2/2016

    Should CDate return same dates here? I'm afraid Microsoft is not going to change this.

    The only way is not to use CDate and IsDate, as Hans Vogelaar noted. I'd like to see a complete list of "unsafe" functions in MS Access (incl. ItemsSelected.Count in connection with ListBox). I do not understand why Microsoft creates "unsafe" environment.


    P.S. My former account was Vladimir Cvajniga but I can't access it anymore, see http://social.msdn.microsoft.com/Forums/office/en-US/5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48/fatal-how-can-i-login-to-my-account?forum=Offtopic#5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48.



    Monday, May 2, 2016 6:11 AM
  • Hi ,

    in this matter you can use the name of the month to distinguish between month and day like below.

    "3/Feb/2016"

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 2, 2016 7:10 AM
    Moderator
  • in this matter you can use the name of the month to distinguish between month and day like below.

    "3/Feb/2016"

    Hi,

    As an alternative you - the way I prefer - is to use the ISO notation:  yyyy-mm-dd.

    Imb.

    Monday, May 2, 2016 8:21 AM