Custom Order for Continuous Form

[Bra81ndy]

I want to specify a custom order for a sub continuous form. I tried an IIF statement like IIF([Test] Like "Viscosity", 1, IIF([Test] Like "Viscosity 0hr", 2, 3)) in the Order by property of the sub form. The Form basically ignores the statement. Also is there a way to force the form to order by a multi-table Query? Your Help would be much appreciated.

 

[GingerR]

Not sure what this means:

Also is there a way to force the form to order by a multi-table Query?[/qhote]

Anyhow, take out that OrderBy thing you made. All you put in the OrderBy entry is a field name.

Instead, in the recordsource of the form, put your little line of code into a new calculated field:

SortOrder: iif(blah)


And sort that field Ascending.

Ok?


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[lameid]

The form has an orderby property. You specify a field list here that you want to sort by. Sometimes forms will take the order of a table or query but sometimes they do odd things too. The safest bet is the order by property.

As for your custom sort, you could use your IIF idea but it is relatively slow.

I would recommend a table with the values like "Viscosity" in one field and a second field called SortOrder. Then you just enter the Sort order you want, outer join to the sorting table and order by the SortOrder field. So yes, you can sort a multitable query.

 

[Bra81ndy]

Thank you for the help. The solution incorporated the advice from both GingerR and lameid. I made a table that outlined the order I wanted for the Test values called QC Index. Then I placed this code in the record source for the form.

SELECT [QC Results].LabNotebook, [QC Results].Test, [QC Results].Result
FROM [QC Results] INNER JOIN [QC Index] ON [QC Results].Test = [QC Index].Test
ORDER BY [QC Index].Order;