Record Sorting

[dan.cawthorne]

Hello and good morning,

can some help me in where i am going wrong,

I'm trying to sort records in a certain way, but its not working,

I Created a query which represents a table and on two fields set as
ascending order

the first column i had was called ProjectQNo: Which has numbers such
as Q077707 but when i hit the year 2008 the QNo was set to Q000108
What happened was all the Qno Numbers lower than say Q077707 where but
at the top of the table. No Good when i open a form and i a want the
latest record to apppear at the end

so i created a Query and Created a ProjectYear Field and had the
Date() format so it created the date automactily and the sorted
projects by ProjectYear: and then By ProjectQNo:

Was Working great untill the 02/02/08 its now its all the projects
created on or after the 02/02/08 in middle of all the January
Projects.


Whats Happening?

 

[dan.cawthorne]

Hello and good morning,

can some help me in where i am going wrong,

I'm trying to sort records in a certain way, but its not working,

I Created a query which represents a table and on two fields set as
ascending order

the first column i had was called ProjectQNo: Which has numbers such
as Q077707 but when i hit the year 2008 the QNo was set to Q000108
What happened was all the Qno Numbers lower than say Q077707 where but
at the top of the table. No Good when i open a form and i a want the
latest record to apppear at the end

so i created a Query and Created a ProjectYear Field and had the
Date() format so it created the date automactily and the sorted
projects by ProjectYear: and then By ProjectQNo:

Was Working great untill the 02/02/08 its now its all the projects
created on or after the 02/02/08 in middle of all the January
Projects.

Whats Happening?

Oh I think I know Whats Happening, Its filtering on the day and Not
Year, How to i get the field to automaticly just create the Year as
2008?

 

[KARL DEWEY]

I Created a query which represents a table and on two fields set as
ascending orderAscending means the values get larger as you go down the list. To have
latest at top you need descending - larger - lower.
Your number format will do neither as it is as you have the most significant
digits, 07 & 08 as the right most character.
Use a calculated field like this ---
Year: Right([ProjectQNo], 2)
And then sort on the ProjectQNo field place to the right of the calculated
field.

 

[John W. Vinson]

the first column i had was called ProjectQNo: Which has numbers such
as Q077707 but when i hit the year 2008 the QNo was set to Q000108
What happened was all the Qno Numbers lower than say Q077707 where but
at the top of the table. No Good when i open a form and i a want the
latest record to apppear at the end

The 07 and 08 at the end of this composite field mean "the year" to you. They
do NOT mean "the year" to Access; the text string "Q000108" in fact is smaller
(in alphabetical, left to right sort order) than the string "Q077707". Access
doesn't see a separate higher priority meaning to the last two characters; as
far as it's concerned, "Q1252ZZ" would be just as reasonable a value.

You're paying the penalty for using what is called an "intelligent key". It's
not a compliment, unfortunately! You're trying to store "intelligence" in what
should be an atomic, single value field. You actually have TWO - or perhaps 3,
depending on whether the first letter is always Q - values jammed into one
field: a sequential number and a date. Ideally you should store this in two
fields (an integer year, values 2007 or 2008), and an integer sequence number
(values 1 or 777); you can concatenate these values for display purposes with
an expression like

QNo: "Q" & Format([SeqNo], "0000") & Format([YearNo] MOD 100, "00")

To sort your intelligent key chronologically you must split it into its
components with a calculated field like

SortKey: Right ([QNo], 2) & Mid([QNo], 2, 4)

and sort by this field.

John W. Vinson [MVP]