Question
-
CreatorTopic
-
March 17, 2010 at 8:45 am #2198458
Type Mismatch error with CDate
Lockedby sankulpune · about 14 years ago
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?Topic is locked -
CreatorTopic
All Answers
-
AuthorReplies
-
-
March 17, 2010 at 8:45 am #3033244
Clarifications
by sankulpune · about 14 years ago
In reply to Type Mismatch error with CDate
Clarifications
-
March 17, 2010 at 3:24 pm #3033167
Well given you meant
by tony hopkinson · about 14 years ago
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.
-
March 19, 2010 at 1:35 pm #2831715
Re. Well given you meant
by sankulpune · about 14 years ago
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.
-
March 22, 2010 at 10:30 am #2832664
Sorry I wasn’t clear
by tony hopkinson · about 14 years ago
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.
-
March 23, 2010 at 1:07 am #2831519
Re. Type Mismatch error with CDate
by sankulpune · about 14 years ago
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.
-
March 23, 2010 at 3:30 pm #2831346
Same reason as you are going to have a problem
by tony hopkinson · about 13 years, 12 months ago
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?
-
March 24, 2010 at 8:17 am #2829827
Re. Type Mismatch error with CDate
by sankulpune · about 13 years, 12 months ago
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.
-
March 24, 2010 at 10:00 am #2829812
One thing that is critical…
by cmiller5400 · about 13 years, 12 months ago
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.
-
March 24, 2010 at 10:41 am #2829805
Here is a quick rule of thumb for dates as string
by slayer_ · about 13 years, 12 months ago
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
-
March 24, 2010 at 4:00 pm #2829729
Because it’s not an error as such.
by tony hopkinson · about 13 years, 12 months ago
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.
-
-
-
-
AuthorReplies