sort report by year (weird format)

[mpjm]

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?

 

[Marshall Barton]

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.

 

[mpjm]

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

 

[Marshall Barton]

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]

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

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.
--
Marsh
MVP [MS Access]

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

 

[Marshall Barton]

What did you enter in the report's Sorting and Grouping
window?

What data type is the "date" field?

 

[mpjm]

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.

What did you enter in the report's Sorting and Grouping
window?

What data type is the "date" field?
--
Marsh
MVP [MS Access]

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]

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]

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?

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.
--
Marsh
MVP [MS Access]

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]

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]

thanks alot
where do i put that expression though?

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)
--
Marsh
MVP [MS Access]

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]

Put it in Sorting and Grouping, instead of what you have
now.

 

[mpjm]

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

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

thanks alot
where do i put that expression though?

 

[Marshall Barton]

I don't see anything wrong. Double check you spelling of
everything.

 

[mpjm]

everything is spelled right, and i've checked and triple checked the
code - but still giving me the same error:
"the expression you entered has a function containing the wrong number
of arguments"

I don't see anything wrong. Double check you spelling of
everything.
--
Marsh
MVP [MS Access]

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?

 

[Marshall Barton]

Arrggghhh, it's one of those things you have to look at 20
times to see the problem. There is a missing ) for the CInt
function.

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

 

[mpjm]

CInt is for integers, but the prop_num field is text
what should i use?
i'm looking on the internet for another one

Arrggghhh, it's one of those things you have to look at 20
times to see the problem. There is a missing ) for the CInt
function.

=IIf(CInt(Left(prop_name,2)) >= 30, "19" & prop_name, "20" &
prop_name)
--
Marsh
MVP [MS Access]

everything is spelled right, and i've checked and triple checked the
code - but still giving me the same error:
"the expression you entered has a function containing the wrong number
of arguments"

 

[mpjm]

i get the error "data type mismatch" which could mean that we're using
the wrong function (CInt) - any other one you know of that will work
for text fields?

that piece of code works, just i get a data type mismatch

thanks!

CInt is for integers, but the prop_num field is text
what should i use?
i'm looking on the internet for another one

Arrggghhh, it's one of those things you have to look at 20
times to see the problem. There is a missing ) for the CInt
function.

=IIf(CInt(Left(prop_name,2)) >= 30, "19" & prop_name, "20" &
prop_name)
--
Marsh
MVP [MS Access]

everything is spelled right, and i've checked and triple checked the
code - but still giving me the same error:
"the expression you entered has a function containing the wrong number
of arguments"

Marshall Barton wrote:
I don't see anything wrong. Double check you spelling of
everything.


(e-mail address removed) wrote:

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?


Marshall Barton wrote:
Put it in Sorting and Grouping, instead of what you have
now.


(e-mail address removed) wrote:
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)

 

[Marshall Barton]

CInt is the correct function. All we're trying to do is
convert the first two characters in your "date" string to a
number so we can compare it to to the number 30. This
whole exercise should end up converting your text field from
something like:

990102 to 19990102
051130 to 20051130

so the sorting has enough informnation to work with.

 

[Marshall Barton]

I don't see how the CInt can cause that error and "work" at
the same time. AFAIK, CInt will only generate that error if
the string it's trying to convert has a non-numeric
character in it. Double check your data in the report's
record source to see if there is an invalid entry. If the
table has a significant number of records, you might want to
use a query to look for invalid entries in this field:

SELECT *
FROM [put name of your table here]
WHERE prop_name LIKE "*[!1-9]*"