Full Version: Using an expression to sort a form
UtterAccess Forums > Microsoft® Access > Access Forms
Relby
I have a form created from a table and would like to sort the form by using a query that uses an expression that combines part of one table field with another table field.
Let me know if this does not make sense.
Any assistance is appreciated
Cheers
Onbypass
Something like
ortField:[Field1name] & [Field2Name]
in the query field presuming both tables are in the query to reference the fields.
HTH
Relby
Thanks Stuart
More like TableField1 = 01.01.02
Table Field2 = 01.01.02.08
Sort Expression = 01010201010208
Note that the query to detemine the sort expression uses multiple tables, not just the underlying table for the form.
I want to display the form in the order of the sort expression and still be able to edit / add records to the table.
Cheers
Rel
Onbypass
OK then I suppose you need to use something like Instr() function to strip out the periods before concanating the 2 fields together, sounds like a job for a user defined function and some sort of loop to go through looking for the "." to strip out.
That do think?
Stuart
Relby
I have nutted out the sort expression using Left, right and mid functions. The trick is.. how can this sort be applyed to the form with out losing the ability to modify the underlying table using the form?
Onbypass
Hi
That about placing a hidden text box on your form and having the control source as your sort expression, then using that to sort records by? I am not sure if this would work and am not at a pc with Access to test it myself, alternately lookup up sorting in vba, I am sure this could be done then place a command button on your form to action the sort, another one to restore normal sort.
HTH
Stuart
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.