date format/sort problem

[JohnLute]

I have a formatted field:
ProdDate1: (Format([ProdDate],"mmm"" '""yy"))

This works fine and the query sorts in chronological order however when I
use this same field in another query it sorts alphabetically.

How can I resolve this so that the field sorts chronologically?

Thanks for your help!

 

[scubadiver]

Are you sure the field in the other query is a "date/time" field and not a
"text" field?

 

[JohnLute]

The field in the table is [ProdDate] and is a Date/Time type with the format
mmm" '"yy as it must contain month/year only.

In my first query I need to format it:
ProdDate1: (Format([ProdDate],"mmm"" '""yy"))

as it's used to join another query at:
DateReceived1: (Format([DateReceived],"mmm"" '""yy"))

[DateReceived] is also a Date/Time type with a Short Date format and
99/99/00;0 input mask. In order to join these two fields I needed to format
them the same otherwise the "Type mismatch in expression" error returned.

Hope that makes sense!

--
www.Marzetti.com

Are you sure the field in the other query is a "date/time" field and not a
"text" field?

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video

I have a formatted field:
ProdDate1: (Format([ProdDate],"mmm"" '""yy"))

This works fine and the query sorts in chronological order however when I
use this same field in another query it sorts alphabetically.

How can I resolve this so that the field sorts chronologically?

Thanks for your help!

 

[JohnLute]

To further clarify. Here's my first query qryFacsCasesProduced100M:
SELECT tblFacilitiesCasesProduced.txtFacilityID, (Format([ProdDate],"mmm""
'""yy")) AS ProdDate1, tblFacilitiesCasesProduced.TotalCasesProduced,
([TotalCasesProduced]/100000) AS Per100M
FROM tblFacilitiesCasesProduced
WHERE
(((tblFacilitiesCasesProduced.txtFacilityID)=[Forms]![frmComplaintQueriesReports]![cbFacility]))
OR ((([Forms]![frmComplaintQueriesReports]![cbFacility]) Is Null));

This query returns [ProdDate1] in chronological order. No problems. It's
then used in qryCompsFacsPerMonthSum100M:
SELECT qryFacsCasesProduced100M.ProdDate1,
qryFacsCasesProduced100M.TotalCasesProduced, ([Per100M]/[SumOfTotalComps1])
AS Per100MSum
FROM qryFacsCasesProduced100M INNER JOIN qryCompsFacsPerMonthSum ON
qryFacsCasesProduced100M.ProdDate1 = qryCompsFacsPerMonthSum.DateReceived1
GROUP BY qryFacsCasesProduced100M.ProdDate1,
qryFacsCasesProduced100M.TotalCasesProduced, ([Per100M]/[SumOfTotalComps1]);

In this query [ProdDate1] sorts alphabetically. I need it to be
chronological.

Whew!

--
www.Marzetti.com

The field in the table is [ProdDate] and is a Date/Time type with the format
mmm" '"yy as it must contain month/year only.

In my first query I need to format it:
ProdDate1: (Format([ProdDate],"mmm"" '""yy"))

as it's used to join another query at:
DateReceived1: (Format([DateReceived],"mmm"" '""yy"))

[DateReceived] is also a Date/Time type with a Short Date format and
99/99/00;0 input mask. In order to join these two fields I needed to format
them the same otherwise the "Type mismatch in expression" error returned.

Hope that makes sense!

--
www.Marzetti.com

Are you sure the field in the other query is a "date/time" field and not a
"text" field?

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video

I have a formatted field:
ProdDate1: (Format([ProdDate],"mmm"" '""yy"))

This works fine and the query sorts in chronological order however when I
use this same field in another query it sorts alphabetically.

How can I resolve this so that the field sorts chronologically?

Thanks for your help!

 

[Dale Fye]

A little tutorial.

There is no such thing as a Date/Time data type which only contains
month/year data. Dates/Times are stored as double precision numbers (the
number to the left of the decimal represents the number of days since Dec 30,
1899, the number to the right of the decimal point indicates the percentage
of the day that has elapsed).

The Format propety of the field determines how the data in the field is
displayed, but does not actually effect the value. The values in the field
are determined by the method used to fill the field. If your program uses
the Now() function to fill these fields, then it stores a number with date
and time componets; if you use the Date() function it only stores the date
portion and sets the decimal portion of the value to 0.

If you are concerned about joining this field to another field, based on the
month and year, then using the Format command to "display" the information as
text month and two digit year makes sense, but if you sort on this format,
you will get:

Apr 06, Aug 06, Dec 06, Feb 06, Jan 06, Jul 06, Jun 06, Mar 06, May 06, Nov
06,
Oct 06, Sep 06

If you really want to sort by month and year, and have them come out in the
right order you need to sort on Format([yourDateField], "yyyymm") or "mmyyyy"
if you want all of the January stuff together, regardless of the year.

--
Email address is not valid.
Please reply to newsgroup only.

The field in the table is [ProdDate] and is a Date/Time type with the format
mmm" '"yy as it must contain month/year only.

In my first query I need to format it:
ProdDate1: (Format([ProdDate],"mmm"" '""yy"))

as it's used to join another query at:
DateReceived1: (Format([DateReceived],"mmm"" '""yy"))

[DateReceived] is also a Date/Time type with a Short Date format and
99/99/00;0 input mask. In order to join these two fields I needed to format
them the same otherwise the "Type mismatch in expression" error returned.

Hope that makes sense!

--
www.Marzetti.com

Are you sure the field in the other query is a "date/time" field and not a
"text" field?

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video

I have a formatted field:
ProdDate1: (Format([ProdDate],"mmm"" '""yy"))

This works fine and the query sorts in chronological order however when I
use this same field in another query it sorts alphabetically.

How can I resolve this so that the field sorts chronologically?

Thanks for your help!

 

[JohnLute]

Thanks for the informative tutorial, Dale!

I've never had to deal with date fields like this so it's relatively new.
Much clearer now and I'm already making some progress!

--
www.Marzetti.com

A little tutorial.

There is no such thing as a Date/Time data type which only contains
month/year data. Dates/Times are stored as double precision numbers (the
number to the left of the decimal represents the number of days since Dec 30,
1899, the number to the right of the decimal point indicates the percentage
of the day that has elapsed).

The Format propety of the field determines how the data in the field is
displayed, but does not actually effect the value. The values in the field
are determined by the method used to fill the field. If your program uses
the Now() function to fill these fields, then it stores a number with date
and time componets; if you use the Date() function it only stores the date
portion and sets the decimal portion of the value to 0.

If you are concerned about joining this field to another field, based on the
month and year, then using the Format command to "display" the information as
text month and two digit year makes sense, but if you sort on this format,
you will get:

Apr 06, Aug 06, Dec 06, Feb 06, Jan 06, Jul 06, Jun 06, Mar 06, May 06, Nov
06,
Oct 06, Sep 06

If you really want to sort by month and year, and have them come out in the
right order you need to sort on Format([yourDateField], "yyyymm") or "mmyyyy"
if you want all of the January stuff together, regardless of the year.

--
Email address is not valid.
Please reply to newsgroup only.

The field in the table is [ProdDate] and is a Date/Time type with the format
mmm" '"yy as it must contain month/year only.

In my first query I need to format it:
ProdDate1: (Format([ProdDate],"mmm"" '""yy"))

as it's used to join another query at:
DateReceived1: (Format([DateReceived],"mmm"" '""yy"))

[DateReceived] is also a Date/Time type with a Short Date format and
99/99/00;0 input mask. In order to join these two fields I needed to format
them the same otherwise the "Type mismatch in expression" error returned.

Hope that makes sense!

--
www.Marzetti.com

Are you sure the field in the other query is a "date/time" field and not a
"text" field?

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video


:

I have a formatted field:
ProdDate1: (Format([ProdDate],"mmm"" '""yy"))

This works fine and the query sorts in chronological order however when I
use this same field in another query it sorts alphabetically.

How can I resolve this so that the field sorts chronologically?

Thanks for your help!