#Func! error when splitting data to two fields

[JH40]

Hi all, I have a date/time field that contains the following:

Admit_Date_time: 4/29/2012 12:00:00 AM

I am splitting into two fields--one for date and one for time using:

FieldA_Date: Left([admit_date_time],InStrRev([admit_date_time],"")-11)

FieldA_Time: ADMIT_TIME: Format(Mid([admit_date_time],InStr([admit_date_time],"")+9),"Short Time")

Works GREAT! Until the time value in Admit_Date_Time is 12:00:00 AM. When this value is listed, my query returns #Func! for that row in the FieldA_Date only. FieldA_Time is blank. Any advice?

 

[Beetle]

Why not just use;

FieldA_Date: DateValue([admit_date_time])

FieldA_Time: TimeValue([admit_date_time])

 

[JH40]

OMG. I am laughing out loud over here. Thank you--I had no idea!

 

[Pat Hartman]

JH40, just FYI, dates are numeric values not strings. That's why string functions don't work well. There're a bunch of date specific functions. You can find them in help or if you have A2010 where help is utterly useless, with google.