Tricky text sorting !

  • Thread starter Thread starter Grenier
  • Start date Start date
G

Grenier

Hi !
Got a field that may contain 1 or 2 or 3 letters or none.
The letters are V A P
Recordset may look like this:
v
ap
va
(nothing)
p
vp
vpa
a
va

I'd like to be able to make a query that sort all field that contain the
letter a

Merci !
JF Grenier
 
Type this into the Field row in query design:
[f1] Like "*a*"

Substitute your field name for f1.

In the sorting row under this, choose:
Ascending
 
Hi !
Got a field that may contain 1 or 2 or 3 letters or none.
The letters are V A P
Recordset may look like this:
v
ap
va
(nothing)
p
vp
vpa
a
va

I'd like to be able to make a query that sort all field that contain the
letter a

Merci !
JF Grenier
Click to expand...

The term "Sort" means "Put selected records into a specified order" (such as
alphabetical order or numerical order). I think you're using the term in its
colloquial meaning, to select or filter.

To find all records where the field contains the letter "a" in any position
create a Query with a criterion on the field of

LIKE "*a*"

The LIKE operator recognizes wildcards, and the * wildcard means "match any
string of zero or more characters".
 
Sorry but I dont want to filter. I need to sort in order that all fields
that contain the letter 'a' are ranked first then the rest... like so
a
va
ap
vap
vp
v
p
(nothing)
 
From this response, it sounds like you may need to add a lookup table that
would define a SortValue associated with each unique text value in this
field. In your example, you would have two fields in the lookup table:
[Value] and [SortValue].

Value, SortValue
a, 1
va, 2
ap, 3
vap, 4
vp, 5
v, 6
p, 7
(nothing), 8

Establish a relationship between the original table's field and the lookup
table's Value field. Then, in query design, add the SortValue field from the
lookup table. Set the SortValue field to Ascending. This should order the
text values in the way you have defined them.

Grenier said:
Sorry but I dont want to filter. I need to sort in order that all fields
that contain the letter 'a' are ranked first then the rest... like so
a
va
ap
vap
vp
v
p
(nothing)


Allen Browne said:
Type this into the Field row in query design:
[f1] Like "*a*"

Substitute your field name for f1.

In the sorting row under this, choose:
Ascending
Click to expand...
Click to expand...
 
Sorry but I dont want to filter. I need to sort in order that all fields
that contain the letter 'a' are ranked first then the rest... like so
Click to expand...

That was not very clear from your origial post!

Use an ORDER BY clause in your SQL:

ORDER BY InStr([field], "a") > 0, [field]

The expression will be True, or -1, if the field contains an a, 0 if it
doesn't; so this will sort all of the values containing an a as a block,
alphabetically within the block, followed by all those that don't.
 
We said nothing about filtering.

The suggestion was to put the expression in the *Field* row, not in the
Criteria row.

Then you can sort on it.

If you then switch to SQL View, you will see the expression in the ORDER BY
clause: same result as John Vinson suggested.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Grenier said:
Sorry but I dont want to filter. I need to sort in order that all fields
that contain the letter 'a' are ranked first then the rest... like so
a
va
ap
vap
vp
v
p
(nothing)


Allen Browne said:
Type this into the Field row in query design:
[f1] Like "*a*"

Substitute your field name for f1.

In the sorting row under this, choose:
Ascending
Click to expand...
Click to expand...
 
I tried both your suggestion but still having problem. I did not mention that
the field I'm tring to sort is the result of a function that combine 3
boolean field.
Fct: Message([flag1];[flag2];[flag3])

The instr() solution is working as long as I dont put an Ascending or
Descending sort
InStr([Fct], "a") > 0 . if I do, Access is asking for the parameter Fct !

The [Fct] Like "*a*" only evaluate record that have data and like instr() ,
can not put Ascending nor Descending sort.

I know the solution would be to use 3 seperate fields for the flags but this
is a modification to an existing application using a custom listbox and
cannot insert colums without changing 2 tons of code. Was hoping for a quick
fix but thank's a lot for your time. I appreciate.












Allen Browne said:
We said nothing about filtering.

The suggestion was to put the expression in the *Field* row, not in the
Criteria row.

Then you can sort on it.

If you then switch to SQL View, you will see the expression in the ORDER BY
clause: same result as John Vinson suggested.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Grenier said:
Sorry but I dont want to filter. I need to sort in order that all fields
that contain the letter 'a' are ranked first then the rest... like so
a
va
ap
vap
vp
v
p
(nothing)


Allen Browne said:
Type this into the Field row in query design:
[f1] Like "*a*"

Substitute your field name for f1.

In the sorting row under this, choose:
Ascending
Click to expand...
Click to expand...
Click to expand...
 
I tried both your suggestion but still having problem. I did not mention that
the field I'm tring to sort is the result of a function that combine 3
boolean field.
Fct: Message([flag1];[flag2];[flag3])
Click to expand...

Well, that is in fact relevant and the reason you're having the problem.
The instr() solution is working as long as I dont put an Ascending or
Descending sort
InStr([Fct], "a") > 0 . if I do, Access is asking for the parameter Fct !
Click to expand...

You often can't reuse a calculated field by its fieldname - recapitulate the
function call in the Order By clause instead. Or, you may be able to order by
whichever flagX value corresponds to "a".
 
Back
Top