Software

Ask something
Question Locked

Type Mismatch error with CDate

0 helpful
10 replies

by sankulpune

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

10 replies

by tony hopkinson

10 March 2010 ! :D

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.

by sankulpune

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.

by tony hopkinson

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.

by sankulpune

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.

by tony hopkinson

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?

by sankulpune

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.

by cmiller5400

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.

by slayer_

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

by tony hopkinson

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.

This discussion is closed to new replies.