Sorting 3 field from option group selection 1

[azalealee]

Hello

I have a select query that has fields -
Policy_ID
Policy_Name
Rev_Due_Date

I would like to sort these fields depending on a user selection from an option group on a form with ID and Name in ascending order and Date in decending order.

I have tried using something like this:
IIf([Forms]![F_ReportsMenu]![FrameOrderBy]=0,[M_Policy].[Policy_Name],IIF([Forms]![F_ReportsMenu]![FrameOrderBy]=1,[M_Policy].[Policy_ID],[M_Revision].[Rev_Due_Date]))

as another column in the query with Sort set to "Ascending", but it only sorted on the ID.

I have also tried putting:
[Forms]![F_ReportsMenu]![FrameOrderBy]= (option group value)

into the criteria of the applicable fields, but that returned no data.

Does anyone have any ideas?

Thanks in advance.

Azalea.

 

[scriverb]

I am going to assume from your posting that there are 1,2,and 3 values for your option group. Use the following SQL for your query:
SELECT [M_Policy].[Policy_ID], M_Policy].[Policy_Name],[M_Policy].[Rev_Due_Date]
FROM M_Policy
ORDER BY
Switch([Forms]![F_ReportsMenu]![FrameOrderBy]=1,[M_Policy]![Policy_ID],[Forms]![F_ReportsMenu]![FrameOrderBy]=2,[M_Policy]![Policy_Name],[Forms]![F_ReportsMenu]![FrameOrderBy]=3,Null), Switch([Forms]![F_ReportsMenu]![FrameOrderBy]=1,Null,[Forms]![F_ReportsMenu]![FrameOrderBy]=2,Null,[Forms]![F_ReportsMenu]![FrameOrderBy]=3,[M_Policy]![Rev_Due_Date]) DESC;

The two Switch statements create two new columns that are not visible when your data is returned that are filled with the data from your table that you want to be sorted on. I hope that this is what you were looking for. Bob Scriver

 

[azalealee]

Hi Bob

It works! Thank you! Exactly what I needed. But I'm now having a different sorting problem....

The dates and the ID's are sorting as strings of characters - not numeric values.
eg Date in Descending order is coming out like:

9/8/2003
5/1/2004
10/3/2001

While it should be:

5/1/2004
9/8/2003
10/3/2001

ID in ascending order is coming out like:

10
109
11
112

Have any suggestions on how I can fix this?

Azalea.

 

[scriverb]

Try this. I don't have any data to test this out but I have perfomed String to Long Integer and String to Date conversions on the sort fields. See if this works.

SELECT [M_Policy].[Policy_ID], M_Policy].[Policy_Name],[M_Policy].[Rev_Due_Date]
FROM M_Policy
ORDER BY
Switch([Forms]![F_ReportsMenu]![FrameOrderBy]=1,CLng([M_Policy]![Policy_ID]),[Forms]![F_ReportsMenu]![FrameOrderBy]=2,[M_Policy]![Policy_Name],[Forms]![F_ReportsMenu]![FrameOrderBy]=3,Null), Switch([Forms]![F_ReportsMenu]![FrameOrderBy]=1,Null,[Forms]![F_ReportsMenu]![FrameOrderBy]=2,Null,[Forms]![F_ReportsMenu]![FrameOrderBy]=3,CDate([M_Policy]![Rev_Due_Date])) DESC;

Get back to me with the results.
Bob Scriver

 

[azalealee]

Nope sorry, hasn't made a difference.

Azalea.

 

[scriverb]

SELECT [M_Policy].[Policy_ID], M_Policy].[Policy_Name],[M_Policy].[Rev_Due_Date]
FROM M_Policy
ORDER BY
Switch([Forms]![F_ReportsMenu]![FrameOrderBy]=1,Format([M_Policy]![Policy_ID],"0000000&quot,[Forms]![F_ReportsMenu]![FrameOrderBy]=2,[M_Policy]![Policy_Name],[Forms]![F_ReportsMenu]![FrameOrderBy]=3,Null), Switch([Forms]![F_ReportsMenu]![FrameOrderBy]=1,Null,[Forms]![F_ReportsMenu]![FrameOrderBy]=2,Null,[Forms]![F_ReportsMenu]![FrameOrderBy]=3,Format([Table1]![Rev_Due_Date], "yyyymmdd&quot) DESC;

Give this one a try. Bob Scriver

 

[scriverb]

SELECT [M_Policy].[Policy_ID], M_Policy].[Policy_Name],[M_Policy].[Rev_Due_Date]
FROM M_Policy
ORDER BY
Switch([Forms]![F_ReportsMenu]![FrameOrderBy]=1,Format(CLng([M_Policy]![Policy_ID]),"0000000&quot,[Forms]![F_ReportsMenu]![FrameOrderBy]=2,[M_Policy]![Policy_Name],[Forms]![F_ReportsMenu]![FrameOrderBy]=3,Null), Switch([Forms]![F_ReportsMenu]![FrameOrderBy]=1,Null,[Forms]![F_ReportsMenu]![FrameOrderBy]=2,Null,[Forms]![F_ReportsMenu]![FrameOrderBy]=3,Format([Table1]![Rev_Due_Date], "yyyymmdd&quot) DESC;

I think we may have to perform the Format function on Policy_ID after it is converted to a Long Integer. Sorry, about the multiple attempts here.
Bob Scriver

 

[azalealee]

Nope, still not working. At every option group selection, only Policy_ID is sorting. Even Policy_Name doesn't sort correctly.

This is the SQL I'm using:

ORDER BY Switch([Forms]![F_ReportsMenu]![FrameOrderBy]=0,[M_Policy]![Policy_Name],[Forms]![F_ReportsMenu]![FrameOrderBy]=1,Format(CLng([M_Policy]![Policy_ID]),"0000000&quot,[Forms]![F_ReportsMenu]![FrameOrderBy]=2,Format(DateAdd([Revision_Frequency_Type],[Revision_Frequency],[Date_Revised]), "yyyymmdd&quot);

I've been sorting the date to Ascending for the time being.

Azalea.

 

[scriverb]

You aren't using the entire Order By code. There are two Switch statements and they must both be included:
ORDER BY Switch([Forms]![F_ReportsMenu]![FrameOrderBy]=1,Format(CLng([M_Policy]![Policy_ID]),"0000000&quot,[Forms]![F_ReportsMenu]![FrameOrderBy]=2,[M_Policy]![Policy_Name],[Forms]![F_ReportsMenu]![FrameOrderBy]=3,Null), Switch([Forms]![F_ReportsMenu]![FrameOrderBy]=1,Null,[Forms]![F_ReportsMenu]![FrameOrderBy]=2,Null,[Forms]![F_ReportsMenu]![FrameOrderBy]=3,Format([Table1]![Rev_Due_Date], "yyyymmdd&quot) DESC;

Copy this and paste it over what you currently are using.
Bob Scriver

 

[scriverb]

Make sure you modify the option group numbers from 1,2,3 to 0,1,2. Sorry. Bob Scriver

 

[azalealee]

OK, I'm using the two Switch statements, but I'm still getting the same results as before - only Policy_ID is sorting.

ORDER BY Switch([Forms]![F_ReportsMenu]![FrameOrderBy]=0,Format(CLng([M_Policy]![Policy_ID]),"0000000&quot,[Forms]![F_ReportsMenu]![FrameOrderBy]=1,[M_Policy]![Policy_Name],[Forms]![F_ReportsMenu]![FrameOrderBy]=2,Null), Switch([Forms]![F_ReportsMenu]![FrameOrderBy]=0,Null,[Forms]![F_ReportsMenu]![FrameOrderBy]=1,Null,[Forms]![F_ReportsMenu]![FrameOrderBy]=2, Format(DateAdd([Revision_Frequency_Type],[Revision_Frequency],[Date_Revised]), "yyyymmdd&quot) DESC;

The red text is Rev_Due_Date. Rev_Due_Date is calculated from fields in two tables - M_Policy and M_Revision, and I found I couldn't just use [Rev_Due_Date].

The SELECT statement contains: DateAdd([Revision_Frequency_Type],[Revision_Frequency],[Date_Revised]) AS Revision_Due_Date

I'm not sure if this would affect the switch statement.

Thanks for your patience Bob.

Azalea.

 

[scriverb]

Please tell me what data is in each of fields below:
[Revision_Frequency_Type],[Revision_Frequency],[Date_Revised]
And, do these fields come from the table m_policy?

Thank you. Bob Scriver

 

[azalealee]

Hi Bob

In table M_Policy:
-Revision_Frequency_Type is a text field ie either yyyy or m
-Revision_Frequency is a long-integer number eg 1,2,3,4...

In table M_Revision:
-Date_Revised is a date/time field eg 19/12/2002, 24/11/2001..

All 3 fields are needed to get Revision_Due_Date.

Azalea

 

[scriverb]

Let's use the following as the end of the switch statement starting at where your red code began and overwriting everything else. Also, let's take this code less the ending DESC; and create a new column to see exactly what is being created.

CStr(Format(DateAdd([Revision_Frequency_Type],[Revision_Frequency],[Date_Revised]), "yyyymmdd&quot)) DESC;

Get back to me with the results.
Bob Scriver

 

[azalealee]

Thank you! Thank you! Thank you! It has finally worked!

Thanks for all your efforts!

Next time you're in Australia and want a taste of local hospitality, give us a call.

For everyone else's info, this was the final code:

Switch([Forms]![F_ReportsMenu]![FrameOrderBy]=0,Format(CLng([M_Policy]![Policy_ID]),"0000000&quot,[Forms]![F_ReportsMenu]![FrameOrderBy]=1,[M_Policy]![Policy_Name],[Forms]![F_ReportsMenu]![FrameOrderBy]=2,Null), Switch([Forms]![F_ReportsMenu]![FrameOrderBy]=0,Null,[Forms]![F_ReportsMenu]![FrameOrderBy]=1,Null,[Forms]![F_ReportsMenu]![FrameOrderBy]=2,Format(DateAdd([Revision_Frequency_Type],[Revision_Frequency],[Date_Revised]),"yyyymmdd&quot)DESC;

Kind Regards
Azalea.

 

[scriverb]

Thank you for the Start!!! And, thank you for the invite to stop in for some Australian hospitality. The next time the door is knocking you never know who might just be a calling, Mate!!!

In your final posting you say worked, you didn't use my final attempt which was the CStr function. Which way did it finally work. I just wanted to know if the CStr was necessary. Bob Scriver

 

[azalealee]

Hmmm...

I did put the CStr into the code and I don't remember taking it out... But I did have a long fiddle with it...

I just tried the query both ways - with and without using CStr and both ways seem to work fine.

Azalea