Sort on Multiple Fields, Nulls at End

[Guest]

I have a query that pulls records with various status dates. Some have dates
in a Complete field, some have dates in a Referred field, some have dates in
a Closed field and some have not been processed so they have no dates (Nulls)
in any of the fields. I want to sort the query with all the Completes in
ascending order, then Referred in ascending order, then Closed in ascending
order, then the Nulls at the end. Help!!!

 

[Ken Snell [MVP]]

Add a calculated field to the query:

MySorting: IIf([Completed] Is Not Null, 1, IIf([Referred] Is Not Null, 2,
IIf([Closed] Is Not Null, 3, 4)))

Sort ascending on this field along with whichever other fields you're using
for sort order.

 

[Guest]

Thank you so much for the quick answer. Please bear with me, as I am not a
programmer. This calculated field makes perfect sense, but where do I put it?

Add a calculated field to the query:

MySorting: IIf([Completed] Is Not Null, 1, IIf([Referred] Is Not Null, 2,
IIf([Closed] Is Not Null, 3, 4)))

Sort ascending on this field along with whichever other fields you're using
for sort order.

--

Ken Snell
<MS ACCESS MVP>

I have a query that pulls records with various status dates. Some have
dates
in a Complete field, some have dates in a Referred field, some have dates
in
a Closed field and some have not been processed so they have no dates
(Nulls)
in any of the fields. I want to sort the query with all the Completes in
ascending order, then Referred in ascending order, then Closed in
ascending
order, then the Nulls at the end. Help!!!

 

[Ken Snell [MVP]]

It is added as a new field in the query. Open the query in design view, go
to the first empty column on the grid, and paste the expression that I
provided into the "Field:" box. (Be sure to change the names of the fields
if what I used are not the real names).

--

Ken Snell
<MS ACCESS MVP>

Thank you so much for the quick answer. Please bear with me, as I am not
a
programmer. This calculated field makes perfect sense, but where do I put
it?

Add a calculated field to the query:

MySorting: IIf([Completed] Is Not Null, 1, IIf([Referred] Is Not Null, 2,
IIf([Closed] Is Not Null, 3, 4)))

Sort ascending on this field along with whichever other fields you're
using
for sort order.

--

Ken Snell
<MS ACCESS MVP>

I have a query that pulls records with various status dates. Some have
dates
in a Complete field, some have dates in a Referred field, some have
dates
in
a Closed field and some have not been processed so they have no dates
(Nulls)
in any of the fields. I want to sort the query with all the Completes
in
ascending order, then Referred in ascending order, then Closed in
ascending
order, then the Nulls at the end. Help!!!

 

[Guest]

This is great! My query is now sorted in the order of the status date
columns needed (Completed, Referred, Closed, etc.) However, I cannot get the
dates within each status category to sort in ascending order. Plus, the Null
fields are broken down into Active/Assigned, Scheduled/Rescheduled and Null
Status (Not Processed). I need group the Active/Assigned together and the
Scheduled/Rescheduled together. Thanks!

It is added as a new field in the query. Open the query in design view, go
to the first empty column on the grid, and paste the expression that I
provided into the "Field:" box. (Be sure to change the names of the fields
if what I used are not the real names).

--

Ken Snell
<MS ACCESS MVP>

Thank you so much for the quick answer. Please bear with me, as I am not
a
programmer. This calculated field makes perfect sense, but where do I put
it?

Add a calculated field to the query:

MySorting: IIf([Completed] Is Not Null, 1, IIf([Referred] Is Not Null, 2,
IIf([Closed] Is Not Null, 3, 4)))

Sort ascending on this field along with whichever other fields you're
using
for sort order.

--

Ken Snell
<MS ACCESS MVP>

I have a query that pulls records with various status dates. Some have
dates
in a Complete field, some have dates in a Referred field, some have
dates
in
a Closed field and some have not been processed so they have no dates
(Nulls)
in any of the fields. I want to sort the query with all the Completes
in
ascending order, then Referred in ascending order, then Closed in
ascending
order, then the Nulls at the end. Help!!!

 

[Ken Snell [MVP]]

Post the SQL statement of the query as it is right now. Need to see which
fields are there to help you get the ORDER BY clause correct.

Identify the fields that are to be sorted within the "status date" groups.

--

Ken Snell
<MS ACCESS MVP>

This is great! My query is now sorted in the order of the status date
columns needed (Completed, Referred, Closed, etc.) However, I cannot get
the
dates within each status category to sort in ascending order. Plus, the
Null
fields are broken down into Active/Assigned, Scheduled/Rescheduled and
Null
Status (Not Processed). I need group the Active/Assigned together and the
Scheduled/Rescheduled together. Thanks!

It is added as a new field in the query. Open the query in design view,
go
to the first empty column on the grid, and paste the expression that I
provided into the "Field:" box. (Be sure to change the names of the
fields
if what I used are not the real names).

--

Ken Snell
<MS ACCESS MVP>

Thank you so much for the quick answer. Please bear with me, as I am
not
a
programmer. This calculated field makes perfect sense, but where do I
put
it?

:

Add a calculated field to the query:

MySorting: IIf([Completed] Is Not Null, 1, IIf([Referred] Is Not Null,
2,
IIf([Closed] Is Not Null, 3, 4)))

Sort ascending on this field along with whichever other fields you're
using
for sort order.

--

Ken Snell
<MS ACCESS MVP>

I have a query that pulls records with various status dates. Some
have
dates
in a Complete field, some have dates in a Referred field, some have
dates
in
a Closed field and some have not been processed so they have no
dates
(Nulls)
in any of the fields. I want to sort the query with all the
Completes
in
ascending order, then Referred in ascending order, then Closed in
ascending
order, then the Nulls at the end. Help!!!

 

[Guest]

Status: Precall Complete, with Precall Complete Date field
Escalated, with Escalated Date field
Referred, with Referred Date Field
Closed, with Closed Date field
Completed By Client, with Coimpleted By Client Date field
Scheduled/Rescheduled
Active/Assigned
or Status field could be empty is not processed yet (Null)

I need the query to list all records in the status order listed above, and
in ascending order by date.

MySorting: IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))))

Thanks!

Post the SQL statement of the query as it is right now. Need to see which
fields are there to help you get the ORDER BY clause correct.

Identify the fields that are to be sorted within the "status date" groups.

--

Ken Snell
<MS ACCESS MVP>

This is great! My query is now sorted in the order of the status date
columns needed (Completed, Referred, Closed, etc.) However, I cannot get
the
dates within each status category to sort in ascending order. Plus, the
Null
fields are broken down into Active/Assigned, Scheduled/Rescheduled and
Null
Status (Not Processed). I need group the Active/Assigned together and the
Scheduled/Rescheduled together. Thanks!

It is added as a new field in the query. Open the query in design view,
go
to the first empty column on the grid, and paste the expression that I
provided into the "Field:" box. (Be sure to change the names of the
fields
if what I used are not the real names).

--

Ken Snell
<MS ACCESS MVP>

Thank you so much for the quick answer. Please bear with me, as I am
not
a
programmer. This calculated field makes perfect sense, but where do I
put
it?

:

Add a calculated field to the query:

MySorting: IIf([Completed] Is Not Null, 1, IIf([Referred] Is Not Null,
2,
IIf([Closed] Is Not Null, 3, 4)))

Sort ascending on this field along with whichever other fields you're
using
for sort order.

--

Ken Snell
<MS ACCESS MVP>

I have a query that pulls records with various status dates. Some
have
dates
in a Complete field, some have dates in a Referred field, some have
dates
in
a Closed field and some have not been processed so they have no
dates
(Nulls)
in any of the fields. I want to sort the query with all the
Completes
in
ascending order, then Referred in ascending order, then Closed in
ascending
order, then the Nulls at the end. Help!!!

 

[Ken Snell [MVP]]

Please post the SQL statement of the entire query, not just the calculated
field that we had discussed.

Or, use another calculated field, with a similar IIf construction, to decide
which date field to use, and then sort on that calculated field too.

--

Ken Snell
<MS ACCESS MVP>

Status: Precall Complete, with Precall Complete Date field
Escalated, with Escalated Date field
Referred, with Referred Date Field
Closed, with Closed Date field
Completed By Client, with Coimpleted By Client Date field
Scheduled/Rescheduled
Active/Assigned
or Status field could be empty is not processed yet (Null)

I need the query to list all records in the status order listed above, and
in ascending order by date.

MySorting: IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))))

Thanks!

Post the SQL statement of the query as it is right now. Need to see which
fields are there to help you get the ORDER BY clause correct.

Identify the fields that are to be sorted within the "status date"
groups.

--

Ken Snell
<MS ACCESS MVP>

This is great! My query is now sorted in the order of the status date
columns needed (Completed, Referred, Closed, etc.) However, I cannot
get
the
dates within each status category to sort in ascending order. Plus,
the
Null
fields are broken down into Active/Assigned, Scheduled/Rescheduled and
Null
Status (Not Processed). I need group the Active/Assigned together and
the
Scheduled/Rescheduled together. Thanks!

:

It is added as a new field in the query. Open the query in design
view,
go
to the first empty column on the grid, and paste the expression that I
provided into the "Field:" box. (Be sure to change the names of the
fields
if what I used are not the real names).

--

Ken Snell
<MS ACCESS MVP>

Thank you so much for the quick answer. Please bear with me, as I
am
not
a
programmer. This calculated field makes perfect sense, but where do
I
put
it?

:

Add a calculated field to the query:

MySorting: IIf([Completed] Is Not Null, 1, IIf([Referred] Is Not
Null,
2,
IIf([Closed] Is Not Null, 3, 4)))

Sort ascending on this field along with whichever other fields
you're
using
for sort order.

--

Ken Snell
<MS ACCESS MVP>

I have a query that pulls records with various status dates. Some
have
dates
in a Complete field, some have dates in a Referred field, some
have
dates
in
a Closed field and some have not been processed so they have no
dates
(Nulls)
in any of the fields. I want to sort the query with all the
Completes
in
ascending order, then Referred in ascending order, then Closed in
ascending
order, then the Nulls at the end. Help!!!

 

[Guest]

It's Huge! But that's what the customer wants!

SELECT tbl_Merchants.[Old MID], tbl_Merchants.[New MID], tbl_Merchants.[New
MID Update], tbl_Merchants.[Updated MID], tbl_Merchants.PNS,
tbl_Merchants.[Merchant Name], tbl_Merchants.Address1,
tbl_Merchants.[Address1 Update], tbl_Merchants.City, tbl_Merchants.[City
Update], tbl_Merchants.State, tbl_Merchants.[State Update],
tbl_Merchants.Zip, tbl_Merchants.[Zip Update], tbl_Merchants.Phone,
tbl_Merchants.[Phone Update], tbl_Merchants.ContactFirst,
tbl_Merchants.ContactLast, tbl_Merchants.[Contact Update], tbl_Merchants.MCC,
tbl_Merchants.[Updated Record], tbl_Merchants.[Info Updated],
tbl_Equipment.[POS Provided], tbl_Equipment.[Actual Terminal],
tbl_Equipment.[Actual Termina Update], tbl_Equipment.[TID#],
tbl_Equipment.[TID# Update], tbl_Equipment.[Application Name],
tbl_Equipment.[Application Name Update], tbl_Equipment.[Hypercom Mem],
tbl_Equipment.[Printer Type], tbl_Equipment.Imprinter, tbl_Equipment.[Multi
Merchant], tbl_Equipment.[Touchtone Services], tbl_Equipment.[PC Software],
tbl_Equipment.[PC Software Name], tbl_Equipment.[PC Software Version],
tbl_Equipment.[Multi Merchant], tbl_Equipment.[Check Service],
tbl_Equipment.[Ck Reader], tbl_Equipment.[Check Service Provider],
tbl_Equipment.[Check MID Num], tbl_Equipment.Debit, tbl_Equipment.[Debit
Update], tbl_Equipment.[Pin Pad Type], tbl_Equipment.[Pin Pad Serial Number],
tbl_Equipment.AMEX, tbl_Equipment.[AMEX SE Num], tbl_Equipment.[AMEX Update],
tbl_Equipment.DCVR, tbl_Equipment.[Discover MID Num], tbl_Equipment.[DCVR
Update], tbl_Equipment.DNRS, tbl_Equipment.[Diners MID Num],
tbl_Equipment.[Diners Update], tbl_Equipment.JCB, tbl_Equipment.[JCB MID
Num], tbl_Equipment.[JCB Update], tbl_Equipment.Table, tbl_Equipment.[Tip
Line], tbl_Equipment.Tab, tbl_Equipment.[Sales Tax], tbl_Equipment.Invoice,
tbl_Equipment.[Footer Message], tbl_Equipment.[Access Num],
tbl_Equipment.Ticket, tbl_Equipment.Reference, tbl_Equipment.[Check In],
tbl_Equipment.[Check Out], tbl_Equipment.Folio, tbl_Equipment.[Clerk/Server
Prompt], tbl_Equipment.[Auto Close], tbl_Equipment.[Auto Close Time],
tbl_CallData.[Call1 Date], tbl_CallData.[Call2 Date], tbl_CallData.[Call3
Date], tbl_CallData.[Call4 Date], tbl_CallData.[Call5 Date],
tbl_CallData.[Call Status Date], tbl_CallData.[Call Results],
tbl_CallData.Status, tbl_CallData.[Precall Completed Date],
tbl_CallData.[Referred Date], tbl_CallData.[Referred Reason],
tbl_CallData.[Escalated Date], tbl_CallData.[Escalation Reason],
tbl_CallData.[Completed By Client], tbl_CallData.[Closed Date], [tbl_Precall
Appt Log].[Appt1 Date], [tbl_Precall Appt Log].[Appt2 Date], [tbl_Precall
Appt Log].[Appt3 Date], tbl_CallData.[Call1 Comment], tbl_CallData.[Logged
By], IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6))))) AS
MySorting
FROM ((tbl_Merchants LEFT JOIN tbl_Equipment ON tbl_Merchants.[New MID] =
tbl_Equipment.[Merchant Id]) LEFT JOIN tbl_CallData ON tbl_Merchants.[New
MID] = tbl_CallData.[Merchant ID]) LEFT JOIN [tbl_Precall Appt Log] ON
tbl_Merchants.[New MID] = [tbl_Precall Appt Log].[Merchant ID]
ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))));

I am still so lost...Thanks!

Please post the SQL statement of the entire query, not just the calculated
field that we had discussed.

Or, use another calculated field, with a similar IIf construction, to decide
which date field to use, and then sort on that calculated field too.

--

Ken Snell
<MS ACCESS MVP>

Status: Precall Complete, with Precall Complete Date field
Escalated, with Escalated Date field
Referred, with Referred Date Field
Closed, with Closed Date field
Completed By Client, with Coimpleted By Client Date field
Scheduled/Rescheduled
Active/Assigned
or Status field could be empty is not processed yet (Null)

I need the query to list all records in the status order listed above, and
in ascending order by date.

MySorting: IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))))

Thanks!

Post the SQL statement of the query as it is right now. Need to see which
fields are there to help you get the ORDER BY clause correct.

Identify the fields that are to be sorted within the "status date"
groups.

--

Ken Snell
<MS ACCESS MVP>


This is great! My query is now sorted in the order of the status date
columns needed (Completed, Referred, Closed, etc.) However, I cannot
get
the
dates within each status category to sort in ascending order. Plus,
the
Null
fields are broken down into Active/Assigned, Scheduled/Rescheduled and
Null
Status (Not Processed). I need group the Active/Assigned together and
the
Scheduled/Rescheduled together. Thanks!

:

It is added as a new field in the query. Open the query in design
view,
go
to the first empty column on the grid, and paste the expression that I
provided into the "Field:" box. (Be sure to change the names of the
fields
if what I used are not the real names).

--

Ken Snell
<MS ACCESS MVP>

Thank you so much for the quick answer. Please bear with me, as I
am
not
a
programmer. This calculated field makes perfect sense, but where do
I
put
it?

:

Add a calculated field to the query:

MySorting: IIf([Completed] Is Not Null, 1, IIf([Referred] Is Not
Null,
2,
IIf([Closed] Is Not Null, 3, 4)))

Sort ascending on this field along with whichever other fields
you're
using
for sort order.

--

Ken Snell
<MS ACCESS MVP>

I have a query that pulls records with various status dates. Some
have
dates
in a Complete field, some have dates in a Referred field, some
have
dates
in
a Closed field and some have not been processed so they have no
dates
(Nulls)
in any of the fields. I want to sort the query with all the
Completes
in
ascending order, then Referred in ascending order, then Closed in
ascending
order, then the Nulls at the end. Help!!!

 

[Ken Snell [MVP]]

Change this part of the SQL statement:

ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))));


to this:

ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6))))),
[tbl_CallData]![Precall Completed Date],
[tbl_CallData]![Escalated Date],
[tbl_CallData]![Referred Date],
[tbl_CallData]![Closed Date],
[tbl_CallData]![Completed By Client];

--

Ken Snell
<MS ACCESS MVP>

It's Huge! But that's what the customer wants!

SELECT tbl_Merchants.[Old MID], tbl_Merchants.[New MID],
tbl_Merchants.[New
MID Update], tbl_Merchants.[Updated MID], tbl_Merchants.PNS,
tbl_Merchants.[Merchant Name], tbl_Merchants.Address1,
tbl_Merchants.[Address1 Update], tbl_Merchants.City, tbl_Merchants.[City
Update], tbl_Merchants.State, tbl_Merchants.[State Update],
tbl_Merchants.Zip, tbl_Merchants.[Zip Update], tbl_Merchants.Phone,
tbl_Merchants.[Phone Update], tbl_Merchants.ContactFirst,
tbl_Merchants.ContactLast, tbl_Merchants.[Contact Update],
tbl_Merchants.MCC,
tbl_Merchants.[Updated Record], tbl_Merchants.[Info Updated],
tbl_Equipment.[POS Provided], tbl_Equipment.[Actual Terminal],
tbl_Equipment.[Actual Termina Update], tbl_Equipment.[TID#],
tbl_Equipment.[TID# Update], tbl_Equipment.[Application Name],
tbl_Equipment.[Application Name Update], tbl_Equipment.[Hypercom Mem],
tbl_Equipment.[Printer Type], tbl_Equipment.Imprinter,
tbl_Equipment.[Multi
Merchant], tbl_Equipment.[Touchtone Services], tbl_Equipment.[PC
Software],
tbl_Equipment.[PC Software Name], tbl_Equipment.[PC Software Version],
tbl_Equipment.[Multi Merchant], tbl_Equipment.[Check Service],
tbl_Equipment.[Ck Reader], tbl_Equipment.[Check Service Provider],
tbl_Equipment.[Check MID Num], tbl_Equipment.Debit, tbl_Equipment.[Debit
Update], tbl_Equipment.[Pin Pad Type], tbl_Equipment.[Pin Pad Serial
Number],
tbl_Equipment.AMEX, tbl_Equipment.[AMEX SE Num], tbl_Equipment.[AMEX
Update],
tbl_Equipment.DCVR, tbl_Equipment.[Discover MID Num], tbl_Equipment.[DCVR
Update], tbl_Equipment.DNRS, tbl_Equipment.[Diners MID Num],
tbl_Equipment.[Diners Update], tbl_Equipment.JCB, tbl_Equipment.[JCB MID
Num], tbl_Equipment.[JCB Update], tbl_Equipment.Table, tbl_Equipment.[Tip
Line], tbl_Equipment.Tab, tbl_Equipment.[Sales Tax],
tbl_Equipment.Invoice,
tbl_Equipment.[Footer Message], tbl_Equipment.[Access Num],
tbl_Equipment.Ticket, tbl_Equipment.Reference, tbl_Equipment.[Check In],
tbl_Equipment.[Check Out], tbl_Equipment.Folio,
tbl_Equipment.[Clerk/Server
Prompt], tbl_Equipment.[Auto Close], tbl_Equipment.[Auto Close Time],
tbl_CallData.[Call1 Date], tbl_CallData.[Call2 Date], tbl_CallData.[Call3
Date], tbl_CallData.[Call4 Date], tbl_CallData.[Call5 Date],
tbl_CallData.[Call Status Date], tbl_CallData.[Call Results],
tbl_CallData.Status, tbl_CallData.[Precall Completed Date],
tbl_CallData.[Referred Date], tbl_CallData.[Referred Reason],
tbl_CallData.[Escalated Date], tbl_CallData.[Escalation Reason],
tbl_CallData.[Completed By Client], tbl_CallData.[Closed Date],
[tbl_Precall
Appt Log].[Appt1 Date], [tbl_Precall Appt Log].[Appt2 Date], [tbl_Precall
Appt Log].[Appt3 Date], tbl_CallData.[Call1 Comment], tbl_CallData.[Logged
By], IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6))))) AS
MySorting
FROM ((tbl_Merchants LEFT JOIN tbl_Equipment ON tbl_Merchants.[New MID] =
tbl_Equipment.[Merchant Id]) LEFT JOIN tbl_CallData ON tbl_Merchants.[New
MID] = tbl_CallData.[Merchant ID]) LEFT JOIN [tbl_Precall Appt Log] ON
tbl_Merchants.[New MID] = [tbl_Precall Appt Log].[Merchant ID]
ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))));

I am still so lost...Thanks!

Please post the SQL statement of the entire query, not just the
calculated
field that we had discussed.

Or, use another calculated field, with a similar IIf construction, to
decide
which date field to use, and then sort on that calculated field too.

--

Ken Snell
<MS ACCESS MVP>

Status: Precall Complete, with Precall Complete Date field
Escalated, with Escalated Date field
Referred, with Referred Date Field
Closed, with Closed Date field
Completed By Client, with Coimpleted By Client Date field
Scheduled/Rescheduled
Active/Assigned
or Status field could be empty is not processed yet (Null)

I need the query to list all records in the status order listed above,
and
in ascending order by date.

MySorting: IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))))

Thanks!

 

[Guest]

Ken,

It worked!! Thank you so much!!! Now, one last little thing...is there any
way to order the Scheduled/Rescheduled by Call Status Date, then the
Active/Assigned by Call Status Date, and then the records that have no Status
(Null) and no Call Status Date (Null) at the end of this query?

Change this part of the SQL statement:

ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))));


to this:

ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6))))),
[tbl_CallData]![Precall Completed Date],
[tbl_CallData]![Escalated Date],
[tbl_CallData]![Referred Date],
[tbl_CallData]![Closed Date],
[tbl_CallData]![Completed By Client];

--

Ken Snell
<MS ACCESS MVP>

It's Huge! But that's what the customer wants!

SELECT tbl_Merchants.[Old MID], tbl_Merchants.[New MID],
tbl_Merchants.[New
MID Update], tbl_Merchants.[Updated MID], tbl_Merchants.PNS,
tbl_Merchants.[Merchant Name], tbl_Merchants.Address1,
tbl_Merchants.[Address1 Update], tbl_Merchants.City, tbl_Merchants.[City
Update], tbl_Merchants.State, tbl_Merchants.[State Update],
tbl_Merchants.Zip, tbl_Merchants.[Zip Update], tbl_Merchants.Phone,
tbl_Merchants.[Phone Update], tbl_Merchants.ContactFirst,
tbl_Merchants.ContactLast, tbl_Merchants.[Contact Update],
tbl_Merchants.MCC,
tbl_Merchants.[Updated Record], tbl_Merchants.[Info Updated],
tbl_Equipment.[POS Provided], tbl_Equipment.[Actual Terminal],
tbl_Equipment.[Actual Termina Update], tbl_Equipment.[TID#],
tbl_Equipment.[TID# Update], tbl_Equipment.[Application Name],
tbl_Equipment.[Application Name Update], tbl_Equipment.[Hypercom Mem],
tbl_Equipment.[Printer Type], tbl_Equipment.Imprinter,
tbl_Equipment.[Multi
Merchant], tbl_Equipment.[Touchtone Services], tbl_Equipment.[PC
Software],
tbl_Equipment.[PC Software Name], tbl_Equipment.[PC Software Version],
tbl_Equipment.[Multi Merchant], tbl_Equipment.[Check Service],
tbl_Equipment.[Ck Reader], tbl_Equipment.[Check Service Provider],
tbl_Equipment.[Check MID Num], tbl_Equipment.Debit, tbl_Equipment.[Debit
Update], tbl_Equipment.[Pin Pad Type], tbl_Equipment.[Pin Pad Serial
Number],
tbl_Equipment.AMEX, tbl_Equipment.[AMEX SE Num], tbl_Equipment.[AMEX
Update],
tbl_Equipment.DCVR, tbl_Equipment.[Discover MID Num], tbl_Equipment.[DCVR
Update], tbl_Equipment.DNRS, tbl_Equipment.[Diners MID Num],
tbl_Equipment.[Diners Update], tbl_Equipment.JCB, tbl_Equipment.[JCB MID
Num], tbl_Equipment.[JCB Update], tbl_Equipment.Table, tbl_Equipment.[Tip
Line], tbl_Equipment.Tab, tbl_Equipment.[Sales Tax],
tbl_Equipment.Invoice,
tbl_Equipment.[Footer Message], tbl_Equipment.[Access Num],
tbl_Equipment.Ticket, tbl_Equipment.Reference, tbl_Equipment.[Check In],
tbl_Equipment.[Check Out], tbl_Equipment.Folio,
tbl_Equipment.[Clerk/Server
Prompt], tbl_Equipment.[Auto Close], tbl_Equipment.[Auto Close Time],
tbl_CallData.[Call1 Date], tbl_CallData.[Call2 Date], tbl_CallData.[Call3
Date], tbl_CallData.[Call4 Date], tbl_CallData.[Call5 Date],
tbl_CallData.[Call Status Date], tbl_CallData.[Call Results],
tbl_CallData.Status, tbl_CallData.[Precall Completed Date],
tbl_CallData.[Referred Date], tbl_CallData.[Referred Reason],
tbl_CallData.[Escalated Date], tbl_CallData.[Escalation Reason],
tbl_CallData.[Completed By Client], tbl_CallData.[Closed Date],
[tbl_Precall
Appt Log].[Appt1 Date], [tbl_Precall Appt Log].[Appt2 Date], [tbl_Precall
Appt Log].[Appt3 Date], tbl_CallData.[Call1 Comment], tbl_CallData.[Logged
By], IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6))))) AS
MySorting
FROM ((tbl_Merchants LEFT JOIN tbl_Equipment ON tbl_Merchants.[New MID] =
tbl_Equipment.[Merchant Id]) LEFT JOIN tbl_CallData ON tbl_Merchants.[New
MID] = tbl_CallData.[Merchant ID]) LEFT JOIN [tbl_Precall Appt Log] ON
tbl_Merchants.[New MID] = [tbl_Precall Appt Log].[Merchant ID]
ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))));

I am still so lost...Thanks!

Please post the SQL statement of the entire query, not just the
calculated
field that we had discussed.

Or, use another calculated field, with a similar IIf construction, to
decide
which date field to use, and then sort on that calculated field too.

--

Ken Snell
<MS ACCESS MVP>

Status: Precall Complete, with Precall Complete Date field
Escalated, with Escalated Date field
Referred, with Referred Date Field
Closed, with Closed Date field
Completed By Client, with Coimpleted By Client Date field
Scheduled/Rescheduled
Active/Assigned
or Status field could be empty is not processed yet (Null)

I need the query to list all records in the status order listed above,
and
in ascending order by date.

MySorting: IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))))

Thanks!

 

[Ken Snell [MVP]]

Change this

ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6))))),
[tbl_CallData]![Precall Completed Date],
[tbl_CallData]![Escalated Date],
[tbl_CallData]![Referred Date],
[tbl_CallData]![Closed Date],
[tbl_CallData]![Completed By Client];

to this:

ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6))))),
CDate(Nz([tbl_CallData]![Precall Completed Date],"12/31/2999")),
CDate(Nz([tbl_CallData]![Escalated Date],"12/31/2999")),
CDate(Nz([tbl_CallData]![Referred Date],"12/31/2999")),
CDate(Nz)[tbl_CallData]![Closed Date],"12/31/2999")),
CDate(Nz([tbl_CallData]![Completed By Client],"12/31/2999"));


--

Ken Snell
<MS ACCESS MVP>

Ken,

It worked!! Thank you so much!!! Now, one last little thing...is there
any
way to order the Scheduled/Rescheduled by Call Status Date, then the
Active/Assigned by Call Status Date, and then the records that have no
Status
(Null) and no Call Status Date (Null) at the end of this query?

Change this part of the SQL statement:

ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))));


to this:

ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6))))),
[tbl_CallData]![Precall Completed Date],
[tbl_CallData]![Escalated Date],
[tbl_CallData]![Referred Date],
[tbl_CallData]![Closed Date],
[tbl_CallData]![Completed By Client];

--

Ken Snell
<MS ACCESS MVP>

It's Huge! But that's what the customer wants!

SELECT tbl_Merchants.[Old MID], tbl_Merchants.[New MID],
tbl_Merchants.[New
MID Update], tbl_Merchants.[Updated MID], tbl_Merchants.PNS,
tbl_Merchants.[Merchant Name], tbl_Merchants.Address1,
tbl_Merchants.[Address1 Update], tbl_Merchants.City,
tbl_Merchants.[City
Update], tbl_Merchants.State, tbl_Merchants.[State Update],
tbl_Merchants.Zip, tbl_Merchants.[Zip Update], tbl_Merchants.Phone,
tbl_Merchants.[Phone Update], tbl_Merchants.ContactFirst,
tbl_Merchants.ContactLast, tbl_Merchants.[Contact Update],
tbl_Merchants.MCC,
tbl_Merchants.[Updated Record], tbl_Merchants.[Info Updated],
tbl_Equipment.[POS Provided], tbl_Equipment.[Actual Terminal],
tbl_Equipment.[Actual Termina Update], tbl_Equipment.[TID#],
tbl_Equipment.[TID# Update], tbl_Equipment.[Application Name],
tbl_Equipment.[Application Name Update], tbl_Equipment.[Hypercom Mem],
tbl_Equipment.[Printer Type], tbl_Equipment.Imprinter,
tbl_Equipment.[Multi
Merchant], tbl_Equipment.[Touchtone Services], tbl_Equipment.[PC
Software],
tbl_Equipment.[PC Software Name], tbl_Equipment.[PC Software Version],
tbl_Equipment.[Multi Merchant], tbl_Equipment.[Check Service],
tbl_Equipment.[Ck Reader], tbl_Equipment.[Check Service Provider],
tbl_Equipment.[Check MID Num], tbl_Equipment.Debit,
tbl_Equipment.[Debit
Update], tbl_Equipment.[Pin Pad Type], tbl_Equipment.[Pin Pad Serial
Number],
tbl_Equipment.AMEX, tbl_Equipment.[AMEX SE Num], tbl_Equipment.[AMEX
Update],
tbl_Equipment.DCVR, tbl_Equipment.[Discover MID Num],
tbl_Equipment.[DCVR
Update], tbl_Equipment.DNRS, tbl_Equipment.[Diners MID Num],
tbl_Equipment.[Diners Update], tbl_Equipment.JCB, tbl_Equipment.[JCB
MID
Num], tbl_Equipment.[JCB Update], tbl_Equipment.Table,
tbl_Equipment.[Tip
Line], tbl_Equipment.Tab, tbl_Equipment.[Sales Tax],
tbl_Equipment.Invoice,
tbl_Equipment.[Footer Message], tbl_Equipment.[Access Num],
tbl_Equipment.Ticket, tbl_Equipment.Reference, tbl_Equipment.[Check
In],
tbl_Equipment.[Check Out], tbl_Equipment.Folio,
tbl_Equipment.[Clerk/Server
Prompt], tbl_Equipment.[Auto Close], tbl_Equipment.[Auto Close Time],
tbl_CallData.[Call1 Date], tbl_CallData.[Call2 Date],
tbl_CallData.[Call3
Date], tbl_CallData.[Call4 Date], tbl_CallData.[Call5 Date],
tbl_CallData.[Call Status Date], tbl_CallData.[Call Results],
tbl_CallData.Status, tbl_CallData.[Precall Completed Date],
tbl_CallData.[Referred Date], tbl_CallData.[Referred Reason],
tbl_CallData.[Escalated Date], tbl_CallData.[Escalation Reason],
tbl_CallData.[Completed By Client], tbl_CallData.[Closed Date],
[tbl_Precall
Appt Log].[Appt1 Date], [tbl_Precall Appt Log].[Appt2 Date],
[tbl_Precall
Appt Log].[Appt3 Date], tbl_CallData.[Call1 Comment],
tbl_CallData.[Logged
By], IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6))))) AS
MySorting
FROM ((tbl_Merchants LEFT JOIN tbl_Equipment ON tbl_Merchants.[New MID]
=
tbl_Equipment.[Merchant Id]) LEFT JOIN tbl_CallData ON
tbl_Merchants.[New
MID] = tbl_CallData.[Merchant ID]) LEFT JOIN [tbl_Precall Appt Log] ON
tbl_Merchants.[New MID] = [tbl_Precall Appt Log].[Merchant ID]
ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))));

I am still so lost...Thanks!

:

Please post the SQL statement of the entire query, not just the
calculated
field that we had discussed.

Or, use another calculated field, with a similar IIf construction, to
decide
which date field to use, and then sort on that calculated field too.

--

Ken Snell
<MS ACCESS MVP>

Status: Precall Complete, with Precall Complete Date field
Escalated, with Escalated Date field
Referred, with Referred Date Field
Closed, with Closed Date field
Completed By Client, with Coimpleted By Client Date field
Scheduled/Rescheduled
Active/Assigned
or Status field could be empty is not processed yet (Null)

I need the query to list all records in the status order listed
above,
and
in ascending order by date.

MySorting: IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))))

Thanks!

 

[Guest]

This works perfectly! Thank you so much for all your help! You are awsome!

Change this

ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6))))),
[tbl_CallData]![Precall Completed Date],
[tbl_CallData]![Escalated Date],
[tbl_CallData]![Referred Date],
[tbl_CallData]![Closed Date],
[tbl_CallData]![Completed By Client];

to this:

ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6))))),
CDate(Nz([tbl_CallData]![Precall Completed Date],"12/31/2999")),
CDate(Nz([tbl_CallData]![Escalated Date],"12/31/2999")),
CDate(Nz([tbl_CallData]![Referred Date],"12/31/2999")),
CDate(Nz)[tbl_CallData]![Closed Date],"12/31/2999")),
CDate(Nz([tbl_CallData]![Completed By Client],"12/31/2999"));


--

Ken Snell
<MS ACCESS MVP>

Ken,

It worked!! Thank you so much!!! Now, one last little thing...is there
any
way to order the Scheduled/Rescheduled by Call Status Date, then the
Active/Assigned by Call Status Date, and then the records that have no
Status
(Null) and no Call Status Date (Null) at the end of this query?

Change this part of the SQL statement:

ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))));


to this:

ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6))))),
[tbl_CallData]![Precall Completed Date],
[tbl_CallData]![Escalated Date],
[tbl_CallData]![Referred Date],
[tbl_CallData]![Closed Date],
[tbl_CallData]![Completed By Client];

--

Ken Snell
<MS ACCESS MVP>




It's Huge! But that's what the customer wants!

SELECT tbl_Merchants.[Old MID], tbl_Merchants.[New MID],
tbl_Merchants.[New
MID Update], tbl_Merchants.[Updated MID], tbl_Merchants.PNS,
tbl_Merchants.[Merchant Name], tbl_Merchants.Address1,
tbl_Merchants.[Address1 Update], tbl_Merchants.City,
tbl_Merchants.[City
Update], tbl_Merchants.State, tbl_Merchants.[State Update],
tbl_Merchants.Zip, tbl_Merchants.[Zip Update], tbl_Merchants.Phone,
tbl_Merchants.[Phone Update], tbl_Merchants.ContactFirst,
tbl_Merchants.ContactLast, tbl_Merchants.[Contact Update],
tbl_Merchants.MCC,
tbl_Merchants.[Updated Record], tbl_Merchants.[Info Updated],
tbl_Equipment.[POS Provided], tbl_Equipment.[Actual Terminal],
tbl_Equipment.[Actual Termina Update], tbl_Equipment.[TID#],
tbl_Equipment.[TID# Update], tbl_Equipment.[Application Name],
tbl_Equipment.[Application Name Update], tbl_Equipment.[Hypercom Mem],
tbl_Equipment.[Printer Type], tbl_Equipment.Imprinter,
tbl_Equipment.[Multi
Merchant], tbl_Equipment.[Touchtone Services], tbl_Equipment.[PC
Software],
tbl_Equipment.[PC Software Name], tbl_Equipment.[PC Software Version],
tbl_Equipment.[Multi Merchant], tbl_Equipment.[Check Service],
tbl_Equipment.[Ck Reader], tbl_Equipment.[Check Service Provider],
tbl_Equipment.[Check MID Num], tbl_Equipment.Debit,
tbl_Equipment.[Debit
Update], tbl_Equipment.[Pin Pad Type], tbl_Equipment.[Pin Pad Serial
Number],
tbl_Equipment.AMEX, tbl_Equipment.[AMEX SE Num], tbl_Equipment.[AMEX
Update],
tbl_Equipment.DCVR, tbl_Equipment.[Discover MID Num],
tbl_Equipment.[DCVR
Update], tbl_Equipment.DNRS, tbl_Equipment.[Diners MID Num],
tbl_Equipment.[Diners Update], tbl_Equipment.JCB, tbl_Equipment.[JCB
MID
Num], tbl_Equipment.[JCB Update], tbl_Equipment.Table,
tbl_Equipment.[Tip
Line], tbl_Equipment.Tab, tbl_Equipment.[Sales Tax],
tbl_Equipment.Invoice,
tbl_Equipment.[Footer Message], tbl_Equipment.[Access Num],
tbl_Equipment.Ticket, tbl_Equipment.Reference, tbl_Equipment.[Check
In],
tbl_Equipment.[Check Out], tbl_Equipment.Folio,
tbl_Equipment.[Clerk/Server
Prompt], tbl_Equipment.[Auto Close], tbl_Equipment.[Auto Close Time],
tbl_CallData.[Call1 Date], tbl_CallData.[Call2 Date],
tbl_CallData.[Call3
Date], tbl_CallData.[Call4 Date], tbl_CallData.[Call5 Date],
tbl_CallData.[Call Status Date], tbl_CallData.[Call Results],
tbl_CallData.Status, tbl_CallData.[Precall Completed Date],
tbl_CallData.[Referred Date], tbl_CallData.[Referred Reason],
tbl_CallData.[Escalated Date], tbl_CallData.[Escalation Reason],
tbl_CallData.[Completed By Client], tbl_CallData.[Closed Date],
[tbl_Precall
Appt Log].[Appt1 Date], [tbl_Precall Appt Log].[Appt2 Date],
[tbl_Precall
Appt Log].[Appt3 Date], tbl_CallData.[Call1 Comment],
tbl_CallData.[Logged
By], IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6))))) AS
MySorting
FROM ((tbl_Merchants LEFT JOIN tbl_Equipment ON tbl_Merchants.[New MID]
=
tbl_Equipment.[Merchant Id]) LEFT JOIN tbl_CallData ON
tbl_Merchants.[New
MID] = tbl_CallData.[Merchant ID]) LEFT JOIN [tbl_Precall Appt Log] ON
tbl_Merchants.[New MID] = [tbl_Precall Appt Log].[Merchant ID]
ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))));

I am still so lost...Thanks!

:

Please post the SQL statement of the entire query, not just the
calculated
field that we had discussed.

Or, use another calculated field, with a similar IIf construction, to
decide
which date field to use, and then sort on that calculated field too.

--

Ken Snell
<MS ACCESS MVP>

Status: Precall Complete, with Precall Complete Date field
Escalated, with Escalated Date field
Referred, with Referred Date Field
Closed, with Closed Date field
Completed By Client, with Coimpleted By Client Date field
Scheduled/Rescheduled
Active/Assigned
or Status field could be empty is not processed yet (Null)

I need the query to list all records in the status order listed
above,
and
in ascending order by date.

MySorting: IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))))

Thanks!

 

[Ken Snell [MVP]]

Glad to hear it worked. You're welcome.

--

Ken Snell
<MS ACCESS MVP>

This works perfectly! Thank you so much for all your help! You are
awsome!

Change this

ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6))))),
[tbl_CallData]![Precall Completed Date],
[tbl_CallData]![Escalated Date],
[tbl_CallData]![Referred Date],
[tbl_CallData]![Closed Date],
[tbl_CallData]![Completed By Client];

to this:

ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6))))),
CDate(Nz([tbl_CallData]![Precall Completed Date],"12/31/2999")),
CDate(Nz([tbl_CallData]![Escalated Date],"12/31/2999")),
CDate(Nz([tbl_CallData]![Referred Date],"12/31/2999")),
CDate(Nz)[tbl_CallData]![Closed Date],"12/31/2999")),
CDate(Nz([tbl_CallData]![Completed By Client],"12/31/2999"));


--

Ken Snell
<MS ACCESS MVP>

Ken,

It worked!! Thank you so much!!! Now, one last little thing...is
there
any
way to order the Scheduled/Rescheduled by Call Status Date, then the
Active/Assigned by Call Status Date, and then the records that have no
Status
(Null) and no Call Status Date (Null) at the end of this query?


:

Change this part of the SQL statement:

ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))));


to this:

ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6))))),
[tbl_CallData]![Precall Completed Date],
[tbl_CallData]![Escalated Date],
[tbl_CallData]![Referred Date],
[tbl_CallData]![Closed Date],
[tbl_CallData]![Completed By Client];

--

Ken Snell
<MS ACCESS MVP>




It's Huge! But that's what the customer wants!

SELECT tbl_Merchants.[Old MID], tbl_Merchants.[New MID],
tbl_Merchants.[New
MID Update], tbl_Merchants.[Updated MID], tbl_Merchants.PNS,
tbl_Merchants.[Merchant Name], tbl_Merchants.Address1,
tbl_Merchants.[Address1 Update], tbl_Merchants.City,
tbl_Merchants.[City
Update], tbl_Merchants.State, tbl_Merchants.[State Update],
tbl_Merchants.Zip, tbl_Merchants.[Zip Update], tbl_Merchants.Phone,
tbl_Merchants.[Phone Update], tbl_Merchants.ContactFirst,
tbl_Merchants.ContactLast, tbl_Merchants.[Contact Update],
tbl_Merchants.MCC,
tbl_Merchants.[Updated Record], tbl_Merchants.[Info Updated],
tbl_Equipment.[POS Provided], tbl_Equipment.[Actual Terminal],
tbl_Equipment.[Actual Termina Update], tbl_Equipment.[TID#],
tbl_Equipment.[TID# Update], tbl_Equipment.[Application Name],
tbl_Equipment.[Application Name Update], tbl_Equipment.[Hypercom
Mem],
tbl_Equipment.[Printer Type], tbl_Equipment.Imprinter,
tbl_Equipment.[Multi
Merchant], tbl_Equipment.[Touchtone Services], tbl_Equipment.[PC
Software],
tbl_Equipment.[PC Software Name], tbl_Equipment.[PC Software
Version],
tbl_Equipment.[Multi Merchant], tbl_Equipment.[Check Service],
tbl_Equipment.[Ck Reader], tbl_Equipment.[Check Service Provider],
tbl_Equipment.[Check MID Num], tbl_Equipment.Debit,
tbl_Equipment.[Debit
Update], tbl_Equipment.[Pin Pad Type], tbl_Equipment.[Pin Pad Serial
Number],
tbl_Equipment.AMEX, tbl_Equipment.[AMEX SE Num], tbl_Equipment.[AMEX
Update],
tbl_Equipment.DCVR, tbl_Equipment.[Discover MID Num],
tbl_Equipment.[DCVR
Update], tbl_Equipment.DNRS, tbl_Equipment.[Diners MID Num],
tbl_Equipment.[Diners Update], tbl_Equipment.JCB, tbl_Equipment.[JCB
MID
Num], tbl_Equipment.[JCB Update], tbl_Equipment.Table,
tbl_Equipment.[Tip
Line], tbl_Equipment.Tab, tbl_Equipment.[Sales Tax],
tbl_Equipment.Invoice,
tbl_Equipment.[Footer Message], tbl_Equipment.[Access Num],
tbl_Equipment.Ticket, tbl_Equipment.Reference, tbl_Equipment.[Check
In],
tbl_Equipment.[Check Out], tbl_Equipment.Folio,
tbl_Equipment.[Clerk/Server
Prompt], tbl_Equipment.[Auto Close], tbl_Equipment.[Auto Close
Time],
tbl_CallData.[Call1 Date], tbl_CallData.[Call2 Date],
tbl_CallData.[Call3
Date], tbl_CallData.[Call4 Date], tbl_CallData.[Call5 Date],
tbl_CallData.[Call Status Date], tbl_CallData.[Call Results],
tbl_CallData.Status, tbl_CallData.[Precall Completed Date],
tbl_CallData.[Referred Date], tbl_CallData.[Referred Reason],
tbl_CallData.[Escalated Date], tbl_CallData.[Escalation Reason],
tbl_CallData.[Completed By Client], tbl_CallData.[Closed Date],
[tbl_Precall
Appt Log].[Appt1 Date], [tbl_Precall Appt Log].[Appt2 Date],
[tbl_Precall
Appt Log].[Appt3 Date], tbl_CallData.[Call1 Comment],
tbl_CallData.[Logged
By], IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not Null,5,6)))))
AS
MySorting
FROM ((tbl_Merchants LEFT JOIN tbl_Equipment ON tbl_Merchants.[New
MID]
=
tbl_Equipment.[Merchant Id]) LEFT JOIN tbl_CallData ON
tbl_Merchants.[New
MID] = tbl_CallData.[Merchant ID]) LEFT JOIN [tbl_Precall Appt Log]
ON
tbl_Merchants.[New MID] = [tbl_Precall Appt Log].[Merchant ID]
ORDER BY IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not
Null,5,6)))));

I am still so lost...Thanks!

:

Please post the SQL statement of the entire query, not just the
calculated
field that we had discussed.

Or, use another calculated field, with a similar IIf construction,
to
decide
which date field to use, and then sort on that calculated field
too.

--

Ken Snell
<MS ACCESS MVP>

Status: Precall Complete, with Precall Complete Date field
Escalated, with Escalated Date field
Referred, with Referred Date Field
Closed, with Closed Date field
Completed By Client, with Coimpleted By Client Date
field
Scheduled/Rescheduled
Active/Assigned
or Status field could be empty is not processed yet (Null)

I need the query to list all records in the status order listed
above,
and
in ascending order by date.

MySorting: IIf([tbl_CallData]![Precall Completed Date] Is Not
Null,1,IIf([tbl_CallData]![Escalated Date] Is Not
Null,2,IIf([tbl_CallData]![Referred Date] Is Not
Null,3,IIf([tbl_CallData]![Closed Date] Is Not
Null,4,IIf([tbl_CallData]![Completed By Client] Is Not
Null,5,6)))))

Thanks!

 

[Guest]

Hello I have a similar problem. My database has drug scree results for my
analyses.
I enter the results for each sample into the table. I have 15 results fields
(results1, results2.........results 15).
I need a query that will search all 15 results fields at once for specific
drugs.
For example. I want all the records that have "caffeine". well I don't want
to prompt the user to enter "caffeine" 15 times for each results field.
do you have any ideas?
thanks,
Ryan

Add a calculated field to the query:

MySorting: IIf([Completed] Is Not Null, 1, IIf([Referred] Is Not Null, 2,
IIf([Closed] Is Not Null, 3, 4)))

Sort ascending on this field along with whichever other fields you're using
for sort order.

--

Ken Snell
<MS ACCESS MVP>

I have a query that pulls records with various status dates. Some have
dates
in a Complete field, some have dates in a Referred field, some have dates
in
a Closed field and some have not been processed so they have no dates
(Nulls)
in any of the fields. I want to sort the query with all the Completes in
ascending order, then Referred in ascending order, then Closed in
ascending
order, then the Nulls at the end. Help!!!

 

[Duane Hookom]

Search for a reply to your previous posting in the "multiple field search
criteria" thread.
Please only ask a question once and consider normalizing your table
structure.

--
Duane Hookom
MS Access MVP

Hello I have a similar problem. My database has drug scree results for my
analyses.
I enter the results for each sample into the table. I have 15 results
fields
(results1, results2.........results 15).
I need a query that will search all 15 results fields at once for specific
drugs.
For example. I want all the records that have "caffeine". well I don't
want
to prompt the user to enter "caffeine" 15 times for each results field.
do you have any ideas?
thanks,
Ryan

Add a calculated field to the query:

MySorting: IIf([Completed] Is Not Null, 1, IIf([Referred] Is Not Null, 2,
IIf([Closed] Is Not Null, 3, 4)))

Sort ascending on this field along with whichever other fields you're
using
for sort order.

--

Ken Snell
<MS ACCESS MVP>

I have a query that pulls records with various status dates. Some have
dates
in a Complete field, some have dates in a Referred field, some have
dates
in
a Closed field and some have not been processed so they have no dates
(Nulls)
in any of the fields. I want to sort the query with all the Completes
in
ascending order, then Referred in ascending order, then Closed in
ascending
order, then the Nulls at the end. Help!!!