Record Source - Order By

[esskaykay]

We have a database that tracks sanitary and storm sewer maintenance. There is a form that I want to order based on the sewer type field (SYSTEM = san or sto). I can do this with a query but this code was written by others so I’m a bit nervous about changing it too much. Currently the form’s Record Source is:

Select * from TACTION order by Jet_Seq

This works fine for [SYSTEM]=”SAN” but not STO. I tried modifying the Record Sources a couple times but neither worked.

=iif([system]=”san”, select * from TACTION order by Jet_Seq, select * from TACTION order by Clean_Seq)

= select * from TACTION order by (if([system]=”san”, Jet_Seq, Clean_Seq))

Am I close here or way off base? Any suggestions would be greatly appreciated.

Thanks,
SKK

 

[gemma-the-husky]

what isnt working?

are you not seeing the STO's or they just not sorted correctly

which field are you trying to sort on for each record type - the same or different? if different, is meaningful data stored in both fields?

 

[esskaykay]

If i code in the IIF command, I see nothing. An error returns stating "The record source does no exist" (see attached).

If [system]="SAN" i want to sort on JET_SEQ, if [system]="STO", i want to sort on CLEAN_SEQ.

SKK

 

[gemma-the-husky]

one way

try having two different queries - one for SAN and one for STO, forget the iif, and just order as appropriate

in the system column put SAN or STO respectively.

Now you see two separate sets of results

to bring these together you can have a third query, called a union query
this has to be in SQL (see query type in the design pane) and will be

select * from stoquery union
select * from sanquery

provisos - the column numbers and types have to be the same for both queries - so as long as jet-seq and clean-seq are compatible you are ok - if not put "dummy" columns in the queries to make it work

secondly - a union query is readonly, so you wont be able to modify anything

--------
alternatively, try your normal query and have an extra column called say sortkey, with a definition

sortkey= iif(system="san",[jet_seq],[clean_seq])

and use this to manage the query. This should work also

 

[esskaykay]

I'll try that tommorrow. I'm a bit leary to modify the code that much because this routine is actually used for numerous other functions (originally written by others). I was hoping I could do it without separate querries but this probably will work. I'll keep you posted...

Thanks again for your time.
SKK

 

[esskaykay]

Ahhh,
I got it working. All I had to do remove the "Order By" from Record Source and add Jet_Seq,Clean_Seq to OrderBy control in the form.

Thanks again...
SKK