Hi John - The data is being exported automatically via an "Auto Export Setup (see file uploaded with same name AutoExportSetup.jpg) from a software package called Teleform. I have the capability to write a custom export script (VBA) that can put the data in any form I wish. That is why I'm asking - what does the data need to look like so that Access will recognize it as a valid date/time format. Perhaps it is not possible. I guess I can just use the Cdate function when it gets to Access.
Mike --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > Mike- > > How are you getting the data into Access? If there's an external file, is > it text? It would seem to me you could link a text file, then write a query > to Append the data and use the CDate function. The problem is you cannot > append a text field to a date/time field even if the text field contains a > valid date/time string. You must convert it. > > John Viescas, author > Building Microsoft Access Applications > Microsoft Office Access 2003 Inside Out > Running Microsoft Access 2000 > SQL Queries for Mere Mortals > http://www.viescas.com/ > (Paris, France) > For the inside scoop on Access 2007, see: > http://blogs.msdn.com/access/ > > _____ > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf > Of takeadoe > Sent: Wednesday, July 05, 2006 2:00 PM > To: [email protected] > Subject: [ms_access] Re: What Does a Valid Date Time Format Look Like? > > > > Brett - 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 > > --- In [EMAIL PROTECTED] <mailto:ms_access%40yahoogroups.com> ps.com, > Brett Collings <bac@> wrote: > > > > Mike, > > > > See previous post, you can use > > > > DateValue("06/26/2006") & " " & TimeValue("11:30AM") > > > > or use them independently if you wish > > > > Brett > > > > At 13:25 01/07/06, you wrote: > > > > >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 > > > > > > > > > > > > > > > > > > > > > > > >Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >-- > > >Internal Virus Database is out-of-date. > > >Checked by AVG Anti-Virus. > > >Version: 7.1.394 / Virus Database: 268.8.4/364 - Release Date: > 14/06/06 > > > > > > -- > > Internal Virus Database is out-of-date. > > Checked by AVG Anti-Virus. > > Version: 7.1.394 / Virus Database: 268.8.4/364 - Release Date: > 14/06/06 > > > > > > > > > [Non-text portions of this message have been removed] > ------------------------ Yahoo! Groups Sponsor --------------------~--> Yahoo! Groups gets a make over. See the new email design. http://us.click.yahoo.com/XISQkA/lOaOAA/yQLSAA/q7folB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
