Sort records in form with null values at end

  • Thread starter Thread starter Jennifer
  • Start date Start date
J

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
 
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.
 
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.


Douglas J. Steele said:
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!)


Jennifer said:
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
Click to expand...
Click to expand...
 
Rather than using the OrderBy property, try setting the sort order in the
query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jennifer said:
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.


Douglas J. Steele said:
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!)


Jennifer said:
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
Click to expand...
Click to expand...
Click to expand...
 
The form is based on a table.

Douglas J. Steele said:
Rather than using the OrderBy property, try setting the sort order in the
query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jennifer said:
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.


Douglas J. Steele said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.

Jennifer said:
The form is based on a table.

Douglas J. Steele said:
Rather than using the OrderBy property, try setting the sort order in the
query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jennifer said:
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
Click to expand...
Click to expand...
Click to expand...
 
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!)


Jennifer said:
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.

Jennifer said:
The form is based on a table.

Douglas J. Steele said:
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
Click to expand...
Click to expand...
Click to expand...
 
How do I add a record from the form and update the main table?


Douglas J. Steele said:
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!)


Jennifer said:
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.

Jennifer said:
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
Click to expand...
Click to expand...
Click to expand...
 
How do I add a record from the form and update the main table?
Click to expand...
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!!
 
Back
Top