Gotcha - Thank you again John. Mike
--- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > Mike- > > You can enter a date/time value from the keyboard exactly like that. Try it > in Nortwind Orders table - type (or paste) that string into OrderDate, and > Access understands it. But that won't work if you try to Append a text > field containing that string to a date/time field. Use CDate to convert it > from a linked table in a query. > > 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 7:12 PM > To: [email protected] > Subject: [ms_access] Re: What Does a Valid Date Time Format Look Like? > > > > 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] <mailto:ms_access%40yahoogroups.com> ps.com, > "John Viescas" <JohnV@> 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. <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 4:20 PM > > To: [EMAIL PROTECTED] <mailto:ms_access%40yahoogroups.com> ps.com > > 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. <http://www.viescas.com/> com/> > com/ > > > (Paris, France) > > > For the inside scoop on Access 2007, see: > > > http://blogs. <http://blogs. <http://blogs.msdn.com/access/> > 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] > > > > > > > > > [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/
