Access Report Completely Disregards IsNull in Order By

[D Witherspoon]

If I set OrderByOn = Yes
And set Order By = GaugeNumber, IsNull([LogDate]) DESC , LogDate, DueDate

The report ignored the IsNull portion of the order by.

When I have this in the query the report is based off of it works fine.
Basically I want the date field sorted from lowest to most recent and then
nulls to appear after the most recent.

If I define a field that has the IsNull in the query and filter on that
field in the report instead of on IsNull it's no different. Or if I leave
filtering off in the report and use the query it ignores the IsNull portion
of the Order By.

Also I do not have any sorting specified in the Grouping and Sorting section
of the report.

What is going on here? Is it something I am not doing right, or is MS
Access one pile of complete garbage!

 

[Tom Ellison]

Dear D:

I recommend Nz(LogDate, #12/31/2999#).

Tom Ellison

 

[D Witherspoon]

Before you responded I ended up doing a similar approach. In my report I
then look for this arbitrary date and replace it with what I need. An
advantage of this method is it makes sorting easier as I can sort ASC or
DESC without having to use two sort fields. This makes it simple for users
for sorting.

Also.. Thanks for the NZ reference. I didn;'t know such a function existed
in Acess and it may become useful from here on in. Looks similar to the
IsNull function in SQL Server.

I still this it's retarded that MS Access doesn;t let it work as what would
have been expected.

Dear D:

I recommend Nz(LogDate, #12/31/2999#).

Tom Ellison

If I set OrderByOn = Yes
And set Order By = GaugeNumber, IsNull([LogDate]) DESC , LogDate, DueDate

The report ignored the IsNull portion of the order by.

When I have this in the query the report is based off of it works fine.
Basically I want the date field sorted from lowest to most recent and
then nulls to appear after the most recent.

If I define a field that has the IsNull in the query and filter on that
field in the report instead of on IsNull it's no different. Or if I
leave filtering off in the report and use the query it ignores the IsNull
portion of the Order By.

Also I do not have any sorting specified in the Grouping and Sorting
section of the report.

What is going on here? Is it something I am not doing right, or is MS
Access one pile of complete garbage!

 

[Tom Ellison]

Dear D:

I'm not sure what you expected. IsNull(LogDate) would return true/false,
with all the false values (0, not null) coming after the true values (-1)
because you sorted DESC. Are you saying it didn't work correctly on this
basis?

Tom Ellison

Before you responded I ended up doing a similar approach. In my report I
then look for this arbitrary date and replace it with what I need. An
advantage of this method is it makes sorting easier as I can sort ASC or
DESC without having to use two sort fields. This makes it simple for
users for sorting.

Also.. Thanks for the NZ reference. I didn;'t know such a function
existed in Acess and it may become useful from here on in. Looks similar
to the IsNull function in SQL Server.

I still this it's retarded that MS Access doesn;t let it work as what
would have been expected.

Dear D:

I recommend Nz(LogDate, #12/31/2999#).

Tom Ellison

If I set OrderByOn = Yes
And set Order By = GaugeNumber, IsNull([LogDate]) DESC , LogDate,
DueDate

The report ignored the IsNull portion of the order by.

When I have this in the query the report is based off of it works fine.
Basically I want the date field sorted from lowest to most recent and
then nulls to appear after the most recent.

If I define a field that has the IsNull in the query and filter on that
field in the report instead of on IsNull it's no different. Or if I
leave filtering off in the report and use the query it ignores the
IsNull portion of the Order By.

Also I do not have any sorting specified in the Grouping and Sorting
section of the report.

What is going on here? Is it something I am not doing right, or is MS
Access one pile of complete garbage!

 

[D Witherspoon]

it didn't work on that basis no.

it didn;t matter if i was sorting ASC or DESC..

As i said the same order by statement worked as expected in the query
but the same order by statement put in the orderby property put the null
dates at the top everytime (which was not the same behaviourin the filter in
the query where the null dates would appear at the bottom, but the dates
would still flow from earliest to latest)

Dear D:

I'm not sure what you expected. IsNull(LogDate) would return true/false,
with all the false values (0, not null) coming after the true values (-1)
because you sorted DESC. Are you saying it didn't work correctly on this
basis?

Tom Ellison

Before you responded I ended up doing a similar approach. In my report I
then look for this arbitrary date and replace it with what I need. An
advantage of this method is it makes sorting easier as I can sort ASC or
DESC without having to use two sort fields. This makes it simple for
users for sorting.

Also.. Thanks for the NZ reference. I didn;'t know such a function
existed in Acess and it may become useful from here on in. Looks similar
to the IsNull function in SQL Server.

I still this it's retarded that MS Access doesn;t let it work as what
would have been expected.

Dear D:

I recommend Nz(LogDate, #12/31/2999#).

Tom Ellison


If I set OrderByOn = Yes
And set Order By = GaugeNumber, IsNull([LogDate]) DESC , LogDate,
DueDate

The report ignored the IsNull portion of the order by.

When I have this in the query the report is based off of it works fine.
Basically I want the date field sorted from lowest to most recent and
then nulls to appear after the most recent.

If I define a field that has the IsNull in the query and filter on that
field in the report instead of on IsNull it's no different. Or if I
leave filtering off in the report and use the query it ignores the
IsNull portion of the Order By.

Also I do not have any sorting specified in the Grouping and Sorting
section of the report.

What is going on here? Is it something I am not doing right, or is MS
Access one pile of complete garbage!

 

[david epsom dot com dot au]

:~) Yes, reports ignore the query sort order.

Reports have their own sort order. They actually
do this by re-building the underlying query.

But it also means that if you put no sort order on
the report, it rebuilds the query to have no sort
order.

You want to look at the 'sorting and grouping' properties
of the report.

You can set a multi-field sort order either by setting
a series of sort fields on the 'sorting and grouping'
form, or by setting one calculated field to sort by.

(Since you want a mixture of ascending and descending,
you would need at least two calculated fields)

We use calculated fields sometimes when we are doing
dynamic changes to the report sort order: it is easier
than dynamically changing the number of sort fields.

Also, I think you might have confused the issue by
asking here in .queries :~)

(david)