Re: sort in report

Great, I got the first 2 things done. On #3 below - I only want the user to
enter a month and year, so how would I go about that but still be able to
sort on the date? Thanks.
Julie

"fredg" wrote:

On Tue, 15 Jul 2008 04:55:00 -0700, lake2212 wrote:

I am setting up a new database. I will have a table with the following
fields I would like to sort on in a report:

Security Name – text field
Security Type – text (can be equal to ‘S’, ‘P’, or ‘C’)
Option Description – this field contains 3 types of information – a month, a
year, a price (examples: Jan-08-25, Feb-07-3.50) (I'd like not to print
decimals unless they are entered by the user)

Is there a way to sort on all these fields in a report?
First – by security
Second – by type (but I want the records to print in this order 'S', 'C', 'P')
Third, forth and fifth – by option month, year and price

I'm still in the design process so changes can be made. Thanks.

1) Add a new column to your query.
SortThis:IIf([SecurityType] = "S",1,IIf([SecurityType] = "C",2,3)

2) Tho proper place to sort your report is in the report's Sorting and
Grouping dialog (not in the query).
In Report Design View, click on View + Sorting and Grouping

In the Field/Expression column and Sort columns, enter:
[SecurityName] Ascending
[SortThis] Ascending

The above will sort the report by SecurityName then SecurityType in
"S", "C", "P" order.

3) As far as then sorting according to the value in your
OptionDescription field, NO you won't be able to.
OptionDescription is a Text (or Memo) datatype field.
You will not be able to sort Jan before Apr because they are not seen
by Access as dates, so Apr would sort before Jan. Also, in a text
field, 12.50 would sort before 4.50.

Your database is not normalized. You should have 2 more fields. One
field as Date datatype, and another as Number datatype. You enter the
date in one field (a valid date, containing month, day, and year) and
the other field enter the price.
Then it would be a simple matter to just add those two fields to the
report's Sorting and Grouping dialog so that they are included in the
sorting.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

.

Relevant Pages

  • RE: Sorting Dates
    ... If you want to sort on the date field, it should be a date data type. ... sorting or using in calculations. ... I am trying to re-do the report as we speak. ... "Duane Hookom" wrote: ...
    (microsoft.public.access.reports)
  • Re: "Convert in Integer" for MA or any guru, please!
    ... I thought you only wanted to sort the report by this value. ... in the sorting and grouping expression/field. ...
    (microsoft.public.access.reports)
  • Re: Selecting records matching user input
    ... Report order is controlled by the Sorting and Grouping dialog of the report. ... What I meant by SOMETIMES the sort order of the query works is just that. ... I have removed everything from Grouping and Sorting and now ORDER BY in my SELECT query is working as it should. ...
    (microsoft.public.access.modulesdaovba)
  • Re: "Sort" a combination of letters and numbers e.g. 20, 21a,200,
    ... The sorting for the report works beautifully, however, any VBA code ... Sometimes I will want to sort by a) area and then b) Tag, ... I only have one piece of code in my database so far, ...
    (microsoft.public.access.reports)
  • Re: Sorting by complex alphanumeric data
    ... sort as though it were a true Year. ... I didn't know that you could write statements into the sorting and grouping ... most likely could use in the Report Sorting & Grouping what I gave you above ... The first one characters are always letters. ...
    (microsoft.public.access.reports)