I have tried a variety of options. nothing seems to work

I found the valid functions for the Pervasive database listed at:
http://www.pervasive.com/library/doc...age-3-001.html

It looks like cdate, mid, left and right functions are all valid.

the error I keep getting in MS Query is:
"Driver not capable."

Any idea which driver? is this something I can update?

I can't seem to find anything on the pervasive site regarding driver
problems with excel.

Thanks,


--
Simon Shaw, CA
President
Kode101 Inc.
www.kode101.com


"Ron Coderre" wrote:

One other thought.....

Maybe ACCPAC SQL doesn't have LEFT, RIGHT, and MID functions. Check the SQL
documentation...perhaps those should be replaced by SUBSTRING (or something
else).

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Simon Shaw" wrote:

here is my actual code:

SELECT OEINVD.ITEM, OEINVD.CATEGORY, OEINVH.CUSTOMER, OEINVH.BILNAME,
OEINVH.TERRITORY, OEINVD.LOCATION, OEINVH.SHIPTO, OEINVD.ORDNUMBER,
OEINVH.PONUMBER, OEINVH.INVNUMBER, OEINVH.INVDATE, OEINVD.UNITPRICE
FROM OEINVD OEINVD, OEINVH OEINVH
WHERE OEINVD.INVUNIQ = OEINVH.INVUNIQ AND ((OEINVH.CUSTOMER In ('1200')) AND
(OEINVH.INVDATE=20090101 And OEINVH.INVDATE<=20091231))
cdate(mid(OEINVH.INVDATE,5,2) & '/' & right(OEINVH.INVDATE,2) & '/' &
left(OEINVH.INVDATE,4)) AS InvoiceDate

I also tried

SELECT OEINVD.ITEM, OEINVD.CATEGORY, OEINVH.CUSTOMER, OEINVH.BILNAME,
OEINVH.TERRITORY, OEINVD.LOCATION, OEINVH.SHIPTO, OEINVD.ORDNUMBER,
OEINVH.PONUMBER, OEINVH.INVNUMBER, cdate(mid(OEINVH.INVDATE,5,2) & '/' &
right(OEINVH.INVDATE,2) & '/' & left(OEINVH.INVDATE,4)) AS InvoiceDate,
OEINVD.UNITPRICE
FROM OEINVD OEINVD, OEINVH OEINVH
WHERE OEINVD.INVUNIQ = OEINVH.INVUNIQ AND ((OEINVH.CUSTOMER In ('1200')) AND
(OEINVH.INVDATE=20090101 And OEINVH.INVDATE<=20091231))


--
Simon Shaw, CA
President
Kode101 Inc.
www.kode101.com


"Ron Coderre" wrote:

Try something like this in your SQL:

cdate(mid(myTable.FakeDate,5,2) & '/' & right(myTable.FakeDate,2) & '/' &
left(myTable.FakeDate,4)) AS 'MyDate'

Note: replace "myTable" with your actual table name and replace "FakeDate"
with your actual field name

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Simon Shaw" wrote:

Hi,

I am building a PivotTable connected to an ACCPAC database table.

My problem is that the date fields in ACCPAC are stored as YYYYMMDD, as a
result it is not regonized by the PivotTable as a date field. The PivotTable
treats the date fields like numbers so I cannot group them by months, years,
etc.

I would like to manually modify the SQL in MS Query to format the field
during the query so it returns as a date field.

Does anyone know the SQL to acheive this? or should I be asking this in an
SQL forum?

Thanks


--
Simon Shaw, CA
President
Kode101 Inc.
www.kode101.com