John - I can output it as a number if I knew what that number needed to look like. Do you know what I mean? Put it another way - let's say I was in Access and I was entering the data from the key board into a date/time field. How would I enter 06/20/2006 11:30:00 AM?
Mike --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > Mike- > > If the output is Text, then you'll have to do an explicit convert to > date/time data type to be able to stuff it into a date/time field in a > table. > > 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 4:20 PM > To: [email protected] > Subject: [ms_access] Re: What Does a Valid Date Time Format Look Like? > > > > 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] <mailto:ms_access%40yahoogroups.com> ps.com, > "John Viescas" <JohnV@> 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. <http://www.viescas.com/> com/ > > (Paris, France) > > For the inside scoop on Access 2007, see: > > http://blogs. <http://blogs.msdn.com/access/> msdn.com/access/ > > > > _____ > > > > From: [EMAIL PROTECTED] <mailto:ms_access%40yahoogroups.com> ps.com > [mailto:[EMAIL PROTECTED] <mailto:ms_access%40yahoogroups.com> ps.com] > On Behalf > > Of takeadoe > > Sent: Wednesday, July 05, 2006 2:00 PM > > To: [EMAIL PROTECTED] <mailto:ms_access%40yahoogroups.com> ps.com > > 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] > > > > > > > > > [Non-text portions of this message have been removed] > 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/
