Format ([myDate], "Medium Date") sometimes works, often doesn't?

[David Ball]

Hi,

For various reasons I have to bring dates into my Table as Short Text and then convert them back to dates in my query.

I usually use:

Format ([myDate], "Medium Date")

The frustrating thing is that sometimes it works like a charm but often it doesn't.

Why? How can I get it to always works.

Thanks very much

Dave

 

[RuralGuy]

Try: Format([myDate], "d-mmm-yy") instead.

 

[David Ball]

Thanks RuralGuy, but I tried this and I still can't change my text to dates. It's driving me crazy!

 

[Galaxiom]

Format returns a string. For Date data type use CDate().

 

[David Ball]

Thanks very much Galaxiom

 

[David Ball]

Now I get date data type but it returns "error" for entries with no dates in them. This then crashes my report when I try to sort by date...

Is there a way to use CDate() and not get an error when there is no date entered (just a null)?

Thanks very much

 

[RuralGuy]

You can test first with the IsDate() function.