Question

  • Creator
    Topic
  • #2198458

    Type Mismatch error with CDate

    Locked

    by sankulpune ·

    Hi,

    I have following code in my if 6 program

    Const ChkDate as String = “10 Match 2010”

    If Now() > CDate(ChkDate) then

    msgbox “Send Reminder”

    End If

    This program runs perfectly on my PC.

    I created the exe and sent it to another location. The exe runs without any
    problems on PCs at this location.

    The same exe does not run on a PCs at third location. It gives error No.: 13,
    Type mismatch.

    I have tried to analyze this problem but unable to understand any reason why
    this error should occur only at third location and how we can resolve this. All
    locations have Win XP. I have also verified that system settings are similar.

    Can anybody suggest the reason and the remedy? Am I overlooking any particular
    system setting?

All Answers

  • Author
    Replies
    • #3033244

      Clarifications

      by sankulpune ·

      In reply to Type Mismatch error with CDate

      Clarifications

    • #3033167

      Well given you meant

      by tony hopkinson ·

      In reply to Type Mismatch error with CDate

      10 March 2010 ! 😀

      Then yes.

      That’s a totally non standard format for a date.

      Whether the PC can ‘understand’ it will be dependent on locale, regional settings etc.

      When transferring dates as strings use a universal format like yyyy-MM-dd.

      Display the things however you want but pass them in a common format.

      Can’t empahsise this enough, the wheels always come off at some point if you don’t use a universal for storage as a string.

      • #2831715

        Re. Well given you meant

        by sankulpune ·

        In reply to Well given you meant

        I played with regional settings and changed date formats in many different ways – from my original MM/DD/YYYY format to Customers DD-MM-YYYY format. But my exe doesn’t bother about it and executes without any problem.

        So, where exactly is the problem? I’m lost.

        • #2832664

          Sorry I wasn’t clear

          by tony hopkinson ·

          In reply to Re. Well given you meant

          The problem is CDate(string) expects string to be in a format known to the machine executing the code.

          That’s why you choose a universal one for transfer which “dd MMMM yyyy” most definitely isn’t.

          You might be able to recreate the error on your machine by twiddling, but you might not see it in debug either.

          Too many variations. Like I said use a specific universal format to put the date out as a string, and then CDate will just work.

          If you want to know waht the target machine is blowing chunks on you’ll have to trap teh exeception and throw up the string it’s trying to convert. No point in doing it on your machine though.

          Even if you fix this one, the owner of teh PC could chnage their regional settings and break your code again, so don’t depend on some particular setting.

        • #2831519

          Re. Type Mismatch error with CDate

          by sankulpune ·

          In reply to Sorry I wasn’t clear

          Hi,

          Thanks all for the responses.

          Finally, after spending 4 days in trial and error investigations, I changed the constant declaration to

          Const ChkDate As Date = #3/10/2010#

          and the code to

          If Now() > ChkDate then

          msgbox “Send Reminder”

          End If

          And it worked.

          But mystery remains about the exact cause of error. Why previous code did work on 2 PCs and not on the third PC even when all the date settings were changed in accordance with the third PC.

        • #2831346

          Same reason as you are going to have a problem

          by tony hopkinson ·

          In reply to Re. Type Mismatch error with CDate

          between systems that take that date as either 3 Oct 2010 , 10 Mar 2010…

          Which bit of unambiguous choice of date format don’t you get?

        • #2829827

          Re. Type Mismatch error with CDate

          by sankulpune ·

          In reply to Same reason as you are going to have a problem

          Tony,

          Sorry, but why then it worked on the two computers? Why I am not able to replicate the error on my PC?

          My point is there was something wrong and I have not been able to pinpoint what exactly was wrong. I have put it under the carpet. And I am scared that it may resurface again, in some other form at some other location at some time in future, and I will be lost again until I find another patchwork solution, waiting for the next disaster.

          Tell me the setting that I can change on my computer that will generate the error. Then I would say that I have really solved the problem.

          I am not an expert. So I am not worried about making mistakes. What worries me is that even after getting opinions from more knowledgeable and experienced persons than me, the exact cause of error eludes me.

        • #2829812

          One thing that is critical…

          by cmiller5400 ·

          In reply to Re. Type Mismatch error with CDate

          Is listening.

          Listen to what Tony said. He has YEARS of programming experience. Taking a string that can be interpreted in a zillion ways and running a cDate on it can produce a zillion different results.

          Sometimes it isn’t just one setting but multiple that cause unintended results.

        • #2829805

          Here is a quick rule of thumb for dates as string

          by slayer_ ·

          In reply to Re. Type Mismatch error with CDate

          Saying your input date is dd/MM/yyyy

          Depending on your regional settings, the day and month can be flipped each time they are cdated or formated. The trick? Insure all date formats are yyyy/MM/dd. This format never gets flipped.

          If you are going to convert a date to a string again, then use the day() month() and year() functions

          Like so

          day(dtDate) & “/” & month(dtDate) & “/” & year(dtDate)

          Put simply, if a date is to be saved to a string in memory, save it as yyyy/mm/dd.

          To revise your original code,
          Const ChkDate as String = “2010/03/01”

          If Now() > CDate(ChkDate) then

          msgbox “Send Reminder”

          End If

        • #2829729

          Because it’s not an error as such.

          by tony hopkinson ·

          In reply to Re. Type Mismatch error with CDate

          It’s a configuration.

          There is only one strategy for dealing with this, make your application use one known format for any string representation of date / time / number / decimal that is logic dependant.

          It’s the only way to be sure your code is going to work, everywhere it needs to.

          Tracking down exactly where this is going wrong, might turn ot to be easy, it could be a major pain in the arse. Once you’ve done it you solved one configuration. There are hundreds of others.

          A little story to illustrate, winodws xp box set up as full english UK.

          One bit of code was saying it couldn’t find any records for a date 3/6/yyyy
          This piece of code was usng an API routine with the date passed as a variant. It was looking for 6/3/yyyy
          That particular routine always took any ambiguous date as being in the format mm/dd/yyyy. It did not respect regional settings, application settings, in fact any configuration. So it only failed from 1-12 / 1 – 12 / where date didnt equal the month. Took support and then me three remote support calls to identify the actual issue, another two hours to track it down (we couldn’t replicate it, different dll on our systems)
          and exactly five minutes to fix the code, but the customer had to wait two months for the patch in the next release.

          Go unambiguous, it’s the only way to be sure now and in the future.

          If you are curious enough to want to know.
          Wrap that CDate call with an on error goto, display the string it failed to convert and then do a graceful fail.

          Personally I’d just fix the problem instead of the symptom though.

Viewing 1 reply thread