Order By

[Guest]

I'm trying to order a form by two fields [Layer] and [Sequence]. I use this
in the report's sorting and grouping:
=Right(" "&[Layer],4) Descending
=Right(" "&[Sequence],4) Descending

How can I apply this to the subform?

Thanks!

 

[Dirk Goldgar]

I'm trying to order a form by two fields [Layer] and [Sequence]. I
use this in the report's sorting and grouping:
=Right(" "&[Layer],4) Descending
=Right(" "&[Sequence],4) Descending

How can I apply this to the subform?

The most reliable way to establish the sort order for the form is to
base it on a query that sorts by the expressions you want. So its SQL
would have an ORDER BY clause like this:

ORDER BY
Right(" " & [Layer], 4) DESC,
Right(" " & [Sequence], 4) DESC

If you needed to switch the form's sort order around on the fly, you
could use the same expressions in assigning to the form's OrderBy
property; e.g.,

Me.OrderBy = _
"Right(' ' & [Layer], 4) DESC, " & _
"Right(' ' & [Sequence], 4) DESC"

Notice that I changed the double-quotes (") to single-quotes (') for
ease in embedding them in the string literal.

What is the purpose of prefixing these field values with 4 spaces and
then sorting by the right-most 4 characters?

 

[Guest]

Thanks, Dirk!

What is the purpose of prefixing these field values with 4 spaces and then sorting by the right-most 4 characters?

I can't exactly recall about the spaces. The fields contain alpha and
numeric values and I believe I had to use the spaces in order to make it
work. The right-most 4 characters assured that I was getting the proper
numeric sequence:

All
Even
20
10
3
2
1

Instead of
1
10
2
20
3
All
Even

Is there an easier/more proper way?

This is mt SQL but I;m not sure where to plug in the ORDER BY clause - my
brain has cramped suddenly:

SELECT tblFGUnitLoadsLayerParameters.txtProfileID,
tblFGUnitLoadsLayerParameters.Layer,
tblFGUnitLoadsLayerParameters.LayerParameters,
tblFGUnitLoadsLayerParameters.Sequence,
tblFGUnitLoadsLayerParameters.Quantity, tblFGUnitLoadsLayerParameters.Comments
FROM tblFGUnitLoadsLayerParameters;

--
www.Marzetti.com

I'm trying to order a form by two fields [Layer] and [Sequence]. I
use this in the report's sorting and grouping:
=Right(" "&[Layer],4) Descending
=Right(" "&[Sequence],4) Descending

How can I apply this to the subform?

The most reliable way to establish the sort order for the form is to
base it on a query that sorts by the expressions you want. So its SQL
would have an ORDER BY clause like this:

ORDER BY
Right(" " & [Layer], 4) DESC,
Right(" " & [Sequence], 4) DESC

If you needed to switch the form's sort order around on the fly, you
could use the same expressions in assigning to the form's OrderBy
property; e.g.,

Me.OrderBy = _
"Right(' ' & [Layer], 4) DESC, " & _
"Right(' ' & [Sequence], 4) DESC"

Notice that I changed the double-quotes (") to single-quotes (') for
ease in embedding them in the string literal.

What is the purpose of prefixing these field values with 4 spaces and
then sorting by the right-most 4 characters?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks, Dirk!


I can't exactly recall about the spaces. The fields contain alpha and
numeric values and I believe I had to use the spaces in order to make
it work. The right-most 4 characters assured that I was getting the
proper numeric sequence:

All
Even
20
10
3
2
1

Instead of
1
10
2
20
3
All
Even

Ah, I see.

Is there an easier/more proper way?

Probably not without changing your table structure. You've got a text
field that you want to sort into numeric sequence, but with exceptions
for certain non-numeric data. That presents a natural problem.
Depending on the nature of the exceptions, you might have used a number
field instead of the text field, but with certain special values to
represent the exceptions; for example, -1 and -2 to represent "All" and
"Even", or 99999 and 99998 (to simplify descending sorts). Of course, a
solution like that requires translation of the field value to the
appropriate text before you display it to the user on a form or report.

Other alternatives would include breaking the field into two fields, one
of which indicates "All", "Even", or "Number", and the other of which (a
numeric field) would contain the numeric value when the first field is
"Number". Or you could use a single text field, but always pad out the
numeric values with leading zeros, so they sort properly -- but here
you're at the mercy of the controls you put on data entry.

In general, your queries will run more efficiently if they don't have to
call a VBA function to convert the field before sorting by it. Whether
efficiency is a problem in this database, with this query, is another
question entirely. No need to waste a lot of time seeking efficiency if
everything is running fine.

This is mt SQL but I;m not sure where to plug in the ORDER BY clause
- my brain has cramped suddenly:

SELECT tblFGUnitLoadsLayerParameters.txtProfileID,
tblFGUnitLoadsLayerParameters.Layer,
tblFGUnitLoadsLayerParameters.LayerParameters,
tblFGUnitLoadsLayerParameters.Sequence,
tblFGUnitLoadsLayerParameters.Quantity,
tblFGUnitLoadsLayerParameters.Comments FROM
tblFGUnitLoadsLayerParameters;

SELECT
txtProfileID,
Layer,
LayerParameters,
Sequence,
Quantity,
Comments
FROM
tblFGUnitLoadsLayerParameters
ORDER BY
Right(" " & [Layer], 4) DESC,
Right(" " & [Sequence], 4) DESC;

 

[Guest]

Thanks again, Dirk!

Those are some good solutions that never crossed my mind! I'll definitely
keep them in mind for the future as things run fine at the moment.

Previous to your post I plugged in the code as you did below but it didn't
return properly. I must've done something wrong. I copy/pasted yours and it
works fine. It must be your Midas touch!

Thanks!

--
www.Marzetti.com

Thanks, Dirk!


I can't exactly recall about the spaces. The fields contain alpha and
numeric values and I believe I had to use the spaces in order to make
it work. The right-most 4 characters assured that I was getting the
proper numeric sequence:

All
Even
20
10
3
2
1

Instead of
1
10
2
20
3
All
Even

Ah, I see.

Is there an easier/more proper way?

Probably not without changing your table structure. You've got a text
field that you want to sort into numeric sequence, but with exceptions
for certain non-numeric data. That presents a natural problem.
Depending on the nature of the exceptions, you might have used a number
field instead of the text field, but with certain special values to
represent the exceptions; for example, -1 and -2 to represent "All" and
"Even", or 99999 and 99998 (to simplify descending sorts). Of course, a
solution like that requires translation of the field value to the
appropriate text before you display it to the user on a form or report.

Other alternatives would include breaking the field into two fields, one
of which indicates "All", "Even", or "Number", and the other of which (a
numeric field) would contain the numeric value when the first field is
"Number". Or you could use a single text field, but always pad out the
numeric values with leading zeros, so they sort properly -- but here
you're at the mercy of the controls you put on data entry.

In general, your queries will run more efficiently if they don't have to
call a VBA function to convert the field before sorting by it. Whether
efficiency is a problem in this database, with this query, is another
question entirely. No need to waste a lot of time seeking efficiency if
everything is running fine.

This is mt SQL but I;m not sure where to plug in the ORDER BY clause
- my brain has cramped suddenly:

SELECT tblFGUnitLoadsLayerParameters.txtProfileID,
tblFGUnitLoadsLayerParameters.Layer,
tblFGUnitLoadsLayerParameters.LayerParameters,
tblFGUnitLoadsLayerParameters.Sequence,
tblFGUnitLoadsLayerParameters.Quantity,
tblFGUnitLoadsLayerParameters.Comments FROM
tblFGUnitLoadsLayerParameters;

SELECT
txtProfileID,
Layer,
LayerParameters,
Sequence,
Quantity,
Comments
FROM
tblFGUnitLoadsLayerParameters
ORDER BY
Right(" " & [Layer], 4) DESC,
Right(" " & [Sequence], 4) DESC;

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

Drat! I just noticed that this is still not right.

The subform returns [Layer] like this:
Even
All
3
20
2
10
1

I'm looking for:
Even
All
20
10
3
2
1

I apologize for the errors I made in communicating this above.

--
www.Marzetti.com

Thanks, Dirk!


I can't exactly recall about the spaces. The fields contain alpha and
numeric values and I believe I had to use the spaces in order to make
it work. The right-most 4 characters assured that I was getting the
proper numeric sequence:

All
Even
20
10
3
2
1

Instead of
1
10
2
20
3
All
Even

Ah, I see.

Is there an easier/more proper way?

Probably not without changing your table structure. You've got a text
field that you want to sort into numeric sequence, but with exceptions
for certain non-numeric data. That presents a natural problem.
Depending on the nature of the exceptions, you might have used a number
field instead of the text field, but with certain special values to
represent the exceptions; for example, -1 and -2 to represent "All" and
"Even", or 99999 and 99998 (to simplify descending sorts). Of course, a
solution like that requires translation of the field value to the
appropriate text before you display it to the user on a form or report.

Other alternatives would include breaking the field into two fields, one
of which indicates "All", "Even", or "Number", and the other of which (a
numeric field) would contain the numeric value when the first field is
"Number". Or you could use a single text field, but always pad out the
numeric values with leading zeros, so they sort properly -- but here
you're at the mercy of the controls you put on data entry.

In general, your queries will run more efficiently if they don't have to
call a VBA function to convert the field before sorting by it. Whether
efficiency is a problem in this database, with this query, is another
question entirely. No need to waste a lot of time seeking efficiency if
everything is running fine.

This is mt SQL but I;m not sure where to plug in the ORDER BY clause
- my brain has cramped suddenly:

SELECT tblFGUnitLoadsLayerParameters.txtProfileID,
tblFGUnitLoadsLayerParameters.Layer,
tblFGUnitLoadsLayerParameters.LayerParameters,
tblFGUnitLoadsLayerParameters.Sequence,
tblFGUnitLoadsLayerParameters.Quantity,
tblFGUnitLoadsLayerParameters.Comments FROM
tblFGUnitLoadsLayerParameters;

SELECT
txtProfileID,
Layer,
LayerParameters,
Sequence,
Quantity,
Comments
FROM
tblFGUnitLoadsLayerParameters
ORDER BY
Right(" " & [Layer], 4) DESC,
Right(" " & [Sequence], 4) DESC;

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Drat! I just noticed that this is still not right.

The subform returns [Layer] like this:
Even
All
3
20
2
10
1

I'm looking for:
Even
All
20
10
3
2
1

I apologize for the errors I made in communicating this above.

No problem, I just assumed that the expressions you were using actually
gave you the sequence you wanted, and didn't think it through
completely. Try this SQL instead:

SELECT
txtProfileID,
Layer,
LayerParameters,
Sequence,
Quantity,
Comments
FROM
tblFGUnitLoadsLayerParameters
ORDER BY
IsNumeric([Layer]) ASC,
Val([Layer]) DESC,
[Layer] DESC,

IsNumeric([Sequence]) ASC,
Val([Sequence]) DESC,
[Sequence] DESC;

I haven't tested that, so it may not be exactly right, but some
combination of those factors for each field should work. What the ORDER
BY clause is saying is, for each field, sort by (1) whether the value of
the field is numeric or not, putting the non-numeric values first, (2)
the numeric value of the field, (which the Val() function will return as
0 if the field is not numeric), in descending order, and (3) the text
value of the field, descending (to get the alpha fields in the right
order).

It's both cumbersome and inefficient, but that may not matter to your
application.

 

[Guest]

I've tinkered with this some more and this returns what I'm looking for:

SELECT tblFGUnitLoadsLayerParameters.txtProfileID,
tblFGUnitLoadsLayerParameters.Layer,
tblFGUnitLoadsLayerParameters.LayerParameters,
tblFGUnitLoadsLayerParameters.Sequence,
tblFGUnitLoadsLayerParameters.Quantity, tblFGUnitLoadsLayerParameters.Comments
FROM tblFGUnitLoadsLayerParameters
ORDER BY Right(" " & [Layer],4) DESC , Right(" " & [Sequence],4) DESC;

This returns the desired:
Even
All
20
10
3
2
1

I tried your previous code but it did return an error as was thought to be
expected:
Data type mismatch in criteria expression. (Error 3464)
The criteria expression in a Find method is attempting to compare a field
with a value whose data type does not match the field’s data type.

I began tinkering with it and arrived at the above. It's odd because the
only real difference I see between this and the first one you wrote is the
use of the 4 space prefixes. As I noted previously I can't recall how I came
up with those but they were needed to make the report return properly. It
appears that the form needs the same...?

 

[Dirk Goldgar]

I've tinkered with this some more and this returns what I'm looking
for:

SELECT tblFGUnitLoadsLayerParameters.txtProfileID,
tblFGUnitLoadsLayerParameters.Layer,
tblFGUnitLoadsLayerParameters.LayerParameters,
tblFGUnitLoadsLayerParameters.Sequence,
tblFGUnitLoadsLayerParameters.Quantity,
tblFGUnitLoadsLayerParameters.Comments FROM
tblFGUnitLoadsLayerParameters
ORDER BY Right(" " & [Layer],4) DESC , Right(" " &
[Sequence],4) DESC;

This returns the desired:
Even
All
20
10
3
2
1

I tried your previous code but it did return an error as was thought
to be expected:
Data type mismatch in criteria expression. (Error 3464)
The criteria expression in a Find method is attempting to compare a
field with a value whose data type does not match the field's data
type.

I think that error message is misleading. I've been tinkering with
this, and I believe that error was raised because the [Sequence] field
is Null in many records, and Val(Null) raises an error. If you still
want to tweak this form, the following revised version of the SQL seems
to work for me:

------ start of SQL ------
SELECT
txtProfileID,
Layer,
LayerParameters,
Sequence,
Quantity,
Comments
FROM
tblFGUnitLoadsLayerParameters
ORDER BY
IsNumeric([Layer]) DESC ,
Val(Nz([Layer])) DESC ,
Layer DESC ,
IsNumeric([Sequence]) DESC ,
Val(Nz([Sequence])) DESC ,
Sequence DESC;
------ end of SQL ------

On the other hand, if the ORDER BY clause using the Right() expressions
is working for you, the only reason I can see to change it is to make
the hierarchy of the sort a little clearer.

 

[Guest]

WOW! Thanks, Dirk! I never would've gotten that! How do you like my database?
It's enough to make you want to puke!

Thanks SO MUCH - this is something that took a REAL brain to resolve!

--
www.Marzetti.com

I've tinkered with this some more and this returns what I'm looking
for:

SELECT tblFGUnitLoadsLayerParameters.txtProfileID,
tblFGUnitLoadsLayerParameters.Layer,
tblFGUnitLoadsLayerParameters.LayerParameters,
tblFGUnitLoadsLayerParameters.Sequence,
tblFGUnitLoadsLayerParameters.Quantity,
tblFGUnitLoadsLayerParameters.Comments FROM
tblFGUnitLoadsLayerParameters
ORDER BY Right(" " & [Layer],4) DESC , Right(" " &
[Sequence],4) DESC;

This returns the desired:
Even
All
20
10
3
2
1

I tried your previous code but it did return an error as was thought
to be expected:
Data type mismatch in criteria expression. (Error 3464)
The criteria expression in a Find method is attempting to compare a
field with a value whose data type does not match the field's data
type.

I think that error message is misleading. I've been tinkering with
this, and I believe that error was raised because the [Sequence] field
is Null in many records, and Val(Null) raises an error. If you still
want to tweak this form, the following revised version of the SQL seems
to work for me:

------ start of SQL ------
SELECT
txtProfileID,
Layer,
LayerParameters,
Sequence,
Quantity,
Comments
FROM
tblFGUnitLoadsLayerParameters
ORDER BY
IsNumeric([Layer]) DESC ,
Val(Nz([Layer])) DESC ,
Layer DESC ,
IsNumeric([Sequence]) DESC ,
Val(Nz([Sequence])) DESC ,
Sequence DESC;
------ end of SQL ------

On the other hand, if the ORDER BY clause using the Right() expressions
is working for you, the only reason I can see to change it is to make
the hierarchy of the sort a little clearer.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)