changing sort field

  • Thread starter Thread starter JIM.H.
  • Start date Start date
J

JIM.H.

Hello,
On my report myId is a group, and myFName and myLName are
detail field names. By Default detail is sorted by
myFName, now if myId=10 I want to sort only this group by
myLName. How can I do that?
Thanks,
Jim.
 
JIM.H. said:
On my report myId is a group, and myFName and myLName are
detail field names. By Default detail is sorted by
myFName, now if myId=10 I want to sort only this group by
myLName.
Click to expand...


That sounds like a strange thing to do, maybe you're too far
down this path and should back up to explain what the real
issue is.

Anyway, the Sorting and Grouping window allows you to
specify and expression in place of a field name. For
example, it's fairly common to sort on an expression like:
=[myLName] & "~" & [myFName]

As for your question, I think you want to use:
=IIf(myId=10, myLName, myFName)
 
Field names are examples, they are not really names. I am
trying to get data from a table and print a sheet for
different people and they they all want a different way of
sorting. I did not want to use different reports. My
group, ID in the example, is first sorting and I do not
want to change it, then I need to sort detail data two
different way, myFName, myLName are the examples of
sorting fields.
Now I did "=IIf(myId=10, myLName, myFName)" but it said
conflict between order by and group by. Is there any other
way I can change the detail section sorting dynamically?
-----Original Message-----
JIM.H. said:
On my report myId is a group, and myFName and myLName are
detail field names. By Default detail is sorted by
myFName, now if myId=10 I want to sort only this group by
myLName.
Click to expand...


That sounds like a strange thing to do, maybe you're too far
down this path and should back up to explain what the real
issue is.

Anyway, the Sorting and Grouping window allows you to
specify and expression in place of a field name. For
example, it's fairly common to sort on an expression like:
=[myLName] & "~" & [myFName]

As for your question, I think you want to use:
=IIf(myId=10, myLName, myFName)
Click to expand...
 
JIM.H. said:
Field names are examples, they are not really names. I am
trying to get data from a table and print a sheet for
different people and they they all want a different way of
sorting. I did not want to use different reports. My
group, ID in the example, is first sorting and I do not
want to change it, then I need to sort detail data two
different way, myFName, myLName are the examples of
sorting fields.
Now I did "=IIf(myId=10, myLName, myFName)" but it said
conflict between order by and group by. Is there any other
way I can change the detail section sorting dynamically?
Click to expand...

You can use the report's open event to set the sorting for
the entire report. If that's all you want, say so.

If you want to sort a group differently depending on the
group, then I have to apologize for my previous off the top
of my head answer.

I was able to get the report to sort the way you want by
using a calculated field in the report's record source
query. I added:
Sort2: IIf(myId=10, myLName, myFName)
and then specified Sort2 as the second level of sorting in
the report.
--
Marsh
MVP [MS Access]


-----Original Message-----
JIM.H. said:
On my report myId is a group, and myFName and myLName are
detail field names. By Default detail is sorted by
myFName, now if myId=10 I want to sort only this group by
myLName.
Click to expand...


That sounds like a strange thing to do, maybe you're too far
down this path and should back up to explain what the real
issue is.

Anyway, the Sorting and Grouping window allows you to
specify and expression in place of a field name. For
example, it's fairly common to sort on an expression like:
=[myLName] & "~" & [myFName]

As for your question, I think you want to use:
=IIf(myId=10, myLName, myFName)
Click to expand...
Click to expand...
 
Thanks Marsh,
That is working perfect. Now I need to list some
important records on different pages. We know myLName is
already sorted under ID=10. So if myLName="Test1" or
myLName="Test2" I need to put a page break before and
after these records. This way, both Test1 and Test2 are
listed in different pages, other records are on the same
pages. How can I do this? (Please note I already have a
page break on group)
Thanks,
Jim.
-----Original Message-----
JIM.H. said:
Field names are examples, they are not really names. I am
trying to get data from a table and print a sheet for
different people and they they all want a different way of
sorting. I did not want to use different reports. My
group, ID in the example, is first sorting and I do not
want to change it, then I need to sort detail data two
different way, myFName, myLName are the examples of
sorting fields.
Now I did "=IIf(myId=10, myLName, myFName)" but it said
conflict between order by and group by. Is there any other
way I can change the detail section sorting dynamically?
Click to expand...

You can use the report's open event to set the sorting for
the entire report. If that's all you want, say so.

If you want to sort a group differently depending on the
group, then I have to apologize for my previous off the top
of my head answer.

I was able to get the report to sort the way you want by
using a calculated field in the report's record source
query. I added:
Sort2: IIf(myId=10, myLName, myFName)
and then specified Sort2 as the second level of sorting in
the report.
--
Marsh
MVP [MS Access]


-----Original Message-----
JIM.H. wrote:
On my report myId is a group, and myFName and myLName are
detail field names. By Default detail is sorted by
myFName, now if myId=10 I want to sort only this
Click to expand...
group
by
myLName.


That sounds like a strange thing to do, maybe you're
Click to expand...
too
far
down this path and should back up to explain what the real
issue is.

Anyway, the Sorting and Grouping window allows you to
specify and expression in place of a field name. For
example, it's fairly common to sort on an expression like:
=[myLName] & "~" & [myFName]

As for your question, I think you want to use:
=IIf(myId=10, myLName, myFName)
Click to expand...
Click to expand...

.
Click to expand...
 
JIM.H. said:
That is working perfect. Now I need to list some
important records on different pages. We know myLName is
already sorted under ID=10. So if myLName="Test1" or
myLName="Test2" I need to put a page break before and
after these records. This way, both Test1 and Test2 are
listed in different pages, other records are on the same
pages. How can I do this? (Please note I already have a
page break on group)
Click to expand...


You can add PageBreak controls at the top and bottom of the
detail section and make them visible or not as needed.

Dim bolSpecial As Boolean
bolSpecial = Me.myLName = "Test1" _
OR Me.myLName = "Test2"
Me.pgBreakTop.Visible = bolSpecial And Me.Top > XX * 1440
Me.pgBreakBottom.Visible = bolSpecial

where XX is the number of inches from the top of the page
where the detail would print if it were the only one on the
page. This is to try to prevent a blank page if the detail
were already at the start of a page.

A more general approach that wouldn't use hard coded values
would be to have a Yes/No field in a table where you can
specify if the myLName should be on a separate page. When
that field is added to the report's record source query, the
report could check the field's value without knowing about
any specific myLName values.
--
Marsh
MVP [MS Access]


-----Original Message-----
JIM.H. said:
Field names are examples, they are not really names. I am
trying to get data from a table and print a sheet for
different people and they they all want a different way of
sorting. I did not want to use different reports. My
group, ID in the example, is first sorting and I do not
want to change it, then I need to sort detail data two
different way, myFName, myLName are the examples of
sorting fields.
Now I did "=IIf(myId=10, myLName, myFName)" but it said
conflict between order by and group by. Is there any other
way I can change the detail section sorting dynamically?
Click to expand...
Marshall Barton wrote
You can use the report's open event to set the sorting
for the entire report. If that's all you want, say so.

If you want to sort a group differently depending on the
group, then I have to apologize for my previous off the
top of my head answer.

I was able to get the report to sort the way you want by
using a calculated field in the report's record source
query. I added:
Sort2: IIf(myId=10, myLName, myFName)
and then specified Sort2 as the second level of sorting
in the report.

-----Original Message-----
JIM.H. wrote:
On my report myId is a group, and myFName and myLName
are
detail field names. By Default detail is sorted by
myFName, now if myId=10 I want to sort only this group
by
myLName.


That sounds like a strange thing to do, maybe you're too
far
down this path and should back up to explain what the real
issue is.

Anyway, the Sorting and Grouping window allows you to
specify and expression in place of a field name. For
example, it's fairly common to sort on an expression like:
=[myLName] & "~" & [myFName]

As for your question, I think you want to use:
=IIf(myId=10, myLName, myFName)
Click to expand...
Click to expand...
Click to expand...
 
I would prefer yes/no flag. If I had that how should I
tell report that it should print yes values in a
different page only under group ID=10. Please note I do
not want to print each yes in a different page, I want
all yes values in a page.
-----Original Message-----
JIM.H. said:
That is working perfect. Now I need to list some
important records on different pages. We know myLName is
already sorted under ID=10. So if myLName="Test1" or
myLName="Test2" I need to put a page break before and
after these records. This way, both Test1 and Test2 are
listed in different pages, other records are on the same
pages. How can I do this? (Please note I already have a
page break on group)
Click to expand...


You can add PageBreak controls at the top and bottom of the
detail section and make them visible or not as needed.

Dim bolSpecial As Boolean
bolSpecial = Me.myLName = "Test1" _
OR Me.myLName = "Test2"
Me.pgBreakTop.Visible = bolSpecial And Me.Top > XX * 1440
Me.pgBreakBottom.Visible = bolSpecial

where XX is the number of inches from the top of the page
where the detail would print if it were the only one on the
page. This is to try to prevent a blank page if the detail
were already at the start of a page.

A more general approach that wouldn't use hard coded values
would be to have a Yes/No field in a table where you can
specify if the myLName should be on a separate page. When
that field is added to the report's record source query, the
report could check the field's value without knowing about
any specific myLName values.
--
Marsh
MVP [MS Access]


-----Original Message-----
JIM.H. wrote:

Field names are examples, they are not really names.
Click to expand...
I
am
trying to get data from a table and print a sheet for
different people and they they all want a different
Click to expand...
way
of
sorting. I did not want to use different reports. My
group, ID in the example, is first sorting and I do not
want to change it, then I need to sort detail data two
different way, myFName, myLName are the examples of
sorting fields.
Now I did "=IIf(myId=10, myLName, myFName)" but it said
conflict between order by and group by. Is there any other
way I can change the detail section sorting dynamically?
Marshall Barton wrote
You can use the report's open event to set the sorting
for the entire report. If that's all you want, say so.

If you want to sort a group differently depending on the
group, then I have to apologize for my previous off the
top of my head answer.

I was able to get the report to sort the way you want by
using a calculated field in the report's record source
query. I added:
Sort2: IIf(myId=10, myLName, myFName)
and then specified Sort2 as the second level of sorting
in the report.


-----Original Message-----
JIM.H. wrote:
On my report myId is a group, and myFName and myLName
are
detail field names. By Default detail is sorted by
myFName, now if myId=10 I want to sort only this group
by
myLName.


That sounds like a strange thing to do, maybe you're too
far
down this path and should back up to explain what
Click to expand...
the
real
issue is.

Anyway, the Sorting and Grouping window allows you to
specify and expression in place of a field name. For
example, it's fairly common to sort on an expression like:
=[myLName] & "~" & [myFName]

As for your question, I think you want to use:
=IIf(myId=10, myLName, myFName)
Click to expand...
Click to expand...

.
Click to expand...
 
Sorry, but I don't understand.

What table would the Yes/No field be in? The LName table
would seem like the logical place???

I think you're saying that the same LName can appear in ID
10 and in other IDs as well, but I'm not sure.

Please clarify what you mean by all LNames on the same page
when thought you wanted the special LNames on a separate
page???
--
Marsh
MVP [MS Access]



JIM.H. said:
I would prefer yes/no flag. If I had that how should I
tell report that it should print yes values in a
different page only under group ID=10. Please note I do
not want to print each yes in a different page, I want
all yes values in a page.
-----Original Message-----
JIM.H. said:
That is working perfect. Now I need to list some
important records on different pages. We know myLName is
already sorted under ID=10. So if myLName="Test1" or
myLName="Test2" I need to put a page break before and
after these records. This way, both Test1 and Test2 are
listed in different pages, other records are on the same
pages. How can I do this? (Please note I already have a
page break on group)
Click to expand...


You can add PageBreak controls at the top and bottom of the
detail section and make them visible or not as needed.

Dim bolSpecial As Boolean
bolSpecial = Me.myLName = "Test1" _
OR Me.myLName = "Test2"
Me.pgBreakTop.Visible = bolSpecial And Me.Top > XX * 1440
Me.pgBreakBottom.Visible = bolSpecial

where XX is the number of inches from the top of the page
where the detail would print if it were the only one on the
page. This is to try to prevent a blank page if the detail
were already at the start of a page.

A more general approach that wouldn't use hard coded values
would be to have a Yes/No field in a table where you can
specify if the myLName should be on a separate page. When
that field is added to the report's record source query, the
report could check the field's value without knowing about
any specific myLName values.
--
Marsh
MVP [MS Access]


-----Original Message-----
JIM.H. wrote:

Field names are examples, they are not really names. I
am
trying to get data from a table and print a sheet for
different people and they they all want a different way
of
sorting. I did not want to use different reports. My
group, ID in the example, is first sorting and I do not
want to change it, then I need to sort detail data two
different way, myFName, myLName are the examples of
sorting fields.
Now I did "=IIf(myId=10, myLName, myFName)" but it said
conflict between order by and group by. Is there any
other
way I can change the detail section sorting dynamically?

Marshall Barton wrote
You can use the report's open event to set the sorting
for the entire report. If that's all you want, say so.

If you want to sort a group differently depending on the
group, then I have to apologize for my previous off the
top of my head answer.

I was able to get the report to sort the way you want by
using a calculated field in the report's record source
query. I added:
Sort2: IIf(myId=10, myLName, myFName)
and then specified Sort2 as the second level of sorting
in the report.


-----Original Message-----
JIM.H. wrote:
On my report myId is a group, and myFName and myLName
are
detail field names. By Default detail is sorted by
myFName, now if myId=10 I want to sort only this
group
by
myLName.


That sounds like a strange thing to do, maybe you're
too
far
down this path and should back up to explain what the
real
issue is.

Anyway, the Sorting and Grouping window allows you to
specify and expression in place of a field name. For
example, it's fairly common to sort on an expression
like:
=[myLName] & "~" & [myFName]

As for your question, I think you want to use:
=IIf(myId=10, myLName, myFName)
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top