Date Period: Format([Call date],"mmm yy") - Sort Date

[Adam]

Hi All,

I have a formula in a query as follows: Date Period: Format([Call date],"mmm
yy")

This is then "grouped"

Is there a way I can have this list sorted in Date order rather than the
ascending order it currently is ?

 

[Dirk Goldgar]

Hi All,

I have a formula in a query as follows: Date Period: Format([Call
date],"mmm yy")

This is then "grouped"

Is there a way I can have this list sorted in Date order rather than
the ascending order it currently is ?

Try this:

Create another calculated field with these properties in the query grid:

Field: SortDate: CDate(Format([Call date],"mmm dd"))
Total: Expression
Sort: Ascending
Show: (unchecked)

I think that should do it.

 

[Adam]

This does not work.

Hi All,

I have a formula in a query as follows: Date Period: Format([Call
date],"mmm yy")

This is then "grouped"

Is there a way I can have this list sorted in Date order rather than
the ascending order it currently is ?

Try this:

Create another calculated field with these properties in the query grid:

Field: SortDate: CDate(Format([Call date],"mmm dd"))
Total: Expression
Sort: Ascending
Show: (unchecked)

I think that should do it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Douglas J. Steele]

Create a computed field that formats the date as mmdd and sort on that
field.

In other words, what Dirk suggested, except

SortDate: Format([Call date],"mmdd"))

instead of

SortDate: CDate(Format([Call date],"mmm dd"))

If that doesn't work, post back explaining what "does not work" means. In
other words, are you getting an error? (if so, what's the error). Is there
no error, but the records are sorted incorrectly (if so, is there a pattern
you can discern to the sort order). If not, is smoke billowing out of your
PC? <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This does not work.

Hi All,

I have a formula in a query as follows: Date Period: Format([Call
date],"mmm yy")

This is then "grouped"

Is there a way I can have this list sorted in Date order rather than
the ascending order it currently is ?

Try this:

Create another calculated field with these properties in the query grid:

Field: SortDate: CDate(Format([Call date],"mmm dd"))
Total: Expression
Sort: Ascending
Show: (unchecked)

I think that should do it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

This does not work.

Hi All,

I have a formula in a query as follows: Date Period: Format([Call
date],"mmm yy")

This is then "grouped"

Is there a way I can have this list sorted in Date order rather than
the ascending order it currently is ?

Try this:

Create another calculated field with these properties in the query
grid:

Field: SortDate: CDate(Format([Call date],"mmm dd"))
Total: Expression
Sort: Ascending
Show: (unchecked)

I think that should do it.

It worked for me in my test. In what way does it "not work"? Maybe I
didn't understand what you wanted.

 

[Adam]

Hi,

I am getting the below error:

You tried to execute a query that doesn't include the specified expression
<name> as part of an aggregate function. (Error 3122)

This does not work.

Hi All,

I have a formula in a query as follows: Date Period: Format([Call
date],"mmm yy")

This is then "grouped"

Is there a way I can have this list sorted in Date order rather than
the ascending order it currently is ?

Try this:

Create another calculated field with these properties in the query
grid:

Field: SortDate: CDate(Format([Call date],"mmm dd"))
Total: Expression
Sort: Ascending
Show: (unchecked)

I think that should do it.

It worked for me in my test. In what way does it "not work"? Maybe I
didn't understand what you wanted.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hi,

I am getting the below error:

You tried to execute a query that doesn't include the specified
expression <name> as part of an aggregate function. (Error 3122)

This does not work.

:

Hi All,

I have a formula in a query as follows: Date Period: Format([Call
date],"mmm yy")

This is then "grouped"

Is there a way I can have this list sorted in Date order rather
than the ascending order it currently is ?

Try this:

Create another calculated field with these properties in the query
grid:

Field: SortDate: CDate(Format([Call date],"mmm dd"))
Total: Expression
Sort: Ascending
Show: (unchecked)

I think that should do it.

It worked for me in my test. In what way does it "not work"? Maybe
I didn't understand what you wanted.

Please post the SQL of the query.