Re: sort report by year (weird format)

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

thanks - i get where to put it, but i'm getting the error "the
expression you entered has a function containing the wrong number of
arguments"

im going to look at it and try to fix it myself, but can you also look
at it and tell me where its wrong?

thanks


Marshall Barton wrote:
Put it in Sorting and Grouping, instead of what you have
now.
--
Marsh
MVP [MS Access]


mpjm@xxxxxxxxxxxxx wrote:

thanks alot
where do i put that expression though?

Marshall Barton wrote:
No need to convert it or redo any records. I believe that
setting Sorting and Grouping to this expression should give
the desired result:

=IIf(CInt(Left(prop_name,2) >= 30, "19" & prop_name, "20" &
prop_name)


mpjm@xxxxxxxxxxxxx wrote:

the data type is a TEXT field...is there a way to convert it so it
doesnt lose all the data? or a way to sort it without having to redo
all 800 (literally) records?


Marshall Barton wrote:
Well, since you will not tell use what data type the field
is, you'll have to figure out which of the two expressions I
posted earlier to use. Just use the appropriate expression
in Sorting and Grouping instead of the field name by itself.
Be sure to replace my generic name "field" with your field
name prop_name.

Please let me know exactly what you used in Sorting and
Grouping and what result that produced.


mpjm@xxxxxxxxxxxxx wrote:

when the database was first created, they came up with a way for
entering this specific data (way back in the 80s), so i really have no
control over changing it, just getting it to sort the way i want it.

in my Sorting and Grouping window i am sorrting by the field/expression
"prop_num" (proposal number) and in descending sort order.

we were able to sort the correct way in Lotus Approach, but i can't
seem to find a way to do it in access.

Marshall Barton wrote:
What did you enter in the report's Sorting and Grouping
window?

What data type is the "date" field?


mpjm@xxxxxxxxxxxxx wrote:

right now how it is currently sorting, the report goes from "99" (the
year abbrev.) to "84" (the very first one, abbrev.) and then has the
y2k years attached at the bottom going from the newest (06) to oldest
(00).

i want to get the report so it sorts from 06 - 84


Marshall Barton wrote:
No code is needed. If you are still having trouble, please
explain what you tried and what result you are getting.

You did not explain the data type of the date field.

It might also help if you posted what you currently have in
the report's Sorting and Grouping.


mpjm@xxxxxxxxxxxxx wrote:

can you come up with some sort of code to help me along?
the data type is text

Marshall Barton wrote:
mpjm@xxxxxxxxxxxxx wrote:

i am trying to sort a report by the year from newest to oldest.
however there is a catch...

the format of the records i want to sort by is YYMMDD - i am trying to
figure out how to get it sorted from the most current (2006) to the
oldest (in the 80s). any ideas without having to recreate a new column
manually? there are over 500 records, need i say more?


I think there is more to this. The format of a value has
nothing to do with its value. For sorting purposes, the
type of the field is also very important (text fields sort
differently from date or numeric fields).

If your field is a text field, you will need a way to
determine if the two characters for the year are in the 20th
or 21st century (kind of like the Y2K problem). The way
Access does it is analogous to using this expression in
Sorting and Grouping:
=IIf(CInt(Left(field,2) >= 30, "19" & field, "20" & field)

If the field is a Long integer, use this instead:
=IIf(field > 300000, 190000 + field, 200000 + field)

If your field is a Date type field, is should work as is.

.

Relevant Pages

  • Re: sort report by year (weird format)
    ... Marshall Barton wrote: ... in Sorting and Grouping instead of the field name by itself. ... just getting it to sort the way i want it. ... You did not explain the data type of the date field. ...
    (microsoft.public.access.reports)
  • Re: sort report by year (weird format)
    ... Marshall Barton wrote: ... in Sorting and Grouping instead of the field name by itself. ... just getting it to sort the way i want it. ... You did not explain the data type of the date field. ...
    (microsoft.public.access.reports)
  • Re: sort report by year (weird format)
    ... Marshall Barton wrote: ... in Sorting and Grouping instead of the field name by itself. ... just getting it to sort the way i want it. ... You did not explain the data type of the date field. ...
    (microsoft.public.access.reports)
  • Re: sort report by year (weird format)
    ... Put it in Sorting and Grouping, ... Marshall Barton wrote: ... just getting it to sort the way i want it. ... You did not explain the data type of the date field. ...
    (microsoft.public.access.reports)
  • Re: sort report by year (weird format)
    ... since you will not tell use what data type the field ... in Sorting and Grouping instead of the field name by itself. ... just getting it to sort the way i want it. ... Marshall Barton wrote: ...
    (microsoft.public.access.reports)