Using iif for sort criteria

[Reese]

I am creating a query to select and sort events whose payments are overdue, prioritizing those that are most overdue at the top. The catch is that the due dates for payment varies based on several conditions and criteria.

Here's my end goal of the sort order (it's not in SQL, I'm just describing it):

1st step- If [Program_Code]="BD" Or "GT" Or "SG" Or "SC" Or "PR", then the sort criteria is [Date_of_Event] < Date()

2nd step- If [Program_Code]="WE" Or "KD", then the sort criteria is [Date_of_Invoice] < Date()

3rd step- If [Program_Code]="ZM" And [Cost_Category]="Full Price" Or "Discount", then the sort criteria is [Invoice_Date]+30 Days is < Date()

4th step- If [Program_Code]="BD" Or "PR" And [Deposit_Paid] Is Null, then the sort criteria is [Invoice_Date] < Date()

I'm not sure how to go about this but I have a feeling it involves an iif statement in the SQL and/or breaking it into smaller queries that are then consolidated into a "master" query. Thanks for the help.

 

[Brianwarnock]

I'm confused, what you are calling sort criteria looks like part of the selection criteria.

Brian

 

[Brianwarnock]

If I am correct and what you are saying is sort in the order of the steps, then I would write a function for execution in a new column of the query , pass it the relevant info and have it return values 0 to 4 , 0 being not wanted, thus the criteria for the row would be > 0 .

Sort on this column

Step 1 must include Not IsNull(Deposit_Paid) for "BD" and"PR"

Brian

 

[Mihail]

Agree with Brian (post #2)
Unlike Brian, I think that you wish to filter the returned values.
Assuming that you display this values by using a report based on your query, then I advice you to design a Public Function that will return the SQL for the WHERE clause that you can use when you open the report.

 

[Reese]

Thanks, guys. I know enough to understand a glimmer of what you are talking about but not enough to actually impliment it--or even where to start.

Would this be something that I would write into the query's SQL or would it be something that would be written as VBA that would then impact the query? The end format of this query would be a continuous report (or form, since I'll need a button to open up a different form to that record's ID).

Thanks for the help. In the mean time I'll back up and start fiddling with things.

 

[Brianwarnock]

You have not said whether I am correct or not so it makes me reluctant to write any code to demonstrate what I mean.

I did not understand Mihail's interpretation so cannot help there.

Brian

 

[Mihail]

@Brian.
In "my" English the "sort" word refer to a sequence (1,2,3,4) or (4,3,2,1). Sorry if I'm wrong.
It is why I understand Reese's post as a request for filter the records (to show only those records that meet his 4 criteria).
Post #5 seems to confirm this because he wrote:

The end format of this query would be a continuous report (or form .....)

but still I'm not sure.

@Reese
Say us exactly what you have and exactly what you wish to achieve.

 

[Brianwarnock]

Mihail I agree 100% with your definition of a sort, however Reese started his first post with the words " select and sort" so I assume that he wants to sort his filtered records, and further assumed that it was in the order of his steps. I was hoping that he would confirm that.

Brian

 

[Mihail]

@Brian
I have only 2 years from my first meeting with Access. So I understand better than you (I think) a beginner that use "sort" instead "filter", "cell" instead "field in record" and so on.
Here is my first question about Access. Take a look (if you wish) and smile.

 

[Reese]

Sorry for the confusion, guys, perhaps my phrasing wasn't the best. To clarify, I want the query to do the following:

-Filter ALL records based on [Paid] and [Incomplete_Booking] content (which I didn't include above because I know how to do that)
-Among those, filter the results in an "if then" manner (in other words each result doesn't meet ALL criteria, but need only meet ONE OF the criteria) and sort the "if then" criteria in a certain manner.

The filter criteria and order in which they are sorted is what I included in my first post.

 

[Brianwarnock]

That does not change my view, I still see the sort order as Step1 to step4, ie records meeting the conditions in Step1 are sorted before those that meet step2

The problem is with the phrases such as

then the sort criteria is [Date_of_Event] < Date()

[Date_of_Event] < Date() is a selection (filtering) criteria, sorting is ascending/descending based on a value. Therefore I believe that my first post is correct and is not effected by the extra conditions.

BTW do you have 2 fields Date_of_Invoice and Invoice_Date ?
Brian

 

[Brianwarnock]

Roughly the kind of thing I'm thinking of is a function like

Function getorder(pc As String, doi As Date, cg As String, dp As Currency) As Long

Select Case pc
    Case "GT", "SG", "SC"
        getorder = 1
    Case "BD", "PR"
        If (dp) = 0 And doi < Date Then
        getorder = 4
        Else
        getorder = 1
        End If
'    Case etc
'    .
'    .
Case Else
        getorder = 0
 End Select

End Function

in the query

newfield   sortit: getorder(pc,doi,cg,dp)
sort         ascending
criteria    >0

Note that a currency or numeric field cannot be Null it defaults to 0, you get a data mismatch /

brian

 

[Reese]

do you have 2 fields Date_of_Invoice and Invoice_Date ?

Oops, that was just a typo in the post, it's Invoice_Date not Date_of_Invoice. I think I see what you're saying with the case function but I've never worked with them before so I'm going to need to do some research before I can test it out and get back to you.

 

[Reese]

After looking at the case function stuff, I realized that I'd be spending more time than I have available to try and figure it out. So I've decided to just break this into separate queries--in which case I can handle each step as a different query. The end result functions perfectly, though it's a little less efficient for the end user.

Not saying that case function wouldn't work; it's just I don't have enough time to fiddle with it. After rolling the project out I can go back to try and streamline it later.

Thanks for the help, though.

 

[Brianwarnock]

I still don't know if my understanding is correct, a yes or no would be appreciated. You have not responded to my posts such as 11 asking if the sort order is step order ie those that meet step 1 criteria before those that meet step 2, I'm not going to spend time creating a DB writing code and testing if I am on the wrong track.
In fact a small mdb would be appreciated.

You do not have to use Select case in the function you can use all If .. Then ..else, but I felt Select Case for the Program_Code and Ifs for the next level simplified the code.

Brian