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 <[EMAIL PROTECTED]> 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 --------------------~--> Something is new at Yahoo! Groups. Check out the enhanced email design. http://us.click.yahoo.com/SISQkA/gOaOAA/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/
