OrderBy - show NULL value at the end

[tobymom]

Dear Experts,

I have a field which has some NULL values. When I sort this field ASC, (Let's say "fldA"), NULL values show up at the top.

I understand I can make them listed at the bottom by using query - fldA Is Null DESC, fldA ASC.

Is there anyway to do this by using Form's "OrderBy" property?

I tried this VBA but didn't work.

Form.OrderBy = "fldA Is Null DESC, fldA ASC"
Form.OrderbyOn = TRUE

Any advise will be appreciated.

Thank you.

 

[TheAceMan1]

How are ya tobymom . . .

For starters, the [blue]Order By[/BLUE] property doesn't accept a condition [blue]"[purple]fldA Is Null[/purple] DESC, fldA ASC"
[/blue]. It only accepts a [blue]fieldName[/blue] and [blue]ASC or DESC[/blue]. [purple]fldA Is Null[/purple] should be part of criteria used in the query or SQL of the [blue]RecordSource[/blue] of the form.

All you should need for the [blue]Order By[/blue] property is:

[blue]   Me!OrderBy = "fldA DESC"
   Me.OrderByOn = True[/blue]

[blue]Your Thoughts? . . .[/blue]"


See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

What about this ?

Form.OrderBy = "Nz(fldA,Chr(255))"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886