What Does a Valid Date Time Format Look Like?

Gang -

I'm generating date and time variables from scanned forms. Currently,
the date and time values are as follows: 06/26/2006 and 11:30 AM. I've
written VBA code to combine them into a single string. The resulting
variable - datetime - is exported automatically to Access. When it
get's there it looks like "06/26/2006 11:30 AM". Problem is, it is a
string. If I set up the database before it is populated with records
and set the datetime variable to date/time format, it throws an error -
saying invalid data type. Clearly the ":" and perhaps the "AM" are
text and the only way I can get the data into Access is as a string.
That's no good. My question for the group is - what does the data have
to look like so that Access sees it as a valid date/time format?

Any help at all on this would be greatly appreciated.

Regards,

Mike

re: What Does a Valid Date Time Format Look Like?

This works for me...

?cdate("06/26/2006 11:30 AM")
6/26/2006 11:30:00 AM
?Dateadd("d",1,cdate("06/26/2006 11:30"))
6/27/2006 11:30:00 AM

I guess you could use Replace to strip out all the extra spaces if you
had to.

re: What Does a Valid Date Time Format Look Like?

Thank you for the note. I guess I didn't do a very good job with the
question. I was hoping to avoid the use of any of the functions in
Access like Cdate and the like. Ideally, I want to send the data to
Access in a form that it recognizes as a date/time variable. Perhaps
some background will help a bit.

Step 1 - create a blank table in Access with 3 variables with the
following names and data types.

County - (text)
Hunter Name (text)
Time of harvest (date/time)

Step 2 - Teleform (OCR software) scans forms, captures the data and
after the operator verifies it, an automated export feature sends all
of the data to Access.

Problem is, I have yet to find a way to format the data before it
leaves Teleform that Access will be happy with. Any ":" or text "AM"
in the string causes an error, as you might expect. Access is looking
for a date/time format and it sees text. Thus, back to my original
question, what is Access looking for?

I know that in SAS (Statistical Analysis Software) dates are stored as
a number. For example, 9/20/2005 might be 14085, which is the number
of days since Jan 1, 1960 (that's not exact, but you get the idea). If
you want to add time, the entire thing is converted to seconds since
1960 or some date.

Maybe let me ask another way - if I were entering date time data in
Access at the keyboard, what would it look like?

Does that make more sense?

Mike

pietlinden@hotmail.com wrote:

re: What Does a Valid Date Time Format Look Like?

"Takeadoe" wrote

Entering dates in a Control that is bound to a Date/Time field on a little
test database I have takes 07/02/2006 15:00:00 and then reformats before
displaying (based on my regional settings in the O/S) to 7/2/2006 3:00:00
PM.

However, when dealing with dates in code, it is often necessary to bracket
the date with #'s so Access doesn't try to do the division indicated by the
date separators. Can you format the date you are exporting as #07/05/2006
15:00:00# (using twenty-four hour time)?

Larry Linson
Microsoft Access MVP

re: What Does a Valid Date Time Format Look Like?

On Wed, 05 Jul 2006 19:48:04 GMT, "Larry Linson" <bouncer@localhost.notwrote:
Date in Access use to be, and maybe still are, dealt with by the OLE automation DLLs and so were
subject to various changes over time, particularly around the year 2000.

see http://blogs.msdn.com/ericlippert/ar.../16/53013.aspx for some remarks on this format.

re: What Does a Valid Date Time Format Look Like?

I have yet to try that, but I will and I'll let you know.

Regards,

Mike
Larry Linson wrote: