OrderBy Property

[wjoc1]

Hi,

I'm trying to use the OrderBy property for a form based on a table, however I can't seem to get it to work. I need to apply some functions to the fileds I am ordering and I think this is where I'm running into problems. The following will not work; 'Segment' and 'Pole No' are the two fields I am trying to order the records by.

Me.OrderBy = "segmentSort([Segment]), alphaNumericSort([Pole No])"

What am I doing wrong and how can I fix it!

I've also tried to do this using the tables property sheet and just putting
segmentSort([Segment]), alphaNumericSort([Pole No]) into it's OrderBy property. but this doesn't work either.

How do I call these functions correctly?

Thanks,
Liam

 

[AncientOne]

segmentSort([Segment]), alphaNumericSort([Pole No])

Can you explain what segmentSort and alphaNumericSort signify?

I believe you should only use the field names. And have the OrderByOn property set?

 

[wjoc1]

segmentSort and alphaNumericSort are two functions as follows:

The data in the "Segment" field is text and has the following format:

C12/33
C2/4
C23/5/7R
C3/2/1L

If I just sort by segment alphabetically the order is as above whereas I apply the segmentSort function to get them in correct order (in terms of the numbers):

C2/4
C12/33
C3/2/1L
C23/5/7R

Similarly I need the alphaNumericSort for the same reason. The vals for the "Pole No" txtfield can be as follows:

1
1A
11
2
22
23R
3

If I just sort alphabetically I get the sort order as above however I need them in proper numerical order (even though they are txt fields) so I apply the alphaNumericSort function to get the following:

1
1A
2
3
11
22
23R

This is why I need them and I have used them in the Order By part of SQL statements and that works fine e.g.

.....WHERE <some_conditions> ORDER BY segmentSort([Segment]), alphaNumericSort([Pole No]);

So how come I can't use them for the OrderBy property anywhere else? I can't base this form on a query either. It has to be based on a table so I need some way to order these records in the table correctly.

 

[AncientOne]

What is the current recordsource for your form? Can you replace it with an SQL statement?