What are we dealing with?
What values (instead of the proper dates) are displaying in the Pivot Table?

***********
Regards,
Ron

XL2002, WinXP


"Angus" wrote:

I have similar question, but the data format in pivottable that i retrieve
from database is in "mm/dd/yyyy" format. Pivottable doesn't recognise this as
date.

Following is my code at MS Query, i want to re-format Order_month:

SELECT Writing_Order_Replenish.Market, Writing_Order_Replenish.Scode,
Writing_Order_Replenish.Description,
Writing_Order_Replenish.Port_destination, Writing_Order_Replenish.Status,
Writing_Order_Replenish.Order_month, Writing_Order_Replenish.Quantity,
Writing_Order_Replenish.First_order_month, Writing_Order_Replenish.Market_PO
FROM `C:\Documents and
Settings\zi38610\Desktop\MIQ\Data\MIQ`.Writing_Ord er_Replenish
Writing_Order_Replenish

"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