TKM said:My query fieled that I need in my report (closing) is: Closing:
Format([AssumedClosingDate],"mm/dd/yy")
The expression in my report is (sorting and grouping named MonthClosed):
="Average of " & Format([AssumedClosingDate],"mmmm") & ":"
the one above is in the sorting and grouping listed as monthclosed footer.
As you can see the name:AssumedClosingDate, is used twice and because it is
used in the sorting portion I cant get my main field to sort corectly. I can
get it to sort by year setting the Month Closed to Desending and the Closing
field to Ascending. However I get the month starting with 12/1/07, 11/15/06,
10,22/06 etc. The month starts at the high point then works to the low month
January. Hope I did not confuse you more.
The
Duane Hookom said:Now I am really confused. A query can't have two columns with the same name.
One or both of them should be changed to ExprN or a table.Fieldname.
We still don't know anything about the fields used in your sorting and
grouping, their data types, some sample records, and how they appear sorted
in your report.
Duane Hookom said:I never mess around with formatting data values in a query. Save this for the
control sources on your report.
I still don't know what your exact expressions are for sorting and grouping.
If you are sorting on an expression like Format([SomeDate], "mmmm") then I
doubt you will have success since your records would be sorted by month name.
April would be first, followed by August, December,...
You might want to sort on
= Month([SomeDate])
This will sort Jan before April as it should be.
If you still can't find resolution, come back with answers to previously
asked questions.
--
Duane Hookom
Microsoft Access MVP
TKM said:My query fieled that I need in my report (closing) is: Closing:
Format([AssumedClosingDate],"mm/dd/yy")
The expression in my report is (sorting and grouping named MonthClosed):
="Average of " & Format([AssumedClosingDate],"mmmm") & ":"
the one above is in the sorting and grouping listed as monthclosed footer.
As you can see the name:AssumedClosingDate, is used twice and because it is
used in the sorting portion I cant get my main field to sort corectly. I can
get it to sort by year setting the Month Closed to Desending and the Closing
field to Ascending. However I get the month starting with 12/1/07, 11/15/06,
10,22/06 etc. The month starts at the high point then works to the low month
January. Hope I did not confuse you more.
The
Duane Hookom said:Now I am really confused. A query can't have two columns with the same name.
One or both of them should be changed to ExprN or a table.Fieldname.
We still don't know anything about the fields used in your sorting and
grouping, their data types, some sample records, and how they appear sorted
in your report.
--
Duane Hookom
Microsoft Access MVP
:
Yes thank you. However what the problem is, the developer has used the same
field twice in the query. One of them is in the sorting and grouping along
with the actual field being used in a =format function in control source.
Then he used the actual name in the query. I cant for the life of me get the
main filed to ascend over the one that is in the grouping and sorting? I
tried putting the original field name in grouping and sorting but it does
nothering. Any ideas?
:
You could try provide us with the SQL of the query as well as the significant
field names and data types.
Your subject line mentions "report". Do you realize the sort order of a
query is mostly ignored in a report? You need to set the Sorting and Grouping
within the report design.
--
Duane Hookom
Microsoft Access MVP
:
I have tried everything to get a field in ascending order. The field is part
of a large query with 12 tables and 26 fields sorting and grouping with 4
fields. However I can not get this one field to Ascend in the correct order.
What can I do?
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.