Re: VBA EXCEL DBASE SQL

From: Greg Schretter (Schretter_at_discussions.microsoft.com)
Date: 12/28/04 

Date: Tue, 28 Dec 2004 13:23:04 -0800

Your formating for the date is wrong.

Pull the date field through a record set and and see what it looks like

In foxpro it was {} but you are using a jet engine doing dbase so it might
want ## and it might convert them to {}.

Don't you just love data???? ;P

"Cindy Winegarden" wrote:

> Hi Bill,
>
> I'm not sure about Dbase but in FoxPro dates are written in text surrounded
> by curly braces: {12/20/2004}.
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@msn.com www.cindywinegarden.com
>
>
> "Bill Wilkie" <bwilkie@sbcglobal.net> wrote in message
> news:%236wZkWI5EHA.3648@TK2MSFTNGP11.phx.gbl...
> > Using WinXPsp2 and OfficeXP Excel. Trying to get data from huge dbase
> > dbf's.
> > ----------------------------------------------------------------------------
> > -----
> > Rem Microsoft ActiveX Data Objects 2.8 in reference selected
> > Sub test()
> > Set MyConn = New ADODB.Connection
> > MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > Source=e:\databases;Extended Properties=dbase IV"
> > MyConn.Open
> >
> > Rem do sql statement to load MyRecSet
> > Rem Set MyRecSet = MyConn.Execute("SELECT restoid,dates,hours,menuid
> > FROM
> > payment")
> > Rem Set MyRecSet = MyConn.Execute("SELECT restoid,dates,hours,menuid
> > FROM
> > payment WHERE payment.dates={20040827}")
> >
> > Rem copy recordset to ***
> > ws.Range("A2").CopyFromRecordset MyRecSet
> >
> > MyConn.Close
> > End Sub
> > --------------------------------------------------
> > Run with first SELECT with no WHERE command and I get all data.
> > Run with second SELECT with WHERE command and get no data. No Errors just
> > no
> > data.
> > The dbf field descripton of the dates field is "Date field formatted as
> > CCYYMMDD (8 characters)"
> > The CC is century.
> >
> >
>
>
>