The problem here is whose system variables? (IE the system the database is designed on or where it will eventually live or where it will live once its original host dies etc. .). Since you can't always control all of these variables I have found that when I HAVE to use access that things are more easily handled by discarding the Access types and storing things as strings (from a VB/Access point of view). In fact, many programmers recommend this practice for dealing with really large numbers in VB (VB of course being the default Access / Office language). Of course it is inefficient but so is Access.
For instance many clients start in Access / Excel and then move to SQL Server. You would think that the two (both being from Microsoft) would play nice as far as Data Type translation goes but if you ever try porting a semi complicated test table from an Access database to SQL server they don't go without being kicked. Rather than wasting time learning Microsoft Errata (I (only half jokingly) think Microsoft does this on purpose to distract programmers so they have less competition) I have found that writing my own subroutines to deal with these strings far easier than trying to shoehorn things into Access types and then trying shoehorn Access data types into SQL Server data types etc. . . -Matt Play nice and always back things up. -----Original Message----- From: Volker I. Lipper [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 9:37 AM To: Jeff Thies Cc: [EMAIL PROTECTED] Subject: Re: date time in Access Hi Jeff, the correct syntax in access itself would be: >#9/30/1999# the writing of the data (mm.dd or dd.mm or mm/dd) belongs to the system variables set on the system acess is installed on. But in every case you have to use the # sign. hth Li > Date: Wed, 05 Mar 2003 07:24:04 -0500 > From: Jeff Thies <[EMAIL PROTECTED]> > Organization: The Limit Publications > To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> > Subject: date time in Access > I'm having trouble with date time in Access with DBI/ODBC > > I'm trying to do this: > > SELECT * FROM some_table WHERE date_time_field > 11/30/99 > > That's giving me all dates, where: > > SELECT * FROM some_table WHERE date_time_field < 11/30/99 > > Is giving me none. > > I must have done something completely wrong! > It really is a date time field and looks like this: 1998-06-25 00:00:00 > > Jeff > >
