Conditional Sorting

[dargo72]

Hello,



I'm not sure exactly how to ask this question, but here it goes.

I have 2 "yes/No" fields for a given record. Also, each of these fields has an associated "date" for which the event is scheduled to occur. I want to be able to sort by the date of either field. However, if the first "Yes/No" field is "Yes" (meaning the event has occurred, I want to ignore its "Date" and only use the second "Yes/No" fields associated date to sort.

Does this make sense?

Any help is appreciated.

[Bob Fitz]

The first boolean field is not required in the table as a field. Use a calculated control (check box) on a form.
What determines the value of the second boolean field.

[dargo72]

The first boolean field is not required in the table as a field. Use a calculated control (check box) on a form.
What determines the value of the second boolean field.

I have no idea what you are asking.... sorry.

But let me try to clarify:

For 1 record I have 2 events that are each scheduled to occur on specific dates. I want to run a query sorting by date for events that have not yet occurred. Meaning if event #1 has not occurred, I want that date to be the determining date. If event #1 has already occurred, I want its date to be ignored and the event #2 date to be the determining factor.

Does this help?

[ItsMe]

Is there a date value in the respective date field when the check box value is equal to False?

You may have better control accomplishing your task using VBA if it is a complex set of rules.

[dargo72]

Is there a date value in the respective date field when the check box value is equal to False?

You may have better control accomplishing your task using VBA if it is a complex set of rules.

Yes, there is always a date, as the date(s) are when something is planned to be completed, not the actual date when it is completed.....

[ItsMe]

Maybe you can provide an example of your report's or form's recordsource. Depending on when/what you are producing with your query will determine how to sort your data. Maybe you can also list the control/field names that will be sorted and determine the conditions.

[dargo72]

Maybe you can provide an example of your report's or form's recordsource. Depending on when/what you are producing with your query will determine how to sort your data. Maybe you can also list the control/field names that will be sorted and determine the conditions.

Thank you. Let me know if this is what you mean.....

For each record the item has 2 "Yes/No" Fields (check boxes on the form) related to whether the event occurred or not. These fields are Confirmed? and Verified?

Each record also has 2 date fields related to when these 2 events are scheduled to occur. Confirmation Date Planned, and Verification Date Planned. These 2 field are always populated whether or not the even has occurred.

I want to sort these record by the earliest date, but if the if the Confirmed? field is "Yes" I want to ignore the "Confirmation Date Planned" field and only sort via the "Verification Date Planned" field.

If both "Yes/No" fields equal "Yes" I don't need to do anything with the record. Also, the "Confirmation Date Planned" will always be before the "Verification Date Planned" if this helps.

I would like to be able to put some calculation into the criteria of a query to get the desired result.

Does this help?

[ItsMe]

I would like to be able to put some calculation into the criteria of a query to get the desired result.

Does this help?

I believe the theory of what you want to accomplish is understood. In order to put it into practice you will need to do your sorting at the time your report or form opens. I believe the best way to go about this is to place some VBA in a class module. Dedicating a saved query to a form or report is not something I practice and I am not comfortable with giving advice on. Storing form names in query criteria can get messy, IMO.

If you build a query and provide the SQL and at least let us know if you are planning to use it in a form or a report, then help should be forthcoming. It will be beneficial if you identify and share the names of the fields/controls for your yes/no fields. Please build something first. This way, you can share the information necessary to go to the next step.