Date Formatting issue

[Joel Wiseheart]

I have a crosstab query that pulls date data from an HP
server, where dates are stored as integers (From our 30-
year old ManMan MRP system). To make it useable in a
modern date format, I have to do the following:

DueDate:CDate([DateField]+26236)

(The "+ 26236" is to accomodate the fact that 0 =
1/1/1900 in Microsoft prograqms, and 10/30/1971 in HP. Go
figure).

The default format of the CDate function is "mm/dd/yyyy".

I would like to change the date format to "m/d/yy". By
shortening the year by two digits, and also shortening
the day and month, it saves about 50 pages on the printed
version once the report is complete, due to space
considerations.

....Now for the problem...

When I format the date as such:

DueDate:Format(CDate([DateField]+26236),"m/d/yy")

the proper formatting is applied, but the sorting now
treats the date like a text string. It sort by month
instead of year, like below:

1/4/03
1/4/04
1/4/05
1/5/03
1/5/04
1/5/05
etc.

I need it to sort like:

1/4/03
1/5/03
1/4/04
1/5/04
1/4/05
1/5/05

It's starting from the leftmost character, and working to
the right for sorting, instead of sorting in
chronological order like date fields do.

Is there a way to get the shorter format, and still
have it sort chrololoically?

Thanks!

 

[John Vinson]

I have a crosstab query that pulls date data from an HP
server, where dates are stored as integers (From our 30-
year old ManMan MRP system). To make it useable in a
modern date format, I have to do the following:

DueDate:CDate([DateField]+26236)

(The "+ 26236" is to accomodate the fact that 0 =
1/1/1900 in Microsoft prograqms,

ummmm... No. It isn't. A Date is stored as a Double Float count of
days, and the zero point is December 30, 1899. This makes 2.0 January
1, 1900.

and 10/30/1971 in HP. Go figure).

The default format of the CDate function is "mm/dd/yyyy".

CDate doesn't HAVE a format. It stores the date as a Double Float
number; the format is applied at display time.

I would like to change the date format to "m/d/yy". By
shortening the year by two digits, and also shortening
the day and month, it saves about 50 pages on the printed
version once the report is complete, due to space
considerations.

Just set the Format property of the Report textbox to "m/d/yy".

...Now for the problem...

When I format the date as such:

DueDate:Format(CDate([DateField]+26236),"m/d/yy")

the proper formatting is applied, but the sorting now
treats the date like a text string. It sort by month
instead of year, like below:

Exactly. The Format function converts a Date/Time value to a String.

Is there a way to get the shorter format, and still
have it sort chrololoically?

Use the CDate as the control source of the textbox, and set the Format
property of the textbox itself rather than using the Format()
function.

 

[Joel Wiseheart]

-----Original Message-----

I have a crosstab query that pulls date data from an HP
server, where dates are stored as integers (From our 30-
year old ManMan MRP system). To make it useable in a
modern date format, I have to do the following:

DueDate:CDate([DateField]+26236)

(The "+ 26236" is to accomodate the fact that 0 =
1/1/1900 in Microsoft prograqms,

ummmm... No. It isn't. A Date is stored as a Double Float count of
days, and the zero point is December 30, 1899. This makes 2.0 January
1, 1900.

and 10/30/1971 in HP. Go figure).

The default format of the CDate function

is "mm/dd/yyyy".

CDate doesn't HAVE a format. It stores the date as a Double Float
number; the format is applied at display time.

I would like to change the date format to "m/d/yy". By
shortening the year by two digits, and also shortening
the day and month, it saves about 50 pages on the printed
version once the report is complete, due to space
considerations.

Just set the Format property of the Report textbox to "m/d/yy".

...Now for the problem...

When I format the date as such:

DueDate:Format(CDate([DateField]+26236),"m/d/yy")

the proper formatting is applied, but the sorting now
treats the date like a text string. It sort by month
instead of year, like below:

Exactly. The Format function converts a Date/Time value to a String.

Is there a way to get the shorter format, and still
have it sort chrololoically?

Use the CDate as the control source of the textbox, and set the Format
property of the textbox itself rather than using the Format()
function.


.

That would be great if there was a textbox....

However, this is pulling data from a linked table (linked
from our ManMan system) into a select query. The select
query (which uses the CDate function) is then used as a
data source for a crosstab query. The date is used as the
column heading of the crosstab.

I tried looking at the "field properties" in the QBE
View. It lists "Format" in the property sheet for every
field listed except the one specified as the column
heading. Grr...

I tried changing the format in the source select query
that feeds the crosstab query. It works great at
reformatting the date in the select query, but reverts
back to the "mm/dd/yyyy" format when used in the column
header of the crosstab query. Grr again...

By the way, the crosstab query then exports to an Excel
spreadsheet, which the end-users prefer to use. So,
there's never any forms or text boxes involved in the
process. It also won't let me modify the format in the
Excel spreadsheet either!

Very strange...

Thanks!

 

[Phobos]

That would be great if there was a textbox....

You should have said what the query was doing, it would have saved Johns
time.

However, this is pulling data from a linked table (linked
from our ManMan system) into a select query. The select
query (which uses the CDate function) is then used as a
data source for a crosstab query. The date is used as the
column heading of the crosstab.

I tried looking at the "field properties" in the QBE
View. It lists "Format" in the property sheet for every
field listed except the one specified as the column
heading. Grr...

I tried changing the format in the source select query
that feeds the crosstab query. It works great at
reformatting the date in the select query, but reverts
back to the "mm/dd/yyyy" format when used in the column
header of the crosstab query. Grr again...

By the way, the crosstab query then exports to an Excel
spreadsheet, which the end-users prefer to use. So,
there's never any forms or text boxes involved in the
process. It also won't let me modify the format in the
Excel spreadsheet either!

Okay, try this:

in your SELECT query, create two fields:

Field 1: Your date in date format (Using the CDate() function)
Field 2: Format([Field1],"dd/mm/yyyy")

Sort by Field1.

Use Field2 as the heading source for your crosstab query.

P

 

[Joel Wiseheart]

-----Original Message-----

That would be great if there was a textbox....

You should have said what the query was doing, it would have saved Johns
time.

However, this is pulling data from a linked table (linked
from our ManMan system) into a select query. The select
query (which uses the CDate function) is then used as a
data source for a crosstab query. The date is used as the
column heading of the crosstab.

I tried looking at the "field properties" in the QBE
View. It lists "Format" in the property sheet for every
field listed except the one specified as the column
heading. Grr...

I tried changing the format in the source select query
that feeds the crosstab query. It works great at
reformatting the date in the select query, but reverts
back to the "mm/dd/yyyy" format when used in the column
header of the crosstab query. Grr again...

By the way, the crosstab query then exports to an Excel
spreadsheet, which the end-users prefer to use. So,
there's never any forms or text boxes involved in the
process. It also won't let me modify the format in the
Excel spreadsheet either!

Okay, try this:

in your SELECT query, create two fields:

Field 1: Your date in date format (Using the CDate() function)
Field 2: Format([Field1],"dd/mm/yyyy")

Sort by Field1.

Use Field2 as the heading source for your crosstab query.

P

This solution doesn't work.

If it sheds any further light on the situation, here's
the SQL view of the crosstab query. I need to find out if
there is a way to have the 'Due Date' sort like a date,
but also have special formatting, for the Date field
listed in the PIVOT statement:

TRANSFORM Sum(qryOpenDemandSwitches.[Open Qty]) AS [The
Value]
SELECT qryOpenDemandSwitches.ODINO,
qryOpenDemandSwitches.[CC 3],
qryOpenDemandSwitches.CLCODARR_3 AS [CC 4], Sum
(qryOpenDemandSwitches.[Open Qty]) AS [Total Of Open Qty]
FROM qryOpenDemandSwitches
GROUP BY qryOpenDemandSwitches.ODINO,
qryOpenDemandSwitches.[CC 3],
qryOpenDemandSwitches.CLCODARR_3
ORDER BY qryOpenDemandSwitches.[CC 3]
PIVOT qryOpenDemandSwitches.[Due Date]; <- This one

If I try:

PIVOT Format(qryOpenDemandSwitches.[Due Date],"m/d/yy")

It formats the date, but as we discussed earlier, it also
sorts the column headings like a string.

I tried right-clicking on the field in QBE view, but
the 'column headings' is the only one that doesn't have
a "Format" property, like John mentioned earlier for a
text box. All the other fields do, so it must be
something in particular about the column headings / PIVOT
statement.

I apologize if my desription of the problem is coming in
pieces. It's a little difficult to relay in a text-only
format, and I'm sending more details as I continue to
research the problem. Thanks for your patience in helping
me.

Thanks again,
Joel

 

[Joel Wiseheart]

-----Original Message-----

-----Original Message-----

That would be great if there was a textbox....

You should have said what the query was doing, it would have saved Johns
time.

However, this is pulling data from a linked table (linked
from our ManMan system) into a select query. The select
query (which uses the CDate function) is then used as a
data source for a crosstab query. The date is used as the
column heading of the crosstab.

I tried looking at the "field properties" in the QBE
View. It lists "Format" in the property sheet for every
field listed except the one specified as the column
heading. Grr...

I tried changing the format in the source select query
that feeds the crosstab query. It works great at
reformatting the date in the select query, but reverts
back to the "mm/dd/yyyy" format when used in the column
header of the crosstab query. Grr again...

By the way, the crosstab query then exports to an Excel
spreadsheet, which the end-users prefer to use. So,
there's never any forms or text boxes involved in the
process. It also won't let me modify the format in the
Excel spreadsheet either!

Okay, try this:

in your SELECT query, create two fields:

Field 1: Your date in date format (Using the CDate() function)
Field 2: Format([Field1],"dd/mm/yyyy")

Sort by Field1.

Use Field2 as the heading source for your crosstab query.

P

This solution doesn't work.

If it sheds any further light on the situation, here's
the SQL view of the crosstab query. I need to find out if
there is a way to have the 'Due Date' sort like a date,
but also have special formatting, for the Date field
listed in the PIVOT statement:

TRANSFORM Sum(qryOpenDemandSwitches.[Open Qty]) AS [The
Value]
SELECT qryOpenDemandSwitches.ODINO,
qryOpenDemandSwitches.[CC 3],
qryOpenDemandSwitches.CLCODARR_3 AS [CC 4], Sum
(qryOpenDemandSwitches.[Open Qty]) AS [Total Of Open Qty]
FROM qryOpenDemandSwitches
GROUP BY qryOpenDemandSwitches.ODINO,
qryOpenDemandSwitches.[CC 3],
qryOpenDemandSwitches.CLCODARR_3
ORDER BY qryOpenDemandSwitches.[CC 3]
PIVOT qryOpenDemandSwitches.[Due Date]; <- This one

If I try:

PIVOT Format(qryOpenDemandSwitches.[Due Date],"m/d/yy")

It formats the date, but as we discussed earlier, it also
sorts the column headings like a string.

I tried right-clicking on the field in QBE view, but
the 'column headings' is the only one that doesn't have
a "Format" property, like John mentioned earlier for a
text box. All the other fields do, so it must be
something in particular about the column headings / PIVOT
statement.

I apologize if my desription of the problem is coming in
pieces. It's a little difficult to relay in a text-only
format, and I'm sending more details as I continue to
research the problem. Thanks for your patience in helping
me.

Thanks again,
Joel

Hello again,
Well I solved the problem for the moment in a
different fashion. John Vinson set off the light bulb in
my head, when he said that the Format function converts
the date to a string. The reason nothing was happening
when I tried to convert it in Excel was that it was
already a string by the time it got there.
The solution to this particular problem was not to do
ANY conversions in Access....just let it go through the
whole select and crosstab process as an integer. Then, I
used VBA in Excel (Auto_Open macro) to convert it to a
date, and add 26236 to it there.
And, of course, the end-users don't care. All they
care about is that they push a button on one end, and
their spreadsheet comes out the other. They don't care
what happens in between.
I'm still curious though, to find out if there is a
syntax for the PIVOT statement, to allow sorting by date
and formatting by different date formats. Ponderous.

Thanks for pointing me in the right direction, John! : )
Joel

 

[Phobos]

Joel,

I think I've cracked it, assuming that:

1. Your imported data is stored in a table called "tblCrosstabTest" and this
table contains a field called "MyDate" of type date.

2. You need to change the SQL in the "qdf.SQL = ..." line at the end of the
routine to your own TRANSFORM SQL statement.

3. Your Crosstab query is called "qryCrosstabTest"

4. You need to set up a reference to DAO to use this code.

When you run the following code, it runs a query on your imported data which
sorts the text-dates by the date-values and saves this string for use in the
TRANSFORM query (this is the "sortedDates" string)

Beware of word-wrapping!


Sub test()
Dim sortedDates As String

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim qdf As QueryDef

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT MyDate, Format(MyDate,""d/m/yy"") as "
& _
"strDate FROM tblCrosstabTest ORDER BY MyDate;")
Set fld = rs.Fields(1)

rs.MoveFirst
While rs.EOF = False
sortedDates = sortedDates & """" & fld.Value & """" & ","
rs.MoveNext
Wend
sortedDates = Left(sortedDates, Len(sortedDates) - 1)

Set qdf = db.QueryDefs("qryCrosstabTest")

qdf.SQL = "TRANSFORM Sum(qryCrosstabTest2.Occurences) AS
SumOfOccurences1" & vbNewLine & _
"SELECT qryCrosstabTest2.Type" & vbNewLine & _
"FROM qryCrosstabTest2" & vbNewLine & _
"GROUP BY qryCrosstabTest2.Type" & vbNewLine & _
"ORDER BY qryCrosstabTest2.Type" & vbNewLine & _
"PIVOT Format([MyDate],""d/m/yy"") In (" & sortedDates &
");"
End Sub





Now open the query...

P