Date as Text to Proper Date

[Tezcatlipoca]

I know this one should be simple, but I jsut cannot see it, and a search of the forums doesn't seem to help.

I have an unbound textbox, txtDate. For various reasons, this box is - and must remain - in text format, as it doesn't always contain an actual date. Various other codes populate this box, sometimes with text (such as "Last August" or the like), and sometimes with a date (which will be listed as text in the following format: "yyyy/mm/dd").

What I'm trying to achieve is some code that when - and only when - a date gets put into this box, it corrects the date into dd/mm/yyyy format then compares it to the system date. If the txtDate date is in the past, something should happen, if it's in the future nothing should happen.

I've tried creating a second textbox with the format Day(Right(Left([txtDate],8),2)) & Month(Right(Left([txtDate],6),2)) & Year(Left([txtDate],4)) to try and turn the text into a proper date, but this just returns an error.

 

[JANR]

Have you tought of using the built-in function IsDate to test for valid dates before you do the formatting?

JR

 

[Tezcatlipoca]

Already done. Even though txtDate is an unbound textbox that can contain any text string, when dates get parsed into it they check out as proper dates with the IsDate function.

What I then want to do is have that date flipped from yyyy/mm/dd format to dd/mm/yyyy format for use elsewhere. The thing that makes this tricky is that txtDate can also contain text strings as well as dates, so any basic checks I'm trying either return errors or don't work at all.

Sure, I could just implement a simple invisible checkbox that contains mycheckbox = IsDate(txtDate) code, then have another routine only look at txtDate is mycheckbox is ticked. This would tell the code to only look at txtDate in instances where a date rather than text string has been returned, but that all just seems overly-elaborate. I'm sure there must be a simpler, cleaner solution.

 

[Rabbie]

Have you tried using the CDATE function to convert it into date/time format. Then you can FORMAT the result of that any which way you like

 

[Tezcatlipoca]

Have you tried using the CDATE function to convert it into date/time format. Then you can FORMAT the result of that any which way you like

CDATE!!! I knew I was missing something! Thanks, Rabbie. Code works exactly as it should now.

 

[Rabbie]

CDATE!!! I knew I was missing something! Thanks, Rabbie. Code works exactly as it should now.

Glad to have been of help