How to sort report rows containing both text and date in a column?

  • Thread starter Thread starter Renee
  • Start date Start date
R

Renee

Hi,

I have created a report based on a query that places "TBA" into a date field
if the field is blank. When I run the report, all the "TBA" rows appear at
the top of report, followed by the rows containing dates. I would like to
have the date rows listed first, followed by the "TBA" rows.

Can someone please help me out with the easiest way to do this?

TIA

Renee
 
Set a higher sorting and grouping level on the expression:
=IsNull([DateField])
and set the ascending or descending to what you want.
 
Thank you Duane. That sounds so simple. I'll try it tomorrow at work.

:-)


Duane Hookom said:
Set a higher sorting and grouping level on the expression:
=IsNull([DateField])
and set the ascending or descending to what you want.

--
Duane Hookom
MS Access MVP


Renee said:
Hi,

I have created a report based on a query that places "TBA" into a date
field if the field is blank. When I run the report, all the "TBA" rows
appear at the top of report, followed by the rows containing dates. I
would like to have the date rows listed first, followed by the "TBA"
rows.

Can someone please help me out with the easiest way to do this?

TIA

Renee
Click to expand...
Click to expand...
 
Renee said:
Thank you Duane. That sounds so simple. I'll try it tomorrow at work.

:-)


Duane Hookom said:
Set a higher sorting and grouping level on the expression:
=IsNull([DateField])
and set the ascending or descending to what you want.

--
Duane Hookom
MS Access MVP


Renee said:
Hi,

I have created a report based on a query that places "TBA" into a date
field if the field is blank. When I run the report, all the "TBA" rows
appear at the top of report, followed by the rows containing dates. I
would like to have the date rows listed first, followed by the "TBA"
rows.

Can someone please help me out with the easiest way to do this?

TIA

Renee
Click to expand...
Click to expand...
Click to expand...

Well, I just thought of a reason why this won't work -- the report is
supposed to be sorted by date in ascending order, with the "TBA"s at the
end. I think I may have to do this with a main report and two subreports
(one by date, one by "TBA"), unless there is an easier way (?).
 
Duane's idea is a good one, but you really need that field to be a real date
field. If you have the text "TBA" in the field, I am guessing it is a Text
field, and you will not be able to sort it correctly.

Assuming that "TBA" means To Be Announced, then TBA literally means
"unknown". In database theory and practice, this is exactly what Null means.

The simplest solution is for you to use a field of type Date/Time, and leave
it blank if you don't know the date yet. Then on the report you can print
TBA for Null very easily. This approach solves all the sorting problems, and
will make life very easy for you.

1. Back up the database:
Make a copy of the mdb file, in case something goes wrong. Make sure Access
is closed when you do this.

2. Convert the "TBA"s into Nulls:
To implement this change, create an Update query into your table (Update on
Query menu, in query design view). In the Criteria row under the date field,
enter:
"TBA"
In the Update row, enter:
Null
Run the query.

3. Change the field to a date/time field:
Open the table in design view.
Change the data type of the field.
Save. Check the dates are right.

4. Create a query as the source for the report:
If you don't already have a query for the report, create one.
Type into the Field row:
NoDate: ([DateField] Is Null)
Save. Close.

5. Open the report in design view:
Set the RecordSource of the report to the query (Data tab of Properties
box.)
Open the Sorting And Grouping dialog (View menu).
Enter 2 rows in the dialog:
NoDate Descending
DateField Ascending

6. Display the nulls as TBA:
Set the ControlSource of the text box to:
=Nz([DateField],"TBA")
Change the Name of the text box to (say) txtDateField
(Access gets confused if the control has the same name as a field, but it is
bound to something else.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Renee said:
Renee said:
Thank you Duane. That sounds so simple. I'll try it tomorrow at work.

:-)


Duane Hookom said:
Set a higher sorting and grouping level on the expression:
=IsNull([DateField])
and set the ascending or descending to what you want.

--
Duane Hookom
MS Access MVP


Hi,

I have created a report based on a query that places "TBA" into a date
field if the field is blank. When I run the report, all the "TBA" rows
appear at the top of report, followed by the rows containing dates. I
would like to have the date rows listed first, followed by the "TBA"
rows.

Can someone please help me out with the easiest way to do this?

TIA

Renee
Click to expand...
Click to expand...

Well, I just thought of a reason why this won't work -- the report is
supposed to be sorted by date in ascending order, with the "TBA"s at the
end. I think I may have to do this with a main report and two subreports
(one by date, one by "TBA"), unless there is an easier way (?).
Click to expand...
 
Thank you Allen. This procedure should be even less steps for me since the
original table has null values for all the TBAs in the date/time DateFields
(I had transformed the nulls into TBAs in the query I used for the report).

I will work on this one later this morning. Thanks again.

Renee


Allen Browne said:
Duane's idea is a good one, but you really need that field to be a real
date field. If you have the text "TBA" in the field, I am guessing it is a
Text field, and you will not be able to sort it correctly.

Assuming that "TBA" means To Be Announced, then TBA literally means
"unknown". In database theory and practice, this is exactly what Null
means.

The simplest solution is for you to use a field of type Date/Time, and
leave it blank if you don't know the date yet. Then on the report you can
print TBA for Null very easily. This approach solves all the sorting
problems, and will make life very easy for you.

1. Back up the database:
Make a copy of the mdb file, in case something goes wrong. Make sure
Access is closed when you do this.

2. Convert the "TBA"s into Nulls:
To implement this change, create an Update query into your table (Update
on Query menu, in query design view). In the Criteria row under the date
field, enter:
"TBA"
In the Update row, enter:
Null
Run the query.

3. Change the field to a date/time field:
Open the table in design view.
Change the data type of the field.
Save. Check the dates are right.

4. Create a query as the source for the report:
If you don't already have a query for the report, create one.
Type into the Field row:
NoDate: ([DateField] Is Null)
Save. Close.

5. Open the report in design view:
Set the RecordSource of the report to the query (Data tab of Properties
box.)
Open the Sorting And Grouping dialog (View menu).
Enter 2 rows in the dialog:
NoDate Descending
DateField Ascending

6. Display the nulls as TBA:
Set the ControlSource of the text box to:
=Nz([DateField],"TBA")
Change the Name of the text box to (say) txtDateField
(Access gets confused if the control has the same name as a field, but it
is bound to something else.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Renee said:
Renee said:
Thank you Duane. That sounds so simple. I'll try it tomorrow at work.

:-)


Set a higher sorting and grouping level on the expression:
=IsNull([DateField])
and set the ascending or descending to what you want.

--
Duane Hookom
MS Access MVP


Hi,

I have created a report based on a query that places "TBA" into a date
field if the field is blank. When I run the report, all the "TBA" rows
appear at the top of report, followed by the rows containing dates. I
would like to have the date rows listed first, followed by the "TBA"
rows.

Can someone please help me out with the easiest way to do this?

TIA

Renee
Click to expand...

Well, I just thought of a reason why this won't work -- the report is
supposed to be sorted by date in ascending order, with the "TBA"s at the
end. I think I may have to do this with a main report and two subreports
(one by date, one by "TBA"), unless there is an easier way (?).
Click to expand...
Click to expand...
 
Add another field to your query:
MySortField:IIF(IsNull([MyDateField]),2,1)
Sort on this field rather than your calculated date field.
 
Back
Top