Sort records in form with null values at end

[Jennifer]

I have Access 2003 and want to sort my form based on a field that has some
null values. The null values need to be at the end as they are people that
are no longer active.

Thanks

 

[Douglas J. Steele]

If you're not already using a query as the form's RecordSource, use one.

In that query, add a computed field Nz([TheFieldInQuestion], "ZZZZZ") or
Nz([TheFieldInQuestion], 99999999)

(which one you use depends on whether the field is text or numeric)

Sort on that computed field rather than on the field itself.

 

[Jennifer]

The form is based on a table. I have the orderby property set with the fields
[DivisionCode] Asc, [DistrictCode] Asc.

I saw in another post the formula

OrderBy: NZ([CaseClosedDate], Date())

but I can't get it to work.

I have the onload property set to true. Just can't get the null values at
the end.

If you're not already using a query as the form's RecordSource, use one.

In that query, add a computed field Nz([TheFieldInQuestion], "ZZZZZ") or
Nz([TheFieldInQuestion], 99999999)

(which one you use depends on whether the field is text or numeric)

Sort on that computed field rather than on the field itself.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have Access 2003 and want to sort my form based on a field that has some
null values. The null values need to be at the end as they are people
that
are no longer active.

Thanks

 

[Douglas J. Steele]

Rather than using the OrderBy property, try setting the sort order in the
query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The form is based on a table. I have the orderby property set with the
fields
[DivisionCode] Asc, [DistrictCode] Asc.

I saw in another post the formula

OrderBy: NZ([CaseClosedDate], Date())

but I can't get it to work.

I have the onload property set to true. Just can't get the null values at
the end.

If you're not already using a query as the form's RecordSource, use one.

In that query, add a computed field Nz([TheFieldInQuestion], "ZZZZZ") or
Nz([TheFieldInQuestion], 99999999)

(which one you use depends on whether the field is text or numeric)

Sort on that computed field rather than on the field itself.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have Access 2003 and want to sort my form based on a field that has
some
null values. The null values need to be at the end as they are people
that
are no longer active.

Thanks

 

[Jennifer]

The form is based on a table.

Rather than using the OrderBy property, try setting the sort order in the
query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The form is based on a table. I have the orderby property set with the
fields
[DivisionCode] Asc, [DistrictCode] Asc.

I saw in another post the formula

OrderBy: NZ([CaseClosedDate], Date())

but I can't get it to work.

I have the onload property set to true. Just can't get the null values at
the end.

If you're not already using a query as the form's RecordSource, use one.

In that query, add a computed field Nz([TheFieldInQuestion], "ZZZZZ") or
Nz([TheFieldInQuestion], 99999999)

(which one you use depends on whether the field is text or numeric)

Sort on that computed field rather than on the field itself.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have Access 2003 and want to sort my form based on a field that has
some
null values. The null values need to be at the end as they are people
that
are no longer active.

Thanks

 

[Jennifer]

I set a text box with

=IIf(IsNull([DivisionCode]),2,1)

and can sort on the text box and the null values are at the end.

But I also need to sort by 2 other fields from the table - Division and
District.

How can I combine the text box and fields in the "OrderBy" property? I've
tried separating by commas and & but it doesn't work.

The form is based on a table.

Rather than using the OrderBy property, try setting the sort order in the
query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The form is based on a table. I have the orderby property set with the
fields
[DivisionCode] Asc, [DistrictCode] Asc.

I saw in another post the formula

OrderBy: NZ([CaseClosedDate], Date())

but I can't get it to work.

I have the onload property set to true. Just can't get the null values at
the end.


:

If you're not already using a query as the form's RecordSource, use one.

In that query, add a computed field Nz([TheFieldInQuestion], "ZZZZZ") or
Nz([TheFieldInQuestion], 99999999)

(which one you use depends on whether the field is text or numeric)

Sort on that computed field rather than on the field itself.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have Access 2003 and want to sort my form based on a field that has
some
null values. The null values need to be at the end as they are people
that
are no longer active.

Thanks

 

[Douglas J. Steele]

As I said in my first post, "If you're not already using a query as the
form's RecordSource, use one."

Create a query that returns all of the fields you need in the order you
want.

Go into the form and change its RecordSource from the table to the name of
the query you just created.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I set a text box with

=IIf(IsNull([DivisionCode]),2,1)

and can sort on the text box and the null values are at the end.

But I also need to sort by 2 other fields from the table - Division and
District.

How can I combine the text box and fields in the "OrderBy" property? I've
tried separating by commas and & but it doesn't work.

The form is based on a table.

Rather than using the OrderBy property, try setting the sort order in
the
query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The form is based on a table. I have the orderby property set with
the
fields
[DivisionCode] Asc, [DistrictCode] Asc.

I saw in another post the formula

OrderBy: NZ([CaseClosedDate], Date())

but I can't get it to work.

I have the onload property set to true. Just can't get the null
values at
the end.


:

If you're not already using a query as the form's RecordSource, use
one.

In that query, add a computed field Nz([TheFieldInQuestion],
"ZZZZZ") or
Nz([TheFieldInQuestion], 99999999)

(which one you use depends on whether the field is text or numeric)

Sort on that computed field rather than on the field itself.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have Access 2003 and want to sort my form based on a field that
has
some
null values. The null values need to be at the end as they are
people
that
are no longer active.

Thanks

 

[Jennifer]

How do I add a record from the form and update the main table?

As I said in my first post, "If you're not already using a query as the
form's RecordSource, use one."

Create a query that returns all of the fields you need in the order you
want.

Go into the form and change its RecordSource from the table to the name of
the query you just created.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I set a text box with

=IIf(IsNull([DivisionCode]),2,1)

and can sort on the text box and the null values are at the end.

But I also need to sort by 2 other fields from the table - Division and
District.

How can I combine the text box and fields in the "OrderBy" property? I've
tried separating by commas and & but it doesn't work.

The form is based on a table.

:

Rather than using the OrderBy property, try setting the sort order in
the
query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The form is based on a table. I have the orderby property set with
the
fields
[DivisionCode] Asc, [DistrictCode] Asc.

I saw in another post the formula

OrderBy: NZ([CaseClosedDate], Date())

but I can't get it to work.

I have the onload property set to true. Just can't get the null
values at
the end.


:

If you're not already using a query as the form's RecordSource, use
one.

In that query, add a computed field Nz([TheFieldInQuestion],
"ZZZZZ") or
Nz([TheFieldInQuestion], 99999999)

(which one you use depends on whether the field is text or numeric)

Sort on that computed field rather than on the field itself.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have Access 2003 and want to sort my form based on a field that
has
some
null values. The null values need to be at the end as they are
people
that
are no longer active.

Thanks

 

[Bob Quintal]

How do I add a record from the form and update the main table?

You don't have to do anything. A simple SELECT query based on a single
table will automatically pass the updates to the table.

It won't work on a Totals query, or a Union query or a complex query
with 3 or more tables.

However, in your case, it will work. Try it!!