Report Sorting

[ricseb]

I've been given the task of creating a report with the following fields: 1.
ID, 2. Start Date, 3. Last Name, 4. City, 5. Job Amt, 6. Amt Due, 7. Method
of Payment, 8. Collection Date, 9. Collection Notes. This report is for a
company that installs windows in homes & businesses. The request/problem I'm
having is that I've been asked to do this: "If there is no Collection Date or
Start Date, sort by the Sale Date. If there is no Collection Date, sort by
Start Date, otherwise sort by Collection Date." But they don't want a "Sale
Date" to show on the report. Any suggestions would be greatly appreciated.

ricseb

 

[George Nicholson]

In your query, create a new field:

SortDate: =
iif(IsNull([CollectionDate]),iif(IsNull([StartDate]),[SaleDate],[StartDate]),[CollectionDate])

Then sort your report on that.

HTH,

 

[ricseb]

Thank you. I will try that & let U know of the results.

ricseb

In your query, create a new field:

SortDate: =
iif(IsNull([CollectionDate]),iif(IsNull([StartDate]),[SaleDate],[StartDate]),[CollectionDate])

Then sort your report on that.

HTH,

I've been given the task of creating a report with the following fields:
1.
ID, 2. Start Date, 3. Last Name, 4. City, 5. Job Amt, 6. Amt Due, 7.
Method
of Payment, 8. Collection Date, 9. Collection Notes. This report is for a
company that installs windows in homes & businesses. The request/problem
I'm
having is that I've been asked to do this: "If there is no Collection Date
or
Start Date, sort by the Sale Date. If there is no Collection Date, sort
by
Start Date, otherwise sort by Collection Date." But they don't want a
"Sale
Date" to show on the report. Any suggestions would be greatly
appreciated.

ricseb

 

[Duane Hookom]

I believe the IIf() will return a variant data type which might lead to
sorting issues. You might want to try:
=DateValue( Nz(CollectionDate, Nz(StartDate, Nz(SaleDate,Date() ) ) ) )
--
Duane Hookom
Microsoft Access MVP

Thank you. I will try that & let U know of the results.

ricseb

In your query, create a new field:

SortDate: =
iif(IsNull([CollectionDate]),iif(IsNull([StartDate]),[SaleDate],[StartDate]),[CollectionDate])

Then sort your report on that.

HTH,

I've been given the task of creating a report with the following fields:
1.
ID, 2. Start Date, 3. Last Name, 4. City, 5. Job Amt, 6. Amt Due, 7.
Method
of Payment, 8. Collection Date, 9. Collection Notes. This report is for a
company that installs windows in homes & businesses. The request/problem
I'm
having is that I've been asked to do this: "If there is no Collection Date
or
Start Date, sort by the Sale Date. If there is no Collection Date, sort
by
Start Date, otherwise sort by Collection Date." But they don't want a
"Sale
Date" to show on the report. Any suggestions would be greatly
appreciated.

ricseb

 

[ricseb]

Perhaps I'm entering this wrong. In the query field textbox, I typed
"SortDate", then built your expression in the "Criteria" textbox. When I run
the query, it's a parameter query asking me for a date. That's not what I'm
trying to do. Am I understanding you correctly George?

ricseb

In your query, create a new field:

SortDate: =
iif(IsNull([CollectionDate]),iif(IsNull([StartDate]),[SaleDate],[StartDate]),[CollectionDate])

Then sort your report on that.

HTH,

I've been given the task of creating a report with the following fields:
1.
ID, 2. Start Date, 3. Last Name, 4. City, 5. Job Amt, 6. Amt Due, 7.
Method
of Payment, 8. Collection Date, 9. Collection Notes. This report is for a
company that installs windows in homes & businesses. The request/problem
I'm
having is that I've been asked to do this: "If there is no Collection Date
or
Start Date, sort by the Sale Date. If there is no Collection Date, sort
by
Start Date, otherwise sort by Collection Date." But they don't want a
"Sale
Date" to show on the report. Any suggestions would be greatly
appreciated.

ricseb

 

[ricseb]

Duane, thanks. I will try that. I'm under pressure to get this report done,
and am at a loss as to how to accomplish it.

ricseb

I believe the IIf() will return a variant data type which might lead to
sorting issues. You might want to try:
=DateValue( Nz(CollectionDate, Nz(StartDate, Nz(SaleDate,Date() ) ) ) )
--
Duane Hookom
Microsoft Access MVP

Thank you. I will try that & let U know of the results.

ricseb

In your query, create a new field:

SortDate: =
iif(IsNull([CollectionDate]),iif(IsNull([StartDate]),[SaleDate],[StartDate]),[CollectionDate])

Then sort your report on that.

HTH,

I've been given the task of creating a report with the following fields:
1.
ID, 2. Start Date, 3. Last Name, 4. City, 5. Job Amt, 6. Amt Due, 7.
Method
of Payment, 8. Collection Date, 9. Collection Notes. This report is for a
company that installs windows in homes & businesses. The request/problem
I'm
having is that I've been asked to do this: "If there is no Collection Date
or
Start Date, sort by the Sale Date. If there is no Collection Date, sort
by
Start Date, otherwise sort by Collection Date." But they don't want a
"Sale
Date" to show on the report. Any suggestions would be greatly
appreciated.

ricseb

 

[Duane Hookom]

You can either add the expression in the query (without the "=")
Field: SortDate: DateValue( Nz(CollectionDate, Nz(StartDate,
Nz(SaleDate,Date() ) ) ) )
Or you can add the expression directly into the sorting and grouping dialog
(with the "=")
=DateValue( Nz(CollectionDate, Nz(StartDate, Nz(SaleDate,Date() ) ) ) )

--
Duane Hookom
Microsoft Access MVP

Perhaps I'm entering this wrong. In the query field textbox, I typed
"SortDate", then built your expression in the "Criteria" textbox. When I run
the query, it's a parameter query asking me for a date. That's not what I'm
trying to do. Am I understanding you correctly George?

ricseb

In your query, create a new field:

SortDate: =
iif(IsNull([CollectionDate]),iif(IsNull([StartDate]),[SaleDate],[StartDate]),[CollectionDate])

Then sort your report on that.

HTH,

I've been given the task of creating a report with the following fields:
1.
ID, 2. Start Date, 3. Last Name, 4. City, 5. Job Amt, 6. Amt Due, 7.
Method
of Payment, 8. Collection Date, 9. Collection Notes. This report is for a
company that installs windows in homes & businesses. The request/problem
I'm
having is that I've been asked to do this: "If there is no Collection Date
or
Start Date, sort by the Sale Date. If there is no Collection Date, sort
by
Start Date, otherwise sort by Collection Date." But they don't want a
"Sale
Date" to show on the report. Any suggestions would be greatly
appreciated.

ricseb

 

[ricseb]

Thank you Duane. I will keep your suggestion but I think I will join two
tables, create a new query that sorts the "SALES DATE" field in Ascending
order, then uncheck it so that the "SALES DATE" doesn't show in the report,
but the "START DATE" and "COLL DATE" fields will still sort from the "Sales
Date". That is what is being asked of me for this report.

ricseb

You can either add the expression in the query (without the "=")
Field: SortDate: DateValue( Nz(CollectionDate, Nz(StartDate,
Nz(SaleDate,Date() ) ) ) )
Or you can add the expression directly into the sorting and grouping dialog
(with the "=")
=DateValue( Nz(CollectionDate, Nz(StartDate, Nz(SaleDate,Date() ) ) ) )

--
Duane Hookom
Microsoft Access MVP

Perhaps I'm entering this wrong. In the query field textbox, I typed
"SortDate", then built your expression in the "Criteria" textbox. When I run
the query, it's a parameter query asking me for a date. That's not what I'm
trying to do. Am I understanding you correctly George?

ricseb

In your query, create a new field:

SortDate: =
iif(IsNull([CollectionDate]),iif(IsNull([StartDate]),[SaleDate],[StartDate]),[CollectionDate])

Then sort your report on that.

HTH,

I've been given the task of creating a report with the following fields:
1.
ID, 2. Start Date, 3. Last Name, 4. City, 5. Job Amt, 6. Amt Due, 7.
Method
of Payment, 8. Collection Date, 9. Collection Notes. This report is for a
company that installs windows in homes & businesses. The request/problem
I'm
having is that I've been asked to do this: "If there is no Collection Date
or
Start Date, sort by the Sale Date. If there is no Collection Date, sort
by
Start Date, otherwise sort by Collection Date." But they don't want a
"Sale
Date" to show on the report. Any suggestions would be greatly
appreciated.

ricseb

 

[Duane Hookom]

Do you understand that the sort order in your query has little or nothing to
do with the order of records in your report? You can sort the query any which
way but it is basically a waste of your time.

--
Duane Hookom
Microsoft Access MVP

Thank you Duane. I will keep your suggestion but I think I will join two
tables, create a new query that sorts the "SALES DATE" field in Ascending
order, then uncheck it so that the "SALES DATE" doesn't show in the report,
but the "START DATE" and "COLL DATE" fields will still sort from the "Sales
Date". That is what is being asked of me for this report.

ricseb

You can either add the expression in the query (without the "=")
Field: SortDate: DateValue( Nz(CollectionDate, Nz(StartDate,
Nz(SaleDate,Date() ) ) ) )
Or you can add the expression directly into the sorting and grouping dialog
(with the "=")
=DateValue( Nz(CollectionDate, Nz(StartDate, Nz(SaleDate,Date() ) ) ) )

--
Duane Hookom
Microsoft Access MVP

Perhaps I'm entering this wrong. In the query field textbox, I typed
"SortDate", then built your expression in the "Criteria" textbox. When I run
the query, it's a parameter query asking me for a date. That's not what I'm
trying to do. Am I understanding you correctly George?

ricseb

:

In your query, create a new field:

SortDate: =
iif(IsNull([CollectionDate]),iif(IsNull([StartDate]),[SaleDate],[StartDate]),[CollectionDate])

Then sort your report on that.

HTH,

I've been given the task of creating a report with the following fields:
1.
ID, 2. Start Date, 3. Last Name, 4. City, 5. Job Amt, 6. Amt Due, 7.
Method
of Payment, 8. Collection Date, 9. Collection Notes. This report is for a
company that installs windows in homes & businesses. The request/problem
I'm
having is that I've been asked to do this: "If there is no Collection Date
or
Start Date, sort by the Sale Date. If there is no Collection Date, sort
by
Start Date, otherwise sort by Collection Date." But they don't want a
"Sale
Date" to show on the report. Any suggestions would be greatly
appreciated.

ricseb

 

[ricseb]

Duane, in that case, how do I get the report to sort in the following way?
Sort by "Collection Date". If there is no Collection Date, sort by "Start
Date". If there is no Start Date, sort by "Sales Date". I've created a new
query that has all of the above fields, but am at a loss as to how to
populate this report in the above order. I know I'm missing something when
it comes to building the correct expression.

Thanks for your help.

Ricky Sebastian

Do you understand that the sort order in your query has little or nothing to
do with the order of records in your report? You can sort the query any which
way but it is basically a waste of your time.

--
Duane Hookom
Microsoft Access MVP

Thank you Duane. I will keep your suggestion but I think I will join two
tables, create a new query that sorts the "SALES DATE" field in Ascending
order, then uncheck it so that the "SALES DATE" doesn't show in the report,
but the "START DATE" and "COLL DATE" fields will still sort from the "Sales
Date". That is what is being asked of me for this report.

ricseb

You can either add the expression in the query (without the "=")
Field: SortDate: DateValue( Nz(CollectionDate, Nz(StartDate,
Nz(SaleDate,Date() ) ) ) )
Or you can add the expression directly into the sorting and grouping dialog
(with the "=")
=DateValue( Nz(CollectionDate, Nz(StartDate, Nz(SaleDate,Date() ) ) ) )

--
Duane Hookom
Microsoft Access MVP


:

Perhaps I'm entering this wrong. In the query field textbox, I typed
"SortDate", then built your expression in the "Criteria" textbox. When I run
the query, it's a parameter query asking me for a date. That's not what I'm
trying to do. Am I understanding you correctly George?

ricseb

:

In your query, create a new field:

SortDate: =
iif(IsNull([CollectionDate]),iif(IsNull([StartDate]),[SaleDate],[StartDate]),[CollectionDate])

Then sort your report on that.

HTH,

I've been given the task of creating a report with the following fields:
1.
ID, 2. Start Date, 3. Last Name, 4. City, 5. Job Amt, 6. Amt Due, 7.
Method
of Payment, 8. Collection Date, 9. Collection Notes. This report is for a
company that installs windows in homes & businesses. The request/problem
I'm
having is that I've been asked to do this: "If there is no Collection Date
or
Start Date, sort by the Sale Date. If there is no Collection Date, sort
by
Start Date, otherwise sort by Collection Date." But they don't want a
"Sale
Date" to show on the report. Any suggestions would be greatly
appreciated.

ricseb

 

[Duane Hookom]

Do exactly as I stated a couple posts previous:
=================================
You can either add the expression in the query (without the "=")
Field: SortDate: DateValue( Nz(CollectionDate, Nz(StartDate,
Nz(SaleDate,Date() ) ) ) )
Or you can add the expression directly into the sorting and grouping dialog
(with the "=")
=DateValue( Nz(CollectionDate, Nz(StartDate, Nz(SaleDate,Date() ) ) ) )
=================================
If you don't understand this, come back for clarification.
--

--
Duane Hookom
Microsoft Access MVP

Duane, in that case, how do I get the report to sort in the following way?
Sort by "Collection Date". If there is no Collection Date, sort by "Start
Date". If there is no Start Date, sort by "Sales Date". I've created a new
query that has all of the above fields, but am at a loss as to how to
populate this report in the above order. I know I'm missing something when
it comes to building the correct expression.

Thanks for your help.

Ricky Sebastian

Do you understand that the sort order in your query has little or nothing to
do with the order of records in your report? You can sort the query any which
way but it is basically a waste of your time.

--
Duane Hookom
Microsoft Access MVP

Thank you Duane. I will keep your suggestion but I think I will join two
tables, create a new query that sorts the "SALES DATE" field in Ascending
order, then uncheck it so that the "SALES DATE" doesn't show in the report,
but the "START DATE" and "COLL DATE" fields will still sort from the "Sales
Date". That is what is being asked of me for this report.

ricseb

:

You can either add the expression in the query (without the "=")
Field: SortDate: DateValue( Nz(CollectionDate, Nz(StartDate,
Nz(SaleDate,Date() ) ) ) )
Or you can add the expression directly into the sorting and grouping dialog
(with the "=")
=DateValue( Nz(CollectionDate, Nz(StartDate, Nz(SaleDate,Date() ) ) ) )

--
Duane Hookom
Microsoft Access MVP


:

Perhaps I'm entering this wrong. In the query field textbox, I typed
"SortDate", then built your expression in the "Criteria" textbox. When I run
the query, it's a parameter query asking me for a date. That's not what I'm
trying to do. Am I understanding you correctly George?

ricseb

:

In your query, create a new field:

SortDate: =
iif(IsNull([CollectionDate]),iif(IsNull([StartDate]),[SaleDate],[StartDate]),[CollectionDate])

Then sort your report on that.

HTH,

I've been given the task of creating a report with the following fields:
1.
ID, 2. Start Date, 3. Last Name, 4. City, 5. Job Amt, 6. Amt Due, 7.
Method
of Payment, 8. Collection Date, 9. Collection Notes. This report is for a
company that installs windows in homes & businesses. The request/problem
I'm
having is that I've been asked to do this: "If there is no Collection Date
or
Start Date, sort by the Sale Date. If there is no Collection Date, sort
by
Start Date, otherwise sort by Collection Date." But they don't want a
"Sale
Date" to show on the report. Any suggestions would be greatly
appreciated.

ricseb

 

[ricseb]

Duane, I really appreciate your patience with me on this issue. I've
designed DB's in Access from scratch, and have created sorted & other kinds
of reports, but this is the 1st time I've taken on a very large DB that has
been designed by 4 different people in the past 7 years. I've never tried to
revise an existing report before, and I've only been with this company for 1
month and am also the Network Administrator and designing the new Web Site &
the art work for it. I know my issue is with the expressions needed for this
report & I know that I'm close, but I just can't seem to grasp it. I've
tried what U said, but the report doesn't populate the way that I want it to.
I'll give it another stab in the morning, but I'm about to tell my boss that
this one is over my head for now anyway.

I'll keep you posted.

Ricky Sebastian

Do exactly as I stated a couple posts previous:
=================================
You can either add the expression in the query (without the "=")
Field: SortDate: DateValue( Nz(CollectionDate, Nz(StartDate,
Nz(SaleDate,Date() ) ) ) )
Or you can add the expression directly into the sorting and grouping dialog
(with the "=")
=DateValue( Nz(CollectionDate, Nz(StartDate, Nz(SaleDate,Date() ) ) ) )
=================================
If you don't understand this, come back for clarification.
--

--
Duane Hookom
Microsoft Access MVP

Duane, in that case, how do I get the report to sort in the following way?
Sort by "Collection Date". If there is no Collection Date, sort by "Start
Date". If there is no Start Date, sort by "Sales Date". I've created a new
query that has all of the above fields, but am at a loss as to how to
populate this report in the above order. I know I'm missing something when
it comes to building the correct expression.

Thanks for your help.

Ricky Sebastian

Do you understand that the sort order in your query has little or nothing to
do with the order of records in your report? You can sort the query any which
way but it is basically a waste of your time.

--
Duane Hookom
Microsoft Access MVP


:

Thank you Duane. I will keep your suggestion but I think I will join two
tables, create a new query that sorts the "SALES DATE" field in Ascending
order, then uncheck it so that the "SALES DATE" doesn't show in the report,
but the "START DATE" and "COLL DATE" fields will still sort from the "Sales
Date". That is what is being asked of me for this report.

ricseb

:

You can either add the expression in the query (without the "=")
Field: SortDate: DateValue( Nz(CollectionDate, Nz(StartDate,
Nz(SaleDate,Date() ) ) ) )
Or you can add the expression directly into the sorting and grouping dialog
(with the "=")
=DateValue( Nz(CollectionDate, Nz(StartDate, Nz(SaleDate,Date() ) ) ) )

--
Duane Hookom
Microsoft Access MVP


:

Perhaps I'm entering this wrong. In the query field textbox, I typed
"SortDate", then built your expression in the "Criteria" textbox. When I run
the query, it's a parameter query asking me for a date. That's not what I'm
trying to do. Am I understanding you correctly George?

ricseb

:

In your query, create a new field:

SortDate: =
iif(IsNull([CollectionDate]),iif(IsNull([StartDate]),[SaleDate],[StartDate]),[CollectionDate])

Then sort your report on that.

HTH,

I've been given the task of creating a report with the following fields:
1.
ID, 2. Start Date, 3. Last Name, 4. City, 5. Job Amt, 6. Amt Due, 7.
Method
of Payment, 8. Collection Date, 9. Collection Notes. This report is for a
company that installs windows in homes & businesses. The request/problem
I'm
having is that I've been asked to do this: "If there is no Collection Date
or
Start Date, sort by the Sale Date. If there is no Collection Date, sort
by
Start Date, otherwise sort by Collection Date." But they don't want a
"Sale
Date" to show on the report. Any suggestions would be greatly
appreciated.

ricseb

 

[Duane Hookom]

Have you found the sorting and grouping dialog in the report design view?

--
Duane Hookom
Microsoft Access MVP

Duane, I really appreciate your patience with me on this issue. I've
designed DB's in Access from scratch, and have created sorted & other kinds
of reports, but this is the 1st time I've taken on a very large DB that has
been designed by 4 different people in the past 7 years. I've never tried to
revise an existing report before, and I've only been with this company for 1
month and am also the Network Administrator and designing the new Web Site &
the art work for it. I know my issue is with the expressions needed for this
report & I know that I'm close, but I just can't seem to grasp it. I've
tried what U said, but the report doesn't populate the way that I want it to.
I'll give it another stab in the morning, but I'm about to tell my boss that
this one is over my head for now anyway.

I'll keep you posted.

Ricky Sebastian

Do exactly as I stated a couple posts previous:
=================================
You can either add the expression in the query (without the "=")
Field: SortDate: DateValue( Nz(CollectionDate, Nz(StartDate,
Nz(SaleDate,Date() ) ) ) )
Or you can add the expression directly into the sorting and grouping dialog
(with the "=")
=DateValue( Nz(CollectionDate, Nz(StartDate, Nz(SaleDate,Date() ) ) ) )
=================================
If you don't understand this, come back for clarification.
--

--
Duane Hookom
Microsoft Access MVP

Duane, in that case, how do I get the report to sort in the following way?
Sort by "Collection Date". If there is no Collection Date, sort by "Start
Date". If there is no Start Date, sort by "Sales Date". I've created a new
query that has all of the above fields, but am at a loss as to how to
populate this report in the above order. I know I'm missing something when
it comes to building the correct expression.

Thanks for your help.

Ricky Sebastian

:

Do you understand that the sort order in your query has little or nothing to
do with the order of records in your report? You can sort the query any which
way but it is basically a waste of your time.

--
Duane Hookom
Microsoft Access MVP


:

Thank you Duane. I will keep your suggestion but I think I will join two
tables, create a new query that sorts the "SALES DATE" field in Ascending
order, then uncheck it so that the "SALES DATE" doesn't show in the report,
but the "START DATE" and "COLL DATE" fields will still sort from the "Sales
Date". That is what is being asked of me for this report.

ricseb

:

You can either add the expression in the query (without the "=")
Field: SortDate: DateValue( Nz(CollectionDate, Nz(StartDate,
Nz(SaleDate,Date() ) ) ) )
Or you can add the expression directly into the sorting and grouping dialog
(with the "=")
=DateValue( Nz(CollectionDate, Nz(StartDate, Nz(SaleDate,Date() ) ) ) )

--
Duane Hookom
Microsoft Access MVP


:

Perhaps I'm entering this wrong. In the query field textbox, I typed
"SortDate", then built your expression in the "Criteria" textbox. When I run
the query, it's a parameter query asking me for a date. That's not what I'm
trying to do. Am I understanding you correctly George?

ricseb

:

In your query, create a new field:

SortDate: =
iif(IsNull([CollectionDate]),iif(IsNull([StartDate]),[SaleDate],[StartDate]),[CollectionDate])

Then sort your report on that.

HTH,

I've been given the task of creating a report with the following fields:
1.
ID, 2. Start Date, 3. Last Name, 4. City, 5. Job Amt, 6. Amt Due, 7.
Method
of Payment, 8. Collection Date, 9. Collection Notes. This report is for a
company that installs windows in homes & businesses. The request/problem
I'm
having is that I've been asked to do this: "If there is no Collection Date
or
Start Date, sort by the Sale Date. If there is no Collection Date, sort
by
Start Date, otherwise sort by Collection Date." But they don't want a
"Sale
Date" to show on the report. Any suggestions would be greatly
appreciated.

ricseb

 

[ricseb]

Yes Duane, I have. I was already aware of that. The problem is that I
didn't design the original report or database, so I've been poking around on
my own to see what the DB relationships are. This particular report doesn't
have the "SALE DATE" in the report field list. What I don't know how to do
is add the "SALE DATE" to this existing report's field list. If I could do
that, then the rest would be a piece of cake because there is already a
"Collections" report designed, just not sorted the way they're asking me to
sort it. The original "Collections" report is sorted by "COLL DATE" only in
Ascending Order. Is there a text that specializes in writing expressions in
Access?

Thanks for your help.

Ricky Sebastian

Have you found the sorting and grouping dialog in the report design view?

--
Duane Hookom
Microsoft Access MVP

Duane, I really appreciate your patience with me on this issue. I've
designed DB's in Access from scratch, and have created sorted & other kinds
of reports, but this is the 1st time I've taken on a very large DB that has
been designed by 4 different people in the past 7 years. I've never tried to
revise an existing report before, and I've only been with this company for 1
month and am also the Network Administrator and designing the new Web Site &
the art work for it. I know my issue is with the expressions needed for this
report & I know that I'm close, but I just can't seem to grasp it. I've
tried what U said, but the report doesn't populate the way that I want it to.
I'll give it another stab in the morning, but I'm about to tell my boss that
this one is over my head for now anyway.

I'll keep you posted.

Ricky Sebastian

Do exactly as I stated a couple posts previous:
=================================
You can either add the expression in the query (without the "=")
Field: SortDate: DateValue( Nz(CollectionDate, Nz(StartDate,
Nz(SaleDate,Date() ) ) ) )
Or you can add the expression directly into the sorting and grouping dialog
(with the "=")
=DateValue( Nz(CollectionDate, Nz(StartDate, Nz(SaleDate,Date() ) ) ) )
=================================
If you don't understand this, come back for clarification.
--

--
Duane Hookom
Microsoft Access MVP


:

Duane, in that case, how do I get the report to sort in the following way?
Sort by "Collection Date". If there is no Collection Date, sort by "Start
Date". If there is no Start Date, sort by "Sales Date". I've created a new
query that has all of the above fields, but am at a loss as to how to
populate this report in the above order. I know I'm missing something when
it comes to building the correct expression.

Thanks for your help.

Ricky Sebastian

:

Do you understand that the sort order in your query has little or nothing to
do with the order of records in your report? You can sort the query any which
way but it is basically a waste of your time.

--
Duane Hookom
Microsoft Access MVP


:

Thank you Duane. I will keep your suggestion but I think I will join two
tables, create a new query that sorts the "SALES DATE" field in Ascending
order, then uncheck it so that the "SALES DATE" doesn't show in the report,
but the "START DATE" and "COLL DATE" fields will still sort from the "Sales
Date". That is what is being asked of me for this report.

ricseb

:

You can either add the expression in the query (without the "=")
Field: SortDate: DateValue( Nz(CollectionDate, Nz(StartDate,
Nz(SaleDate,Date() ) ) ) )
Or you can add the expression directly into the sorting and grouping dialog
(with the "=")
=DateValue( Nz(CollectionDate, Nz(StartDate, Nz(SaleDate,Date() ) ) ) )

--
Duane Hookom
Microsoft Access MVP


:

Perhaps I'm entering this wrong. In the query field textbox, I typed
"SortDate", then built your expression in the "Criteria" textbox. When I run
the query, it's a parameter query asking me for a date. That's not what I'm
trying to do. Am I understanding you correctly George?

ricseb

:

In your query, create a new field:

SortDate: =
iif(IsNull([CollectionDate]),iif(IsNull([StartDate]),[SaleDate],[StartDate]),[CollectionDate])

Then sort your report on that.

HTH,

I've been given the task of creating a report with the following fields:
1.
ID, 2. Start Date, 3. Last Name, 4. City, 5. Job Amt, 6. Amt Due, 7.
Method
of Payment, 8. Collection Date, 9. Collection Notes. This report is for a
company that installs windows in homes & businesses. The request/problem
I'm
having is that I've been asked to do this: "If there is no Collection Date
or
Start Date, sort by the Sale Date. If there is no Collection Date, sort
by
Start Date, otherwise sort by Collection Date." But they don't want a
"Sale
Date" to show on the report. Any suggestions would be greatly
appreciated.

ricseb

 

[Duane Hookom]

Do you understand queries? Have you tried to open the report's record source
in design view? You might find the [Sale Data] so you can add it to the
querie's fields.

--
Duane Hookom
Microsoft Access MVP

Yes Duane, I have. I was already aware of that. The problem is that I
didn't design the original report or database, so I've been poking around on
my own to see what the DB relationships are. This particular report doesn't
have the "SALE DATE" in the report field list. What I don't know how to do
is add the "SALE DATE" to this existing report's field list. If I could do
that, then the rest would be a piece of cake because there is already a
"Collections" report designed, just not sorted the way they're asking me to
sort it. The original "Collections" report is sorted by "COLL DATE" only in
Ascending Order. Is there a text that specializes in writing expressions in
Access?

Thanks for your help.

Ricky Sebastian

Have you found the sorting and grouping dialog in the report design view?

--
Duane Hookom
Microsoft Access MVP

Duane, I really appreciate your patience with me on this issue. I've
designed DB's in Access from scratch, and have created sorted & other kinds
of reports, but this is the 1st time I've taken on a very large DB that has
been designed by 4 different people in the past 7 years. I've never tried to
revise an existing report before, and I've only been with this company for 1
month and am also the Network Administrator and designing the new Web Site &
the art work for it. I know my issue is with the expressions needed for this
report & I know that I'm close, but I just can't seem to grasp it. I've
tried what U said, but the report doesn't populate the way that I want it to.
I'll give it another stab in the morning, but I'm about to tell my boss that
this one is over my head for now anyway.

I'll keep you posted.

Ricky Sebastian

:

Do exactly as I stated a couple posts previous:
=================================
You can either add the expression in the query (without the "=")
Field: SortDate: DateValue( Nz(CollectionDate, Nz(StartDate,
Nz(SaleDate,Date() ) ) ) )
Or you can add the expression directly into the sorting and grouping dialog
(with the "=")
=DateValue( Nz(CollectionDate, Nz(StartDate, Nz(SaleDate,Date() ) ) ) )
=================================
If you don't understand this, come back for clarification.
--

--
Duane Hookom
Microsoft Access MVP


:

Duane, in that case, how do I get the report to sort in the following way?
Sort by "Collection Date". If there is no Collection Date, sort by "Start
Date". If there is no Start Date, sort by "Sales Date". I've created a new
query that has all of the above fields, but am at a loss as to how to
populate this report in the above order. I know I'm missing something when
it comes to building the correct expression.

Thanks for your help.

Ricky Sebastian

:

Do you understand that the sort order in your query has little or nothing to
do with the order of records in your report? You can sort the query any which
way but it is basically a waste of your time.

--
Duane Hookom
Microsoft Access MVP


:

Thank you Duane. I will keep your suggestion but I think I will join two
tables, create a new query that sorts the "SALES DATE" field in Ascending
order, then uncheck it so that the "SALES DATE" doesn't show in the report,
but the "START DATE" and "COLL DATE" fields will still sort from the "Sales
Date". That is what is being asked of me for this report.

ricseb

:

You can either add the expression in the query (without the "=")
Field: SortDate: DateValue( Nz(CollectionDate, Nz(StartDate,
Nz(SaleDate,Date() ) ) ) )
Or you can add the expression directly into the sorting and grouping dialog
(with the "=")
=DateValue( Nz(CollectionDate, Nz(StartDate, Nz(SaleDate,Date() ) ) ) )

--
Duane Hookom
Microsoft Access MVP


:

Perhaps I'm entering this wrong. In the query field textbox, I typed
"SortDate", then built your expression in the "Criteria" textbox. When I run
the query, it's a parameter query asking me for a date. That's not what I'm
trying to do. Am I understanding you correctly George?

ricseb

:

In your query, create a new field:

SortDate: =
iif(IsNull([CollectionDate]),iif(IsNull([StartDate]),[SaleDate],[StartDate]),[CollectionDate])

Then sort your report on that.

HTH,

I've been given the task of creating a report with the following fields:
1.
ID, 2. Start Date, 3. Last Name, 4. City, 5. Job Amt, 6. Amt Due, 7.
Method
of Payment, 8. Collection Date, 9. Collection Notes. This report is for a
company that installs windows in homes & businesses. The request/problem
I'm
having is that I've been asked to do this: "If there is no Collection Date
or
Start Date, sort by the Sale Date. If there is no Collection Date, sort
by
Start Date, otherwise sort by Collection Date." But they don't want a
"Sale
Date" to show on the report. Any suggestions would be greatly
appreciated.

ricseb

 

[ricseb]

Duane, that was a big help. That's something I wasn't taught about Access.
I've put my DB duties on hold for the moment to design & develop the
companies Web Site as they're anxious to get it up & running. Thanks a lot
for your help. I've printed all of your responses to my problem.

Take care,

Ricky Sebastian

Do you understand queries? Have you tried to open the report's record source
in design view? You might find the [Sale Data] so you can add it to the
querie's fields.

--
Duane Hookom
Microsoft Access MVP

Yes Duane, I have. I was already aware of that. The problem is that I
didn't design the original report or database, so I've been poking around on
my own to see what the DB relationships are. This particular report doesn't
have the "SALE DATE" in the report field list. What I don't know how to do
is add the "SALE DATE" to this existing report's field list. If I could do
that, then the rest would be a piece of cake because there is already a
"Collections" report designed, just not sorted the way they're asking me to
sort it. The original "Collections" report is sorted by "COLL DATE" only in
Ascending Order. Is there a text that specializes in writing expressions in
Access?

Thanks for your help.

Ricky Sebastian

Have you found the sorting and grouping dialog in the report design view?

--
Duane Hookom
Microsoft Access MVP


:

Duane, I really appreciate your patience with me on this issue. I've
designed DB's in Access from scratch, and have created sorted & other kinds
of reports, but this is the 1st time I've taken on a very large DB that has
been designed by 4 different people in the past 7 years. I've never tried to
revise an existing report before, and I've only been with this company for 1
month and am also the Network Administrator and designing the new Web Site &
the art work for it. I know my issue is with the expressions needed for this
report & I know that I'm close, but I just can't seem to grasp it. I've
tried what U said, but the report doesn't populate the way that I want it to.
I'll give it another stab in the morning, but I'm about to tell my boss that
this one is over my head for now anyway.

I'll keep you posted.

Ricky Sebastian

:

Do exactly as I stated a couple posts previous:
=================================
You can either add the expression in the query (without the "=")
Field: SortDate: DateValue( Nz(CollectionDate, Nz(StartDate,
Nz(SaleDate,Date() ) ) ) )
Or you can add the expression directly into the sorting and grouping dialog
(with the "=")
=DateValue( Nz(CollectionDate, Nz(StartDate, Nz(SaleDate,Date() ) ) ) )
=================================
If you don't understand this, come back for clarification.
--

--
Duane Hookom
Microsoft Access MVP


:

Duane, in that case, how do I get the report to sort in the following way?
Sort by "Collection Date". If there is no Collection Date, sort by "Start
Date". If there is no Start Date, sort by "Sales Date". I've created a new
query that has all of the above fields, but am at a loss as to how to
populate this report in the above order. I know I'm missing something when
it comes to building the correct expression.

Thanks for your help.

Ricky Sebastian

:

Do you understand that the sort order in your query has little or nothing to
do with the order of records in your report? You can sort the query any which
way but it is basically a waste of your time.

--
Duane Hookom
Microsoft Access MVP


:

Thank you Duane. I will keep your suggestion but I think I will join two
tables, create a new query that sorts the "SALES DATE" field in Ascending
order, then uncheck it so that the "SALES DATE" doesn't show in the report,
but the "START DATE" and "COLL DATE" fields will still sort from the "Sales
Date". That is what is being asked of me for this report.

ricseb

:

You can either add the expression in the query (without the "=")
Field: SortDate: DateValue( Nz(CollectionDate, Nz(StartDate,
Nz(SaleDate,Date() ) ) ) )
Or you can add the expression directly into the sorting and grouping dialog
(with the "=")
=DateValue( Nz(CollectionDate, Nz(StartDate, Nz(SaleDate,Date() ) ) ) )

--
Duane Hookom
Microsoft Access MVP


:

Perhaps I'm entering this wrong. In the query field textbox, I typed
"SortDate", then built your expression in the "Criteria" textbox. When I run
the query, it's a parameter query asking me for a date. That's not what I'm
trying to do. Am I understanding you correctly George?

ricseb

:

In your query, create a new field:

SortDate: =
iif(IsNull([CollectionDate]),iif(IsNull([StartDate]),[SaleDate],[StartDate]),[CollectionDate])

Then sort your report on that.

HTH,

I've been given the task of creating a report with the following fields:
1.
ID, 2. Start Date, 3. Last Name, 4. City, 5. Job Amt, 6. Amt Due, 7.
Method
of Payment, 8. Collection Date, 9. Collection Notes. This report is for a
company that installs windows in homes & businesses. The request/problem
I'm
having is that I've been asked to do this: "If there is no Collection Date
or
Start Date, sort by the Sale Date. If there is no Collection Date, sort
by
Start Date, otherwise sort by Collection Date." But they don't want a
"Sale
Date" to show on the report. Any suggestions would be greatly
appreciated.

ricseb