Sorting Problem

B

bw

I deleted a previous question on this, but I now can't view the thread.
Please accept my appology. I need to start again.

Field: DBxInst: ([DBNum]*1000)+[Inst Num]

Field: TheSortOrder:
IIf([Forms]![boxSeatsForITI]![MySortOrder]=1,[BankName],[DBxInst])

The Sorting and Grouping window contains TheSortOrder.

When MySortOrder = 1, the report is sorted correctly by [BankName].
When MysortOrder = 2, the report is not sorted by [BankName], nor is it
sorted by [DBxInst], because the value of DBxInst is WRONG. But if I
put [DBxInst] in the Sorting and Grouping window instead of MySortOrder,
then it will sort correctly by this numeric value.

Help please...
 
D

Duane Hookom

You may need to change your expression to
....,[BankName],Right(Space(30) & [DBxInst],30) )
 
B

bw

Duane!

Well, unbelievably, it works. I have spent days trying to get this to
work, and now that it is, I don't understand it. What difference does
this make? I'm assuming that it has something to do with one value
being text, and the other numeric, but again, why should this make a
difference?

I can't thank you enough for your help.
Thanks much,
Bernie


Duane Hookom said:
You may need to change your expression to
...,[BankName],Right(Space(30) & [DBxInst],30) )
--
Duane Hookom
Microsoft Access MVP


bw said:
I deleted a previous question on this, but I now can't view the
thread.
Please accept my appology. I need to start again.

Field: DBxInst: ([DBNum]*1000)+[Inst Num]

Field: TheSortOrder:
IIf([Forms]![boxSeatsForITI]![MySortOrder]=1,[BankName],[DBxInst])

The Sorting and Grouping window contains TheSortOrder.

When MySortOrder = 1, the report is sorted correctly by [BankName].
When MysortOrder = 2, the report is not sorted by [BankName], nor is
it
sorted by [DBxInst], because the value of DBxInst is WRONG. But if I
put [DBxInst] in the Sorting and Grouping window instead of
MySortOrder,
then it will sort correctly by this numeric value.

Help please...
Click to expand...
Click to expand...
 
B

bw

Oh, by the way, I forgot to ask...Is there a way to make a comment to
this or any field in the Record Source?


Duane Hookom said:
You may need to change your expression to
...,[BankName],Right(Space(30) & [DBxInst],30) )
--
Duane Hookom
Microsoft Access MVP


bw said:
I deleted a previous question on this, but I now can't view the
thread.
Please accept my appology. I need to start again.

Field: DBxInst: ([DBNum]*1000)+[Inst Num]

Field: TheSortOrder:
IIf([Forms]![boxSeatsForITI]![MySortOrder]=1,[BankName],[DBxInst])

The Sorting and Grouping window contains TheSortOrder.

When MySortOrder = 1, the report is sorted correctly by [BankName].
When MysortOrder = 2, the report is not sorted by [BankName], nor is
it
sorted by [DBxInst], because the value of DBxInst is WRONG. But if I
put [DBxInst] in the Sorting and Grouping window instead of
MySortOrder,
then it will sort correctly by this numeric value.

Help please...
Click to expand...
Click to expand...
 
D

Duane Hookom

I try to always return the same data type from any expression including
IIf(). My suggestion converts the number to text and pads it to the left with
spaces. This makes the sorting of the number accurate. I haven't checked this
with either decimals or negative numbers.

There isn't a method for storing comments in a query. I believe Stephen
Lebans might have a solution at www.lebans.com.

--
Duane Hookom
Microsoft Access MVP


bw said:
Duane!

Well, unbelievably, it works. I have spent days trying to get this to
work, and now that it is, I don't understand it. What difference does
this make? I'm assuming that it has something to do with one value
being text, and the other numeric, but again, why should this make a
difference?

I can't thank you enough for your help.
Thanks much,
Bernie


Duane Hookom said:
You may need to change your expression to
...,[BankName],Right(Space(30) & [DBxInst],30) )
--
Duane Hookom
Microsoft Access MVP


bw said:
I deleted a previous question on this, but I now can't view the
thread.
Please accept my appology. I need to start again.

Field: DBxInst: ([DBNum]*1000)+[Inst Num]

Field: TheSortOrder:
IIf([Forms]![boxSeatsForITI]![MySortOrder]=1,[BankName],[DBxInst])

The Sorting and Grouping window contains TheSortOrder.

When MySortOrder = 1, the report is sorted correctly by [BankName].
When MysortOrder = 2, the report is not sorted by [BankName], nor is
it
sorted by [DBxInst], because the value of DBxInst is WRONG. But if I
put [DBxInst] in the Sorting and Grouping window instead of
MySortOrder,
then it will sort correctly by this numeric value.

Help please...
Click to expand...
Click to expand...
Click to expand...
 
B

bw

Duane,

Unfortunately I didn't learn a thing from your suggestion (which worked
perfectly).

I now have another similar problem, which I just can't get to work.
Consider the following:
TheSortOrder:
IIf([Forms]![boxClients]![MySortOrder]=1,[ConvertDate],IIf([Forms]![boxClients]![MySortOrder]=2,[DateCreated],IIf([Forms]![boxClients]![MySortOrder]=3,[Service
Type])))

When Forms]![boxClients]![MySortOrder]=1 or 2 (Date Fields), it works
fine.
When Forms]![boxClients]![MySortOrder]=3 (Text Field), I get "The
expression is typed incorrectly, or is too complex to be evaluated....

If I change the fields in the expression to all the same type (Date or
Text), it works okay.
I have tried your previous suggestion on this expression, but that
doesn't work either.

So if you can provide a solution I would be grateful. But I would also
like to understand why this is happening? How should I approach this in
the future when I again have dissimilar data types?

Thanks again...



Duane Hookom said:
You may need to change your expression to
...,[BankName],Right(Space(30) & [DBxInst],30) )
--
Duane Hookom
Microsoft Access MVP


bw said:
I deleted a previous question on this, but I now can't view the
thread.
Please accept my appology. I need to start again.

Field: DBxInst: ([DBNum]*1000)+[Inst Num]

Field: TheSortOrder:
IIf([Forms]![boxSeatsForITI]![MySortOrder]=1,[BankName],[DBxInst])

The Sorting and Grouping window contains TheSortOrder.

When MySortOrder = 1, the report is sorted correctly by [BankName].
When MysortOrder = 2, the report is not sorted by [BankName], nor is
it
sorted by [DBxInst], because the value of DBxInst is WRONG. But if I
put [DBxInst] in the Sorting and Grouping window instead of
MySortOrder,
then it will sort correctly by this numeric value.

Help please...
Click to expand...
Click to expand...
 
D

Duane Hookom

I'm going to through another curve at you...
TheSortOrder:
Choose([Forms]![boxClients]![MySortOrder], Format([ConvertDate],"yyyymmdd"),
Format([DateCreated],"yyyymmdd"), [Service Type])

--
Duane Hookom
Microsoft Access MVP


bw said:
Duane,

Unfortunately I didn't learn a thing from your suggestion (which worked
perfectly).

I now have another similar problem, which I just can't get to work.
Consider the following:
TheSortOrder:
IIf([Forms]![boxClients]![MySortOrder]=1,[ConvertDate],IIf([Forms]![boxClients]![MySortOrder]=2,[DateCreated],IIf([Forms]![boxClients]![MySortOrder]=3,[Service
Type])))

When Forms]![boxClients]![MySortOrder]=1 or 2 (Date Fields), it works
fine.
When Forms]![boxClients]![MySortOrder]=3 (Text Field), I get "The
expression is typed incorrectly, or is too complex to be evaluated....

If I change the fields in the expression to all the same type (Date or
Text), it works okay.
I have tried your previous suggestion on this expression, but that
doesn't work either.

So if you can provide a solution I would be grateful. But I would also
like to understand why this is happening? How should I approach this in
the future when I again have dissimilar data types?

Thanks again...



Duane Hookom said:
You may need to change your expression to
...,[BankName],Right(Space(30) & [DBxInst],30) )
--
Duane Hookom
Microsoft Access MVP


bw said:
I deleted a previous question on this, but I now can't view the
thread.
Please accept my appology. I need to start again.

Field: DBxInst: ([DBNum]*1000)+[Inst Num]

Field: TheSortOrder:
IIf([Forms]![boxSeatsForITI]![MySortOrder]=1,[BankName],[DBxInst])

The Sorting and Grouping window contains TheSortOrder.

When MySortOrder = 1, the report is sorted correctly by [BankName].
When MysortOrder = 2, the report is not sorted by [BankName], nor is
it
sorted by [DBxInst], because the value of DBxInst is WRONG. But if I
put [DBxInst] in the Sorting and Grouping window instead of
MySortOrder,
then it will sort correctly by this numeric value.

Help please...
Click to expand...
Click to expand...
Click to expand...
 
B

bw

Duane,

It's not a curve, just confirmation that I am learning something with
your help.

I had already did what you suggested, and it now works.
I didn't use the "Choose" function, but it is a much cleaner way to
select the right value instead of using a bunch of IIf statements.

I have learned with this that for some strange reason, one cannot mix
and match field types, and that I should always convert multiple field
types to text.

Do I have it now?
Would you consider this a "bug" in the way Access handles the unlike
fields?

Thanks so much for your help...again.
Bernie

Duane Hookom said:
I'm going to through another curve at you...
TheSortOrder:
Choose([Forms]![boxClients]![MySortOrder],
Format([ConvertDate],"yyyymmdd"),
Format([DateCreated],"yyyymmdd"), [Service Type])

--
Duane Hookom
Microsoft Access MVP


bw said:
Duane,

Unfortunately I didn't learn a thing from your suggestion (which
worked
perfectly).

I now have another similar problem, which I just can't get to work.
Consider the following:
TheSortOrder:
IIf([Forms]![boxClients]![MySortOrder]=1,[ConvertDate],IIf([Forms]![boxClients]![MySortOrder]=2,[DateCreated],IIf([Forms]![boxClients]![MySortOrder]=3,[Service
Type])))

When Forms]![boxClients]![MySortOrder]=1 or 2 (Date Fields), it works
fine.
When Forms]![boxClients]![MySortOrder]=3 (Text Field), I get "The
expression is typed incorrectly, or is too complex to be
evaluated....

If I change the fields in the expression to all the same type (Date
or
Text), it works okay.
I have tried your previous suggestion on this expression, but that
doesn't work either.

So if you can provide a solution I would be grateful. But I would
also
like to understand why this is happening? How should I approach this
in
the future when I again have dissimilar data types?

Thanks again...



Duane Hookom said:
You may need to change your expression to
...,[BankName],Right(Space(30) & [DBxInst],30) )
--
Duane Hookom
Microsoft Access MVP


:

I deleted a previous question on this, but I now can't view the
thread.
Please accept my appology. I need to start again.

Field: DBxInst: ([DBNum]*1000)+[Inst Num]

Field: TheSortOrder:
IIf([Forms]![boxSeatsForITI]![MySortOrder]=1,[BankName],[DBxInst])

The Sorting and Grouping window contains TheSortOrder.

When MySortOrder = 1, the report is sorted correctly by
[BankName].
When MysortOrder = 2, the report is not sorted by [BankName], nor
is
it
sorted by [DBxInst], because the value of DBxInst is WRONG. But
if I
put [DBxInst] in the Sorting and Grouping window instead of
MySortOrder,
then it will sort correctly by this numeric value.

Help please...
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I would not consider this sorting behaviour a "bug". If it worked they way
you originally wanted it to, I would consider that a bug. There are some
report sorting bugs but this isn't one of them.
--
Duane Hookom
Microsoft Access MVP


bw said:
Duane,

It's not a curve, just confirmation that I am learning something with
your help.

I had already did what you suggested, and it now works.
I didn't use the "Choose" function, but it is a much cleaner way to
select the right value instead of using a bunch of IIf statements.

I have learned with this that for some strange reason, one cannot mix
and match field types, and that I should always convert multiple field
types to text.

Do I have it now?
Would you consider this a "bug" in the way Access handles the unlike
fields?

Thanks so much for your help...again.
Bernie

Duane Hookom said:
I'm going to through another curve at you...
TheSortOrder:
Choose([Forms]![boxClients]![MySortOrder],
Format([ConvertDate],"yyyymmdd"),
Format([DateCreated],"yyyymmdd"), [Service Type])

--
Duane Hookom
Microsoft Access MVP


bw said:
Duane,

Unfortunately I didn't learn a thing from your suggestion (which
worked
perfectly).

I now have another similar problem, which I just can't get to work.
Consider the following:
TheSortOrder:
IIf([Forms]![boxClients]![MySortOrder]=1,[ConvertDate],IIf([Forms]![boxClients]![MySortOrder]=2,[DateCreated],IIf([Forms]![boxClients]![MySortOrder]=3,[Service
Type])))

When Forms]![boxClients]![MySortOrder]=1 or 2 (Date Fields), it works
fine.
When Forms]![boxClients]![MySortOrder]=3 (Text Field), I get "The
expression is typed incorrectly, or is too complex to be
evaluated....

If I change the fields in the expression to all the same type (Date
or
Text), it works okay.
I have tried your previous suggestion on this expression, but that
doesn't work either.

So if you can provide a solution I would be grateful. But I would
also
like to understand why this is happening? How should I approach this
in
the future when I again have dissimilar data types?

Thanks again...



You may need to change your expression to
...,[BankName],Right(Space(30) & [DBxInst],30) )
--
Duane Hookom
Microsoft Access MVP


:

I deleted a previous question on this, but I now can't view the
thread.
Please accept my appology. I need to start again.

Field: DBxInst: ([DBNum]*1000)+[Inst Num]

Field: TheSortOrder:
IIf([Forms]![boxSeatsForITI]![MySortOrder]=1,[BankName],[DBxInst])

The Sorting and Grouping window contains TheSortOrder.

When MySortOrder = 1, the report is sorted correctly by
[BankName].
When MysortOrder = 2, the report is not sorted by [BankName], nor
is
it
sorted by [DBxInst], because the value of DBxInst is WRONG. But
if I
put [DBxInst] in the Sorting and Grouping window instead of
MySortOrder,
then it will sort correctly by this numeric value.

Help please...
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem Sorting 3
Don't use more than one duplicate 4
Trouble with my sorting and grouping 4
grouping/sorting limits 2
concatenated field in a report is not sorted 1
need help sorting text by trailing Numbers Value 9
Unable To Use Group & Sort Options 2
Sorting and Grouping 1

Top