error writing date field to Access 2000 using SQL statement in VB 6
Thank you for the quick response. Your solution didn't work, but it
helped me to look a bit deeper into the issue, and I've finally
resolved it. Poor database design on my part. I named my date field
"Date", which Access doesn't like. Changing it to "OrderDate" fixes
the problem. Not the first time I've done this either, you'd think I'd
learn....
Quote:
> You need to format the date in a form that SQL is happy with. Currently your
> date gets formatted using your locale settings which (it seems) are not
> SQL-friendly.
> Try:
> StoreOrderNum(Counter4) & "', #" & format(StoreDate(Counter4), "yyyy-mm-dd")
> & "#)"
> There are several acceptable date formats, but this one is the ISO standard
> and supposed to be universally acceptable. Certainly works in all the
> SQL/Access applications I work with.
> > Hello:
> > I'm writing a program in VB 6 that retrieves info from email messages
> > in an Outlook folder and then inserts them into an Access 2000
> > database. Everything works except that when I try to insert date
> > records into a field (defined as date/time in Access) I get an "Syntax
> > error in SQL INSERT INTO statement" error (-2147217900). I assume it's
> > either a problem with:
> > - the way I format the date in VB
> > - my SQL statement
> > - the way I've set up the field in Access
> > Here's the most current SQL statement I use (simplified so that I'm
> > only writing to two fields):
> > cnUpdateDB.Execute "INSERT INTO Orders(OrderNum, Date) VALUES ('" &
> > StoreOrderNum(Counter4) & "', #" & StoreDate(Counter4) & "#)"
> > Does that seem right? The StoreDate variable is an array (I've also
> > tried just using simple string and date variables). The array is of
> > variable type "Date".
> > I'm fairly new to this, and hope it's something simple. Any help is
> > appreciated. Again, all of the other inserts (into string, integer and
> > currency fields) are working correctly, just not the date insert.
> > Thanks,
> > Tom Meuzelaar
> > RockWare, Inc.
