Setting Form.OrderBy Property using VBA

[jrollins]

If I set the form's OrderBy property in Access using the following expression, the form sorts properly:

Right(([ProjectNumber]),(Len([ProjectNumber])-1))

When I place the following code into Form_Load, it doesn't sort properly:

Me.OrderBy = "Right(([ProjectNumber]),(Len([ProjectNumber])-1)), Projects.TaskNumber DESC"
Me.OrderByOn = True

I'm guessing it's a syntax issue but I can find no documentation. Can anyone help?

 

[Hasu]

Hi,

I think you don't need to use right. Could you explain me why you need to use:

Right(([ProjectNumber]),(Len([ProjectNumber])-1))

try this one,

Me.OrderBy = "Right(([ProjectNumber]),(Len([ProjectNumber])-1)), Projects.TaskNumber DESC"

Me.OrderByOn = True


Hope this helps...
Hasu
(Trust on someone, someone will trust you!)

 

[jrollins]

I need to strip off the leading character in ProjectNumber before sorting.

It appears that your suggested VBA code is identical to the code I posted ???

 

[Hasu]

Hi,

Sorry! By mistake I pasted wrong code. Then I figure out.

First, change your form record source with below query, because you need formula column to execute in code to get run your order by clause.


Form record source properties:
SELECT *, Right(([ProjectNumber]),(Len([ProjectNumber])-1)) AS PNo FROM Projects

Type below code in form_load event:

Me.OrderBy = "Projects.PNo, Projects.TaskNumber DESC"
Me.OrderByOn = True



Hope this helps...
Hasu
(Trust on someone, someone will trust you!)