sorting in reports.

[Danden]

I made an sql that shows workers birthday and sort it after the date. But when I try it in my report, it doesn't sort it. What's the problem here?

 

[Mihail]

Define the sort order at report level.

 

[Danden]

I get wrong sorting based on year of birth. I want to sort it on the day and month. Any ideas?

 

[Mihail]

Design to more fields in your query:
BMonth: Month(Birthday)
BDay: Day(Birthday)
then create the report based on this query.
This way you will be able to sort about the month and the day.

 

[Danden]

I get sorted list when I run Sql query, but when I open a report based on that query, it doesn't sort it after the day, but still sorted after the year.
It seems like I miss something.

 

[Mihail]

Remove the old sort order and define a new one: First by month and second by day.

 

[Pat Hartman]

Access rebuilds the query used as the report's RecordSource and ignores the order by clause. You MUST use the sorting and grouping options to set the sort order.

 

[Danden]

I have designed tre more fields in my query, BDay, BMonth and BYear. But how can I define BDay and BMonth to have to digit.

 

[Mihail]

Can explain with more words ? I don't understand your problem.
So,
Yo have a query.
This query contain the fields BYear, BMonth, BDay and a lot of other fields with no importance for this discussion.
Remove the old report and create a new one, by using the wizard, and sort FIRST by BYear, SECOND by BMonth and LAST by BDay.

Where is the trouble ?

 

[Pat Hartman]

To sort correctly, the month field will need to be numeric (01-12). If it is text (Jan-Dec), you will get an alphabetic sort and April becomes the first month of the year.

 

[Danden]

I'm sorry for the misunderstanding. I explain it again.
The problem is, when I tryed to sort report list based on birthdays dato, then I get a list sorted on the basis of year. Because of that I made a couple of new fields in my query, like BDay: Day([Birthday]), BMonth: Month([Birthday]), BYear: Month([Year]).
I add these fields to the control source element
=[BDay] & "-" & [BMonth] & "-" & [BYear]
and I get report output format like this
1-7-1982
4-7-1963
I would prefer that both the day and the mounth are two fields, like this
01-07-1982
04-07-1963
It should probably be defined in a query, but how?

 

[Mihail]

Try this:
Put this function in a public module (a regular module, not a form module)

Public Function FormatDateWithTwoDigits(ByVal Bday, ByVal Bmonth, ByVal Byear) As String
    If Len(Bday) = 1 Then Bday = "0" & Bday
    If Len(Bmonth) = 1 Then Bmonth = "0" & Bmonth
    If Len(Byear) = 2 Then Byear = "20" & Byear
    FormatDateWithTwoDigits = Bday & "-" & Bmonth & "-" & Byear
End Function

Replace
=[BDay] & "-" & [BMonth] & "-" & [BYear]
with

=  FormatDateWithTwoDigits(BDay, BMonth, BYear)

 

[Danden]

It's working. Thank you for help.

 

[Pat Hartman]

You can have a field(s) to sort on and display different fields.

Humans in the US prefer their dates formatted as month/day/year.
Humans in other parts of the world prefer their dates formatted as day/month/year.

Computers, in order to sort correctly or determine earlier or later, MUST have their dates as Year/month/day if you are going to format them.

So, if you are OK with older people born in May sorting before younger people born in January, use the birth date to sort. If all you want to do is sort by month and day, then use Format()
Select ...., Format(bDate, "mmdd") AS MySortDate, Format(bDate, "dd mm yyyy") As MyDisplayDate
From YourTable
Order by Format(bDate, "mmdd");