Full Version: Converting to US Date fails for some reason...
UtterAccess Forums > Microsoft® Access > Access Date + Time
MrSiezen
Hi!
I'm trying to convert string values that look like dates to a date value in US way.
CODE
strBijeenk = "3-1-2006"
strLastBijeenk = "20-12-2005"
(both strings)
USDate = Format(strBijeenk, "mm dd yyyy")
USDateLast = Format(strLastBijeenk, "mm dd yyyy")

USDate gives 3-1-2006, like it should.
But USDateLast gives 20-12-2005, instead of 12-20-2005.
Why?
freakazeud
Hi,
I think you should try something like:
ateSerial(Mid([string],5),Mid([string],3,2),Left([string],2))
HTH
Good luck
fkegley
You might also want to try this:
SDateLast = Format(CDate(strLastBijeenk), "mm dd yyyy")
MrSiezen
Sorry,
But that generates a type mismatch. But if I would use something like that, wouldn't I get into trouble with dates like 1-12-2005?
MrSiezen
Ah Frank that works!
hx for the help from both of you!
freakazeud
Or...
CDate(Left([YourControl01],1) & "/" & Mid([YourControl01],3,1) & "/" & Mid([YourControl01],5))
Many options here!
HTH
Good luck
fkegley
You're welcome. As Oliver pointed out, there's a number of ways to skin that particular cat!
freakazeud
As usual wink.gif
MrSiezen
Hmm talked too fast...
It seems that if you assign this a date value as USDateLast is, it converts it back! I guess have to work around using it a date value...
fkegley
It is using your system settings, isn't it to determine which part is the day and which the month? I believe that is true, particularly if the date is ambiguous as to which is the month and which the day.
think you're going to have to use Oliver's suggestion after all.
MrSiezen
Well,
recreated a part of the code so it also accepts it as a string value. Got it to work now after all! To my opinion the date handling syntax is one the hardest of them all (for as far my VBA knowledge goes hehe).
Thx!
fkegley
Yes, I agree, it would be much easier for all of us if there was one universally agreed way to represent dates. However, I don't think it will happen in our lifetimes.
You're welcome to my contribution. I am glad I was able to help and of course, glad you got it working!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.