Sorting First & Last Character, 2000 & 2003

[jfcby]

Hello,

I have created a query with data like this:

Field1 Field2 Field3
AHU-A TASK1 DESCRIPTION1
AHU-A TASK2 DESCRIPTION2
AHU-Q TASK3 DESCRIPTION3
AHU-Q TASK4 DESCRIPTION4
AHU-S TASK5 DESCRIPTION5
AHU-S TASK6 DESCRIPTION6
FCU-Q TASK 7 DESCRIPTION7
FCU-S TASK 8 DESCRIPTION8
FCU-A TASK 9 DESCRIPTION9

I need to sort Field1 by first letter Ascending and by last letter in
this order A, S, Q.

How can I sort Field1 first lettar ascending and by last letter in this
order A, S, Q?

Thank you for your help,
jfcby

 

[Douglas J. Steele]

Your SQL would look like:

ORDER BY Left([Field1], 1), Right([Field1], 1) DESC

To accomplish this in the graphical query builder, add two computed fields
Left([Field1]), 1) and Right([Field1], 1) to the grid, and set the Sort
column appropriately.

 

[Joseph Meehan]

Hello,

I have created a query with data like this:

Field1 Field2 Field3
AHU-A TASK1 DESCRIPTION1
AHU-A TASK2 DESCRIPTION2
AHU-Q TASK3 DESCRIPTION3
AHU-Q TASK4 DESCRIPTION4
AHU-S TASK5 DESCRIPTION5
AHU-S TASK6 DESCRIPTION6
FCU-Q TASK 7 DESCRIPTION7
FCU-S TASK 8 DESCRIPTION8
FCU-A TASK 9 DESCRIPTION9

I need to sort Field1 by first letter Ascending and by last letter in
this order A, S, Q.

How can I sort Field1 first lettar ascending and by last letter in
this order A, S, Q?

Thank you for your help,
jfcby

While it is not the answer to your question the following thoughts might
eliminate the need for your question and maybe many more in the future.

Field 1 looks like it should be two fields maybe even in two different
tables. The first three characters and the last characters appear to be
different types of information. Maybe group and subgroup or location and
job assignment.

Field 2 looks like it is a list of tasks by number. You don't need the
word "Task" in each record if they are all going to be Tasks. Likewise for
field 3.

You might do it that way in Excel but Access would rather just store the
information more logically. If the first field were broken down into two
fields (both in the same table or in two related tables) you likely would
have not needed to ask the question.

 

[John Nurick]

Not quite, Doug. Jcfby wanted a custom sort order for the last
character: A, S, Q rather than A, Q, S. This might do, provided those
three letters are the only possible ones:

ORDER BY Left([Field1], 1),
IIF(Right([Field1], 1)='S', 'B', Right([Field1],1))

Your SQL would look like:

ORDER BY Left([Field1], 1), Right([Field1], 1) DESC

To accomplish this in the graphical query builder, add two computed fields
Left([Field1]), 1) and Right([Field1], 1) to the grid, and set the Sort
column appropriately.

 

[jfcby]

Hello Douglas,

I am new to Access only 2 weeks. I have a couple of questions:

Is this two different sort options?

Your SQL would look like:
ORDER BY Left([Field1], 1), Right([Field1], 1) DESC

Where do I put this code for it to sort?

To accomplish this in the graphical query builder, add two computed fields
Left([Field1]), 1) and Right([Field1], 1) to the grid,

Does this code go in the Field on the query design view?

and set the Sort column appropriately.

Do you mean to set the sort to ascending or descending?

This is what I've tried but it does not sort properly.
On my query in design view I've put the Left([Field1], 1) in the Field
and set sort to Ascending and it sorted correct alphabetically. Then I
put the Right([Field1], 1) in another Field, set the sort to
Descending it did not sort correct S, Q, A and I set the sort to
Ascending it did not sort correct A, Q, S but I want it to sort A, S,
Q.

Thank you for your help,
jfcby

 

[Douglas J. Steele]

Hello Douglas,

I am new to Access only 2 weeks. I have a couple of questions:

Is this two different sort options?

Not really: it's simply two different ways of expressing the same thing.

Your SQL would look like:
ORDER BY Left([Field1], 1), Right([Field1], 1) DESC

Where do I put this code for it to sort?

You'd go into the SQL View (View | SQL View) and work directly with the SQL.
However, see the follow-up post John Nurick made, correctly my oversight.

To accomplish this in the graphical query builder, add two computed
fields
Left([Field1]), 1) and Right([Field1], 1) to the grid,

Does this code go in the Field on the query design view?

and set the Sort column appropriately.

Do you mean to set the sort to ascending or descending?

This is what I've tried but it does not sort properly.
On my query in design view I've put the Left([Field1], 1) in the Field
and set sort to Ascending and it sorted correct alphabetically. Then I
put the Right([Field1], 1) in another Field, set the sort to
Descending it did not sort correct S, Q, A and I set the sort to
Ascending it did not sort correct A, Q, S but I want it to sort A, S,
Q.

Yeah, I didn't read as closely as I should have.

As John correctly suggested,

ORDER BY Left([Field1], 1),
IIF(Right([Field1], 1)='S', 'B', Right([Field1],1))

will give you the sort you're asking for.

 

[Douglas J. Steele]

That's what I get to skimming too superficially.

Thanks, John.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Not quite, Doug. Jcfby wanted a custom sort order for the last
character: A, S, Q rather than A, Q, S. This might do, provided those
three letters are the only possible ones:

ORDER BY Left([Field1], 1),
IIF(Right([Field1], 1)='S', 'B', Right([Field1],1))

Your SQL would look like:

ORDER BY Left([Field1], 1), Right([Field1], 1) DESC

To accomplish this in the graphical query builder, add two computed fields
Left([Field1]), 1) and Right([Field1], 1) to the grid, and set the Sort
column appropriately.

 

[jfcby]

Hello Douglas,

This is my SQL View:

SELECT dbTEST.[Part Number], dbTEST.[Price $], dbTEST.[Part
Description]
ORDER BY Left([Part Description], 1),
IIF(Right([Part Description], 1)='S', 'B', Right([Part
Description],1))
FROM dbTEST
WHERE (((dbTEST.[Part Description]) Like "*[*]?*"));

But I'm getting this error message:

Syntax error (missing operator) in query expersion
'dbTEST.[Part Description]
ORDER BY Left([Part Description], 1)'.

Then when I click ok it highlights ORDER

Thank you for you help,
jfcby

Hello Douglas,

I am new to Access only 2 weeks. I have a couple of questions:

Is this two different sort options?

Not really: it's simply two different ways of expressing the same thing.

Your SQL would look like:
ORDER BY Left([Field1], 1), Right([Field1], 1) DESC

Where do I put this code for it to sort?

You'd go into the SQL View (View | SQL View) and work directly with the SQL.
However, see the follow-up post John Nurick made, correctly my oversight.

To accomplish this in the graphical query builder, add two computed
fields
Left([Field1]), 1) and Right([Field1], 1) to the grid,

Does this code go in the Field on the query design view?

and set the Sort column appropriately.

Do you mean to set the sort to ascending or descending?

This is what I've tried but it does not sort properly.
On my query in design view I've put the Left([Field1], 1) in the Field
and set sort to Ascending and it sorted correct alphabetically. Then I
put the Right([Field1], 1) in another Field, set the sort to
Descending it did not sort correct S, Q, A and I set the sort to
Ascending it did not sort correct A, Q, S but I want it to sort A, S,
Q.

Yeah, I didn't read as closely as I should have.

As John correctly suggested,

ORDER BY Left([Field1], 1),
IIF(Right([Field1], 1)='S', 'B', Right([Field1],1))

will give you the sort you're asking for.

 

[jfcby]

Hello Douglas,

I figured out what I was doing wrong this is the working SQL:

SELECT dbTEST.[Part Number], dbTEST.[Price $], dbTEST.[Part
Description]
FROM dbTEST
ORDER BY Left([Part Description],2), IIf(Right([Part
Description],1)='S','B',Right([Part Description],1));

Thank you for your help,
jfcby

Hello Douglas,

I am new to Access only 2 weeks. I have a couple of questions:

Is this two different sort options?

Not really: it's simply two different ways of expressing the same thing.

Your SQL would look like:
ORDER BY Left([Field1], 1), Right([Field1], 1) DESC

Where do I put this code for it to sort?

You'd go into the SQL View (View | SQL View) and work directly with the SQL.
However, see the follow-up post John Nurick made, correctly my oversight.

To accomplish this in the graphical query builder, add two computed
fields
Left([Field1]), 1) and Right([Field1], 1) to the grid,

Does this code go in the Field on the query design view?

and set the Sort column appropriately.

Do you mean to set the sort to ascending or descending?

This is what I've tried but it does not sort properly.
On my query in design view I've put the Left([Field1], 1) in the Field
and set sort to Ascending and it sorted correct alphabetically. Then I
put the Right([Field1], 1) in another Field, set the sort to
Descending it did not sort correct S, Q, A and I set the sort to
Ascending it did not sort correct A, Q, S but I want it to sort A, S,
Q.

Yeah, I didn't read as closely as I should have.

As John correctly suggested,

ORDER BY Left([Field1], 1),
IIF(Right([Field1], 1)='S', 'B', Right([Field1],1))

will give you the sort you're asking for.

 

[Douglas J. Steele]

If you need the WHERE condition you used to have, try:

SELECT dbTEST.[Part Number], dbTEST.[Price $],
dbTEST.[Part Description]
FROM dbTEST
WHERE (((dbTEST.[Part Description]) Like "*[*]?*"))
ORDER BY Left([Part Description],2),
IIf(Right([Part Description],1)='S','B',Right([Part Description],1));


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Douglas,

I figured out what I was doing wrong this is the working SQL:

SELECT dbTEST.[Part Number], dbTEST.[Price $], dbTEST.[Part
Description]
FROM dbTEST
ORDER BY Left([Part Description],2), IIf(Right([Part
Description],1)='S','B',Right([Part Description],1));

Thank you for your help,
jfcby

Hello Douglas,

I am new to Access only 2 weeks. I have a couple of questions:

Is this two different sort options?

Not really: it's simply two different ways of expressing the same thing.

Your SQL would look like:
ORDER BY Left([Field1], 1), Right([Field1], 1) DESC

Where do I put this code for it to sort?

You'd go into the SQL View (View | SQL View) and work directly with the
SQL.
However, see the follow-up post John Nurick made, correctly my oversight.

To accomplish this in the graphical query builder, add two computed
fields
Left([Field1]), 1) and Right([Field1], 1) to the grid,

Does this code go in the Field on the query design view?

and set the Sort column appropriately.

Do you mean to set the sort to ascending or descending?

This is what I've tried but it does not sort properly.
On my query in design view I've put the Left([Field1], 1) in the Field
and set sort to Ascending and it sorted correct alphabetically. Then I
put the Right([Field1], 1) in another Field, set the sort to
Descending it did not sort correct S, Q, A and I set the sort to
Ascending it did not sort correct A, Q, S but I want it to sort A, S,
Q.

Yeah, I didn't read as closely as I should have.

As John correctly suggested,

ORDER BY Left([Field1], 1),
IIF(Right([Field1], 1)='S', 'B', Right([Field1],1))

will give you the sort you're asking for.

 

[jfcby]

Thanks everone for your help

jfcby

Hello Douglas,

I figured out what I was doing wrong this is the working SQL:

SELECT dbTEST.[Part Number], dbTEST.[Price $], dbTEST.[Part
Description]
FROM dbTEST
ORDER BY Left([Part Description],2), IIf(Right([Part
Description],1)='S','B',Right([Part Description],1));

Thank you for your help,
jfcby

Hello Douglas,

I am new to Access only 2 weeks. I have a couple of questions:

Is this two different sort options?

Not really: it's simply two different ways of expressing the same thing.

Your SQL would look like:
ORDER BY Left([Field1], 1), Right([Field1], 1) DESC

Where do I put this code for it to sort?

You'd go into the SQL View (View | SQL View) and work directly with the SQL.
However, see the follow-up post John Nurick made, correctly my oversight.

To accomplish this in the graphical query builder, add two computed
fields
Left([Field1]), 1) and Right([Field1], 1) to the grid,

Does this code go in the Field on the query design view?

and set the Sort column appropriately.

Do you mean to set the sort to ascending or descending?

This is what I've tried but it does not sort properly.
On my query in design view I've put the Left([Field1], 1) in the Field
and set sort to Ascending and it sorted correct alphabetically. Then I
put the Right([Field1], 1) in another Field, set the sort to
Descending it did not sort correct S, Q, A and I set the sort to
Ascending it did not sort correct A, Q, S but I want it to sort A, S,
Q.

Yeah, I didn't read as closely as I should have.

As John correctly suggested,

ORDER BY Left([Field1], 1),
IIF(Right([Field1], 1)='S', 'B', Right([Field1],1))

will give you the sort you're asking for.

 

[jfcby]

Hello Douglas,

If you need the WHERE condition you used to have, try:

SELECT dbTEST.[Part Number], dbTEST.[Price $],
dbTEST.[Part Description]
FROM dbTEST
WHERE (((dbTEST.[Part Description]) Like "*[*]?*"))
ORDER BY Left([Part Description],2),
IIf(Right([Part Description],1)='S','B',Right([Part Description],1));

The above code works great.

Thank you for your help,
jfcby