sort in report

Thanks, I got it working now. Can you also tell me how to make that date and
currency field appear like this on the report:

Option date: 04/01/2009 and Price: 52.00 to appear as Apr-09-52 (no
decimals)
or
Option date: 05/01/2010 Price: 25.50 to appear as May-10-25.50 (yes to
decimals that are input by user)
I appreciate all your help!
Julie

"fredg" wrote:

On Tue, 15 Jul 2008 14:06:00 -0700, lake2212 wrote:

I added the date field and it is working, but I cannot get it to sort by the
date on my report. I defined the date field "Option Date" as data type
"Date/Time". Then I added "Option Date" to my Sorting and Grouping in the
report. What could I be doing wrong? Thanks.
Julie

"fredg" wrote:

On Tue, 15 Jul 2008 09:54:05 -0700, lake2212 wrote:

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


Create a new field Date datatype.
Do NOT name the field "Date", as "Date" is an Access reserved word.
You can use "OrderDate", "SalesDate", "dteDate", etc.

A valid date (month, day, year) must be stored.
If the user enters just a month/year, Access will assume the first day
of the month, so enter 7/08 and Access will store it as 7/1/2008
(note: It's actually stored as a number... 7/1/2008 is stored as 39630
.... so the format is irrelevant).
However... 7/08 is ambiguous.
Does the user mean July 2008 or July 8th. If Access assumes July 8th
it will default to 7/8 2008, the current year.
Therefore you should always use a 4 digit year and avoid confusing
Access. Enter 7/2008 and Access will know you mean July 2008.

As you can readily see from the above, as Access stores 39630 for
7/1/2008, sorting becomes very easy, as it's just a numerical sort and
the actual month name is not important.

Good Luck.

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


Did you include the [Option Date] field in the record source of the
report (the query)?


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

Yup, I see your right. I noticed Fredg said a similar suggestion.

I'm humbled.

Bonnie
http://www.dataplus-svc.com

John W. Vinson/MVP wrote:
Sorry Julie - I missed that.

A quick and easy would be to add a field - put an "A" in for every type =
"S",
"B" for "C" and "C" for "P" and group or sort on it.

Well... I wouldn't really recommend that. This new field would be redundant,
and could very easily get out of synch.

Instead base the report on a query with a calculated field:

SortGroup: Switch([Type] = "S", 1, [Type] = "C", 2, [Type] = "P", 3, True,
100)

to create a calculated field which can then be used for sorting. Use 0
instead of 100 if you want unspecified Type values to sort first.

--
Message posted via http://www.accessmonster.com

On Tue, 15 Jul 2008 17:43:02 -0700, lake2212 wrote:

Thanks, I got it working now. Can you also tell me how to make that date and
currency field appear like this on the report:

Option date: 04/01/2009 and Price: 52.00 to appear as Apr-09-52 (no
decimals)
or
Option date: 05/01/2010 Price: 25.50 to appear as May-10-25.50 (yes to
decimals that are input by user)
I appreciate all your help!
Julie

"fredg" wrote:

On Tue, 15 Jul 2008 14:06:00 -0700, lake2212 wrote:

I added the date field and it is working, but I cannot get it to sort by the
date on my report. I defined the date field "Option Date" as data type
"Date/Time". Then I added "Option Date" to my Sorting and Grouping in the
report. What could I be doing wrong? Thanks.
Julie

"fredg" wrote:

On Tue, 15 Jul 2008 09:54:05 -0700, lake2212 wrote:

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 ¡V text field
Security Type ¡V text (can be equal to ¡¥S¡¦, ¡¥P¡¦, or ¡¥C¡¦)
Option Description ¡V this field contains 3 types of information ¡V 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 ¡V by security
Second ¡V by type (but I want the records to print in this order 'S', 'C', 'P')
Third, forth and fifth ¡V 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


Create a new field Date datatype.
Do NOT name the field "Date", as "Date" is an Access reserved word.
You can use "OrderDate", "SalesDate", "dteDate", etc.

A valid date (month, day, year) must be stored.
If the user enters just a month/year, Access will assume the first day
of the month, so enter 7/08 and Access will store it as 7/1/2008
(note: It's actually stored as a number... 7/1/2008 is stored as 39630
.... so the format is irrelevant).
However... 7/08 is ambiguous.
Does the user mean July 2008 or July 8th. If Access assumes July 8th
it will default to 7/8 2008, the current year.
Therefore you should always use a 4 digit year and avoid confusing
Access. Enter 7/2008 and Access will know you mean July 2008.

As you can readily see from the above, as Access stores 39630 for
7/1/2008, sorting becomes very easy, as it's just a numerical sort and
the actual month name is not important.

Good Luck.

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


Did you include the [Option Date] field in the record source of the
report (the query)?

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


Use an Unbound control on your report.
Set it's control source to:
=Format([Option Date],"mm-yy") & "-" & [YourNumberField]

Which will format the date as 7-08- and the value in the Number field
will be whatever the value is, unformatted.
or...
=Format([Option Date],"mm-yy") & "-" &
Format([YourNumberField],"#,###.00")

Which will format the number with 2 decimals, i.e. 52.00 or 42.25,
etc.. It will also use the comma to separate thousands... 1,234.50.

You can experiment with the number formatting."##" or "##.0" or
"Currency" or "Standard" or "$ #.###", etc.

I suggest you look up the Format Property + Number and Currency
datatype in Access and VBA help.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Great! This format
=Format([Option Date],"mm-yy") & "-" & [YourNumberField]
works perfectly.
1) How do I do this only if another value equals "C" or "P" or does not
equal "S"?
2) Can I convert the number month to text (Jan,Feb,etc.)
Thanks.

"fredg" wrote:

On Tue, 15 Jul 2008 17:43:02 -0700, lake2212 wrote:

Thanks, I got it working now. Can you also tell me how to make that date and
currency field appear like this on the report:

Option date: 04/01/2009 and Price: 52.00 to appear as Apr-09-52 (no
decimals)
or
Option date: 05/01/2010 Price: 25.50 to appear as May-10-25.50 (yes to
decimals that are input by user)
I appreciate all your help!
Julie

"fredg" wrote:

On Tue, 15 Jul 2008 14:06:00 -0700, lake2212 wrote:

I added the date field and it is working, but I cannot get it to sort by the
date on my report. I defined the date field "Option Date" as data type
"Date/Time". Then I added "Option Date" to my Sorting and Grouping in the
report. What could I be doing wrong? Thanks.
Julie

"fredg" wrote:

On Tue, 15 Jul 2008 09:54:05 -0700, lake2212 wrote:

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


Create a new field Date datatype.
Do NOT name the field "Date", as "Date" is an Access reserved word.
You can use "OrderDate", "SalesDate", "dteDate", etc.

A valid date (month, day, year) must be stored.
If the user enters just a month/year, Access will assume the first day
of the month, so enter 7/08 and Access will store it as 7/1/2008
(note: It's actually stored as a number... 7/1/2008 is stored as 39630
.... so the format is irrelevant).
However... 7/08 is ambiguous.
Does the user mean July 2008 or July 8th. If Access assumes July 8th
it will default to 7/8 2008, the current year.
Therefore you should always use a 4 digit year and avoid confusing
Access. Enter 7/2008 and Access will know you mean July 2008.

As you can readily see from the above, as Access stores 39630 for
7/1/2008, sorting becomes very easy, as it's just a numerical sort and
the actual month name is not important.

Good Luck.

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


Did you include the [Option Date] field in the record source of the
report (the query)?

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


Use an Unbound control on your report.
Set it's control source to:
=Format([Option Date],"mm-yy") & "-" & [YourNumberField]

Which will format the date as 7-08- and the value in the Number field
will be whatever the value is, unformatted.
or...
=Format([Option Date],"mm-yy") & "-" &
Format([YourNumberField],"#,###.00")

Which will format the number with 2 decimals, i.e. 52.00 or 42.25,
etc.. It will also use the comma to separate thousands... 1,234.50.

You can experiment with the number formatting."##" or "##.0" or
"Currency" or "Standard" or "$ #.###", etc.

I suggest you look up the Format Property + Number and Currency
datatype in Access and VBA help.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

On Wed, 16 Jul 2008 05:04:18 -0700, lake2212 wrote:

Great! This format
=Format([Option Date],"mm-yy") & "-" & [YourNumberField]
works perfectly.
1) How do I do this only if another value equals "C" or "P" or does not
equal "S"?
2) Can I convert the number month to text (Jan,Feb,etc.)
Thanks.

"fredg" wrote:

On Tue, 15 Jul 2008 17:43:02 -0700, lake2212 wrote:

Thanks, I got it working now. Can you also tell me how to make that date and
currency field appear like this on the report:

Option date: 04/01/2009 and Price: 52.00 to appear as Apr-09-52 (no
decimals)
or
Option date: 05/01/2010 Price: 25.50 to appear as May-10-25.50 (yes to
decimals that are input by user)
I appreciate all your help!
Julie

"fredg" wrote:

On Tue, 15 Jul 2008 14:06:00 -0700, lake2212 wrote:

I added the date field and it is working, but I cannot get it to sort by the
date on my report. I defined the date field "Option Date" as data type
"Date/Time". Then I added "Option Date" to my Sorting and Grouping in the
report. What could I be doing wrong? Thanks.
Julie

"fredg" wrote:

On Tue, 15 Jul 2008 09:54:05 -0700, lake2212 wrote:

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 ¡V text field
Security Type ¡V text (can be equal to ¡¥S¡¦, ¡¥P¡¦, or ¡¥C¡¦)
Option Description ¡V this field contains 3 types of information ¡V 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 ¡V by security
Second ¡V by type (but I want the records to print in this order 'S', 'C', 'P')
Third, forth and fifth ¡V 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


Create a new field Date datatype.
Do NOT name the field "Date", as "Date" is an Access reserved word.
You can use "OrderDate", "SalesDate", "dteDate", etc.

A valid date (month, day, year) must be stored.
If the user enters just a month/year, Access will assume the first day
of the month, so enter 7/08 and Access will store it as 7/1/2008
(note: It's actually stored as a number... 7/1/2008 is stored as 39630
.... so the format is irrelevant).
However... 7/08 is ambiguous.
Does the user mean July 2008 or July 8th. If Access assumes July 8th
it will default to 7/8 2008, the current year.
Therefore you should always use a 4 digit year and avoid confusing
Access. Enter 7/2008 and Access will know you mean July 2008.

As you can readily see from the above, as Access stores 39630 for
7/1/2008, sorting becomes very easy, as it's just a numerical sort and
the actual month name is not important.

Good Luck.

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


Did you include the [Option Date] field in the record source of the
report (the query)?

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


Use an Unbound control on your report.
Set it's control source to:
=Format([Option Date],"mm-yy") & "-" & [YourNumberField]

Which will format the date as 7-08- and the value in the Number field
will be whatever the value is, unformatted.
or...
=Format([Option Date],"mm-yy") & "-" &
Format([YourNumberField],"#,###.00")

Which will format the number with 2 decimals, i.e. 52.00 or 42.25,
etc.. It will also use the comma to separate thousands... 1,234.50.

You can experiment with the number formatting."##" or "##.0" or
"Currency" or "Standard" or "$ #.###", etc.

I suggest you look up the Format Property + Number and Currency
datatype in Access and VBA help.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail



regarding How do I do this only if another value equals "C" or "P"
or does not equal "S"

There is something faulty in your requested criteria logic above.
For example, if the value is "G" (or any other letter except "S"),
the criteria is met, so you don't need equals "C" or "P".

Anyway, here is your requested expression using ="C", = "P", or
"S".
Also, note the change in the Format to show the month as a 3 letter
text value. Jan, Feb, etc.

If you change the logic, just change the expression below.

=IIf([SomeControl] ="C" or [SomeControl] = "P" Or [SomeControl]
"S",Format([Option Date],"mmm-yy") & "-" & [YourNumberField]

Replace [SomeControl] with the name of the criteria control.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Got it. It looks great! Can you answer a couple more questions?

On this report, I list a Stock name, Symbol, Type, Option Description, and
Number of Shares. Would it be possible to subtotal only the "Stock" Types,
and only show the subtotal for that Type, then go on to list the others
without subtotals?

Example - Here would be sample data:

Stock name Symbol Type Option Desc Number of Shares
American Exp AXP Stock 500
American Exp AXP Stock 200
American Exp AXP Stock 25
American Exp AXP Call Jan-10-20 -15
American Exp AXP Put Apr-09-25.5 20
American Exp AXP Call Jan-11-50 -35

Can I get the report to look like this:
Stock Name Symbol Type Option Desc Number of Shares
American Exp AXP Stock 725 (just the
total here)
Call Jan-10-20 -15
Put Apr-09-25.5 20
Call Jan-11-50 -35

Thanks for your help.
Julie


"fredg" wrote:

On Wed, 16 Jul 2008 05:04:18 -0700, lake2212 wrote:

Great! This format
=Format([Option Date],"mm-yy") & "-" & [YourNumberField]
works perfectly.
1) How do I do this only if another value equals "C" or "P" or does not
equal "S"?
2) Can I convert the number month to text (Jan,Feb,etc.)
Thanks.

"fredg" wrote:

On Tue, 15 Jul 2008 17:43:02 -0700, lake2212 wrote:

Thanks, I got it working now. Can you also tell me how to make that date and
currency field appear like this on the report:

Option date: 04/01/2009 and Price: 52.00 to appear as Apr-09-52 (no
decimals)
or
Option date: 05/01/2010 Price: 25.50 to appear as May-10-25.50 (yes to
decimals that are input by user)
I appreciate all your help!
Julie

"fredg" wrote:

On Tue, 15 Jul 2008 14:06:00 -0700, lake2212 wrote:

I added the date field and it is working, but I cannot get it to sort by the
date on my report. I defined the date field "Option Date" as data type
"Date/Time". Then I added "Option Date" to my Sorting and Grouping in the
report. What could I be doing wrong? Thanks.
Julie

"fredg" wrote:

On Tue, 15 Jul 2008 09:54:05 -0700, lake2212 wrote:

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


Create a new field Date datatype.
Do NOT name the field "Date", as "Date" is an Access reserved word.
You can use "OrderDate", "SalesDate", "dteDate", etc.

A valid date (month, day, year) must be stored.
If the user enters just a month/year, Access will assume the first day
of the month, so enter 7/08 and Access will store it as 7/1/2008
(note: It's actually stored as a number... 7/1/2008 is stored as 39630
.... so the format is irrelevant).
However... 7/08 is ambiguous.
Does the user mean July 2008 or July 8th. If Access assumes July 8th
it will default to 7/8 2008, the current year.
Therefore you should always use a 4 digit year and avoid confusing
Access. Enter 7/2008 and Access will know you mean July 2008.

As you can readily see from the above, as Access stores 39630 for
7/1/2008, sorting becomes very easy, as it's just a numerical sort and
the actual month name is not important.

Good Luck.

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


Did you include the [Option Date] field in the record source of the
report (the query)?

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


Use an Unbound control on your report.
Set it's control source to:
=Format([Option Date],"mm-yy") & "-" & [YourNumberField]

Which will format the date as 7-08- and the value in the Number field
will be whatever the value is, unformatted.
or...
=Format([Option Date],"mm-yy") & "-" &
Format([YourNumberField],"#,###.00")

Which will format the number with 2 decimals, i.e. 52.00 or 42.25,
etc.. It will also use the comma to separate thousands... 1,234.50.

You can experiment with the number formatting."##" or "##.0" or
"Currency" or "Standard" or "$ #.###", etc.

I suggest you look up the Format Property + Number and Currency
datatype in Access and VBA help.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail



regarding How do I do this only if another value equals "C" or "P"
or does not equal "S"

There is something faulty in your requested criteria logic above.
For example, if the value is "G" (or any other letter except "S"),
the criteria is met, so you don't need equals "C" or "P".

Anyway, here is your requested expression using ="C", = "P", or
"S".
Also, note the change in the Format to show the month as a 3 letter
text value. Jan, Feb, etc.

If you change the logic, just change the expression below.

=IIf([SomeControl] ="C" or [SomeControl] = "P" Or [SomeControl]
"S",Format([Option Date],"mmm-yy") & "-" & [YourNumberField]

Replace [SomeControl] with the name of the criteria control.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

On Wed, 16 Jul 2008 12:46:09 -0700, lake2212 wrote:

Got it. It looks great! Can you answer a couple more questions?

On this report, I list a Stock name, Symbol, Type, Option Description, and
Number of Shares. Would it be possible to subtotal only the "Stock" Types,
and only show the subtotal for that Type, then go on to list the others
without subtotals?

Example - Here would be sample data:

Stock name Symbol Type Option Desc Number of Shares
American Exp AXP Stock 500
American Exp AXP Stock 200
American Exp AXP Stock 25
American Exp AXP Call Jan-10-20 -15
American Exp AXP Put Apr-09-25.5 20
American Exp AXP Call Jan-11-50 -35

Can I get the report to look like this:
Stock Name Symbol Type Option Desc Number of Shares
American Exp AXP Stock 725 (just the
total here)
Call Jan-10-20 -15
Put Apr-09-25.5 20
Call Jan-11-50 -35

Thanks for your help.
Julie

*** snipped ***

Julie,
This is a completely different topic.

One of the purposes of these newsgroups is that all who come here can
learn by reading other peoples questions and responses.
By keeping this in the same thread you and I would be shutting out
someone with a similar question who wouldn't see this because the
subject of the post (sorting in a report) is not in keeping with this
question. I hope you will understand.

Please start a new thread.

Note: I see above that you indicate that you have a field named
"Type".
"Type" is a field property and should not be used as a field name.
You may get unintended results.

For more Access reserved words see:
http://www.allenbrowne.com/Ap****ueBadWord.html
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Will do. Thanks for the suggestion.
Julie

"fredg" wrote:

On Wed, 16 Jul 2008 12:46:09 -0700, lake2212 wrote:

Got it. It looks great! Can you answer a couple more questions?

On this report, I list a Stock name, Symbol, Type, Option Description, and
Number of Shares. Would it be possible to subtotal only the "Stock" Types,
and only show the subtotal for that Type, then go on to list the others
without subtotals?

Example - Here would be sample data:

Stock name Symbol Type Option Desc Number of Shares
American Exp AXP Stock 500
American Exp AXP Stock 200
American Exp AXP Stock 25
American Exp AXP Call Jan-10-20 -15
American Exp AXP Put Apr-09-25.5 20
American Exp AXP Call Jan-11-50 -35

Can I get the report to look like this:
Stock Name Symbol Type Option Desc Number of Shares
American Exp AXP Stock 725 (just the
total here)
Call Jan-10-20 -15
Put Apr-09-25.5 20
Call Jan-11-50 -35

Thanks for your help.
Julie

*** snipped ***

Julie,
This is a completely different topic.

One of the purposes of these newsgroups is that all who come here can
learn by reading other peoples questions and responses.
By keeping this in the same thread you and I would be shutting out
someone with a similar question who wouldn't see this because the
subject of the post (sorting in a report) is not in keeping with this
question. I hope you will understand.

Please start a new thread.

Note: I see above that you indicate that you have a field named
"Type".
"Type" is a field property and should not be used as a field name.
You may get unintended results.

For more Access reserved words see:
http://www.allenbrowne.com/Ap****ueBadWord.html
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail