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
:
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
:
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
:
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.