Sort Problem

  • Thread starter Thread starter RFJ
  • Start date Start date
R

RFJ

I want to sort on a field ("Data") that contains the numbers 0 to 9. Sounds
straightforward but the sort doesn't work as expected.

What appears to be happening is that information in the "Data" field is
originally entered via a look up table and, although what is displayed in
the query is the numerical value, the actual sort is being done on the look
up value which was set in the field properties of the "Data" table.

There are no lookups set (or able to change) in the actual query.

Can SKS help out.

TIA
 
It sounds like you are describing one of the reasons NOT to use a lookup
datatype in a table definition: because what is stored and what is
displayed are not the same. This leads to problems knowing what to search
for in queries!

A check in the tablesdbdesign newsgroup will reveal a considerable consensus
against using lookup-type data fields. The typically-proposed solution is
to change the field type to the data type that corresponds to the key field
in the table being used for lookup (e.g., a lookup table with an Autonumber
key field would need a corresponding LongInt data field in the table using
that key -- like your described situation).

Also, use of the fieldname "Data" may be confusing Access -- I'm not
certain, but suspect this is a reserved word.
 
Sort on Val([Data]). I would make a separate field in the
query like Data2:Val([Data]) and sort on that. Good luck

Mike
 
Tx Mike,

I tried setting up the new field as you suggest but it just seems to return
a zero. The fieldname I'm entering, Survey_Job_Level, is /definitely/
correct. The whole expression I'm entering is :

Level Value: Val([Survey_Job_Level])

The Survey_Job_Level field in the table holding the information (and the one
I'm trying to get the numerical value) has a look-up property pointing to
another table. That table (Survey Levels) has two fields - a numerical value
(the one I want) and the associated descriptor (the one that is currently
driving the sort sequence even though it is the numerical value that is
displayed).

For info, if it helps, the application is a salary survey where the levels
are

1 CEO
2 Deputy CEO
3 Senior Manager

etc

Is there something else I'm missing - any advice would be appreciated.

Rob


Sort on Val([Data]). I would make a separate field in the
query like Data2:Val([Data]) and sort on that. Good luck

Mike
-----Original Message-----
I want to sort on a field ("Data") that contains the numbers 0 to 9. Sounds
straightforward but the sort doesn't work as expected.

What appears to be happening is that information in the "Data" field is
originally entered via a look up table and, although what is displayed in
the query is the numerical value, the actual sort is being done on the look
up value which was set in the field properties of the "Data" table.

There are no lookups set (or able to change) in the actual query.

Can SKS help out.

TIA


.
Click to expand...
Click to expand...
 
Try having both tables open in your query, they should be
linked b the job level fields. Sort from the job level
table not the one you are currently using. Hope this
helps.

Mike
-----Original Message-----
Tx Mike,

I tried setting up the new field as you suggest but it just seems to return
a zero. The fieldname I'm entering, Survey_Job_Level, is /definitely/
correct. The whole expression I'm entering is :

Level Value: Val([Survey_Job_Level])

The Survey_Job_Level field in the table holding the information (and the one
I'm trying to get the numerical value) has a look-up property pointing to
another table. That table (Survey Levels) has two fields - a numerical value
(the one I want) and the associated descriptor (the one that is currently
driving the sort sequence even though it is the numerical value that is
displayed).

For info, if it helps, the application is a salary survey where the levels
are

1 CEO
2 Deputy CEO
3 Senior Manager

etc

Is there something else I'm missing - any advice would be appreciated.

Rob


Sort on Val([Data]). I would make a separate field in the
query like Data2:Val([Data]) and sort on that. Good luck

Mike
-----Original Message-----
I want to sort on a field ("Data") that contains the numbers 0 to 9. Sounds
straightforward but the sort doesn't work as expected.

What appears to be happening is that information in the "Data" field is
originally entered via a look up table and, although
Click to expand...
what
is displayed in
the query is the numerical value, the actual sort is being done on the look
up value which was set in the field properties of the "Data" table.

There are no lookups set (or able to change) in the actual query.

Can SKS help out.

TIA


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top