Home ms Access '16 - Continuous form - OrderBy textbox value created with expression
Reply: 3

ms Access '16 - Continuous form - OrderBy textbox value created with expression

FlooringSales
1#
FlooringSales Published in 2017-12-07 14:31:36Z

I have a form that shows the records from Table-A, it is a continuous form. I have a textbox that uses a DCount expression to count records from Table-B that share the same SOP-Number.

=DCount("*","[Table-B]","SOP = " & [SOP])

This works but I don't know how I would go about sorting that column based of the resulting values. The other columns are sorted thus:

" ORDER BY [FIELD NAME] ASC;"

So without a field I don't know how (or if) I can sort the form. Could I create a RecordSet to store the values then sort by that field (I don't know if this is even possible)

Thank you

Dan


  • Access 2016 (365)
Steve
2#
Steve Reply to 2017-12-07 14:42:35Z

I'm not quite understanding why you won't know what fields you're pulling from Table B. But you can always order by the column number. So if you're looking to order by the first column, just put:

ORDER BY 1 ASC
Andre
3#
Andre Reply to 2017-12-07 15:41:47Z

You can't sort the recordsource but you can sort the form itself:

Me.OrderBy = "NumTasks DESC"
Me.OrderByOn = True

(assuming your textbox with the DCount control source is named NumTasks ).

Jörgen R
4#
Jörgen R Reply to 2017-12-08 12:48:05Z

You could change the datasource to

select ,DCount('''[Table-B],'SOP=' & SOP) from [Table-A] order by DCount('*''[Table-B],'SOP=' & SOP)

This is not a good soulution i Table-A is big.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.298714 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO