OrderBy & Null Field Values

G

Guest

I would like to sort a forms records by LastName, but in those cases where
the LastName field is empty, have those records show up last instead of first
based on the empty field. I have a code set in the on open event as well as
connected to button that switches the sort order to a different field.

Current code is:
If Forms![Main Review Form]![View Type Subform].Form!View = True Then
Me.OrderBy = "[PI LName]"
Me.OrderByOn = True

I have tried using: Me.OrderBy = "IIf(Not Is Null([PI LName]),1,2)"

Any ideas?
 
J

John Vinson

I would like to sort a forms records by LastName, but in those cases where
the LastName field is empty, have those records show up last instead of first
based on the empty field. I have a code set in the on open event as well as
connected to button that switches the sort order to a different field.

Current code is:
If Forms![Main Review Form]![View Type Subform].Form!View = True Then
Me.OrderBy = "[PI LName]"
Me.OrderByOn = True

I have tried using: Me.OrderBy = "IIf(Not Is Null([PI LName]),1,2)"

Any ideas?
Click to expand...

Try

Me.OrderBy = "NZ([PI LName], 'ZZZZZZZZ')"

or

Me.OrderBy = "- IsNull([PI Lname]), [PI Lname]"

John W. Vinson[MVP]
 
G

Guest

Neither seems to have worked. For the value to change to ZZZZ do I have to
add in [PI LName]='zzzzzzz' ? I have done what you have suggested for
a report grouping level and it worked, not sure if having it in a form on
open coding changes how it works or if something else would interfere.

Thanks for your ideas!

John Vinson said:
I would like to sort a forms records by LastName, but in those cases where
the LastName field is empty, have those records show up last instead of first
based on the empty field. I have a code set in the on open event as well as
connected to button that switches the sort order to a different field.

Current code is:
If Forms![Main Review Form]![View Type Subform].Form!View = True Then
Me.OrderBy = "[PI LName]"
Me.OrderByOn = True

I have tried using: Me.OrderBy = "IIf(Not Is Null([PI LName]),1,2)"

Any ideas?
Click to expand...

Try

Me.OrderBy = "NZ([PI LName], 'ZZZZZZZZ')"

or

Me.OrderBy = "- IsNull([PI Lname]), [PI Lname]"

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

Neither seems to have worked.
Click to expand...

What exactly did you do? What "didn't work"? Did you also do what I
neglected to suggest - setting Me.OrderbyOn to True?
For the value to change to ZZZZ do I have to
add in [PI LName]='zzzzzzz' ?
Click to expand...

I did not suggest that you change the value to ZZZZZ. I suggested that
you set the Form's OrderBy property to the NZ() function, so that NULL
values will sort last.
I have done what you have suggested for
a report grouping level and it worked, not sure if having it in a form on
open coding changes how it works or if something else would interfere.
Click to expand...

My guess is that it's the OrderByOn property still being its default
False value, so that the OrderBy property is being ignored. Sorry I
forgot to mention it!

John W. Vinson[MVP]
 
G

Guest

Sorry, I should have given more detail before.Here is the complete coding I
have for each variation as there may be something I have to adjust that you
can see right away...:
ISNULL Scenerio
Private Sub Form_Open(Cancel As Integer)
DoCmd.Restore
If Forms![Main Review Form]![View Type Subform].Form!View = True Then
Forms![Main Review Form]![View Type Subform].Form!View.Caption = "Admin View"
Me.OrderBy = "IsNull([PI LName]), [PI LName]"
Me.OrderByOn = True
Else
If Forms![Main Review Form]![View Type Subform].Form!View = False Then
Forms![Main Review Form]![View Type Subform].Form!View.Caption = "LOI View"
Me.OrderBy = "IsNull([LOI PI LName]), [LOI PI LName]"
Me.OrderByOn = True
End If
End If
End Sub

Nz Scenerio
Private Sub Form_Open(Cancel As Integer)
DoCmd.Restore
If Forms![Main Review Form]![View Type Subform].Form!View = True Then
Forms![Main Review Form]![View Type Subform].Form!View.Caption = "Admin View"
Me.OrderBy = "Nz([PI LName],'ZZZZZZZ')"
Me.OrderByOn = True
Else
If Forms![Main Review Form]![View Type Subform].Form!View = False Then
Forms![Main Review Form]![View Type Subform].Form!View.Caption = "LOI View"
Me.OrderBy = "Nz([LOI PI LName], 'ZZZZZZZ')"
Me.OrderByOn = True
End If
End If
End Sub

Appreciate any ideas! Thanks!

John Vinson said:
Neither seems to have worked.
Click to expand...

What exactly did you do? What "didn't work"? Did you also do what I
neglected to suggest - setting Me.OrderbyOn to True?
For the value to change to ZZZZ do I have to
add in [PI LName]='zzzzzzz' ?
Click to expand...

I did not suggest that you change the value to ZZZZZ. I suggested that
you set the Form's OrderBy property to the NZ() function, so that NULL
values will sort last.
I have done what you have suggested for
a report grouping level and it worked, not sure if having it in a form on
open coding changes how it works or if something else would interfere.
Click to expand...

My guess is that it's the OrderByOn property still being its default
False value, so that the OrderBy property is being ignored. Sorry I
forgot to mention it!

John W. Vinson[MVP]
Click to expand...
 
G

Guest

Just for future questions - I ended up adding 2 fields to the query that the
form was based on - both used only as a sortby field. Field 1 was
AppSortOrder: IIf(IsNull([PI LName]),1,0) and Field 2 was LOISortOrder:
IIf(IsNull([LOI PI LName]),1,0)

Then in the on open event of the main form and the on current event on the
subform (view/tab change button) I added variations of the following code:
If Forms![Main Review Form]![View Type Subform].Form!View = True Then
Forms![Main Review Form]![View Type Subform].Form!View.Caption = "Admin View"
Me.OrderBy = "[AppSortOrder], [PI LName]"
Me.OrderByOn = True
Else
If Forms![Main Review Form]![View Type Subform].Form!View = False Then
Forms![Main Review Form]![View Type Subform].Form!View.Caption = "LOI View"
Me.OrderBy = "[LOISortOrder], [LOI PI LName]"
Me.OrderByOn = True
End If
End If

This worked great and allowed users to switch sort order between the 2
leaders name fields (with null values showing up last) and one tab/view to
the other by clicking a button (subform attached to a single field table).

Vittles said:
Sorry, I should have given more detail before.Here is the complete coding I
have for each variation as there may be something I have to adjust that you
can see right away...:
ISNULL Scenerio
Private Sub Form_Open(Cancel As Integer)
DoCmd.Restore
If Forms![Main Review Form]![View Type Subform].Form!View = True Then
Forms![Main Review Form]![View Type Subform].Form!View.Caption = "Admin View"
Me.OrderBy = "IsNull([PI LName]), [PI LName]"
Me.OrderByOn = True
Else
If Forms![Main Review Form]![View Type Subform].Form!View = False Then
Forms![Main Review Form]![View Type Subform].Form!View.Caption = "LOI View"
Me.OrderBy = "IsNull([LOI PI LName]), [LOI PI LName]"
Me.OrderByOn = True
End If
End If
End Sub

Nz Scenerio
Private Sub Form_Open(Cancel As Integer)
DoCmd.Restore
If Forms![Main Review Form]![View Type Subform].Form!View = True Then
Forms![Main Review Form]![View Type Subform].Form!View.Caption = "Admin View"
Me.OrderBy = "Nz([PI LName],'ZZZZZZZ')"
Me.OrderByOn = True
Else
If Forms![Main Review Form]![View Type Subform].Form!View = False Then
Forms![Main Review Form]![View Type Subform].Form!View.Caption = "LOI View"
Me.OrderBy = "Nz([LOI PI LName], 'ZZZZZZZ')"
Me.OrderByOn = True
End If
End If
End Sub

Appreciate any ideas! Thanks!

John Vinson said:
Neither seems to have worked.
Click to expand...

What exactly did you do? What "didn't work"? Did you also do what I
neglected to suggest - setting Me.OrderbyOn to True?
For the value to change to ZZZZ do I have to
add in [PI LName]='zzzzzzz' ?
Click to expand...

I did not suggest that you change the value to ZZZZZ. I suggested that
you set the Form's OrderBy property to the NZ() function, so that NULL
values will sort last.
I have done what you have suggested for
a report grouping level and it worked, not sure if having it in a form on
open coding changes how it works or if something else would interfere.
Click to expand...

My guess is that it's the OrderByOn property still being its default
False value, so that the OrderBy property is being ignored. Sorry I
forgot to mention it!

John W. Vinson[MVP]
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Form: OrderBy minimum of fields 1
OrderBy Property 2
Sort records in form programmatically 3
OrderBy property 3
Setting OrderBy for a report in VBA 2
OrderBy in Reports does not work 12
Sorting filtered list on form with a Command Button 5
Nz function 1

Top