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

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

Hi Ron,

Were do I place your code in the SQL?

I have

Select mytables.myfields
From mytables
Where mytable.DateField=20060101

I tried your code as part of the Select statement, then after the where
statement. no luck so far.

--
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

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

Your basic structure in the second example seems correct. You may want to
check that the concatenation operators are correct for the database your
querying against. The ampersand (&) may be inappropriate. Also, the single
quotes may need to be double-quotes (").

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

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

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

Is there some way that I could progamatically change the PivotItem values of
the Date Field in the PivotCache? so that it is recognized as a date field
for grouping?

--
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

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

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

Now the order is 1/1/2008, 10/1/2007, 11/1/2007, 12/1/2007, 2/1/2008,
3/1/2008...

"Ron Coderre" wrote:

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

Since those values are sorting as text and not numbers, you need to convert
those values from text to dates.

Try this:

DATEVALUE(Writing_Order_Replenish.Order_month)

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

XL2002, WinXP


"Angus" wrote:

Now the order is 1/1/2008, 10/1/2007, 11/1/2007, 12/1/2007, 2/1/2008,
3/1/2008...

"Ron Coderre" wrote:

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