OrderBy Date and Null dates last

[vbaInet]

I'll ask again, why are you setting the Record Source of the form in code?

 

[Danick]

I'll ask again, why are you setting the Record Source of the form in code?

It's a continuous form with lots of filters and buttons which I use to set the record source for different conditions. This particular one is for showing the records in a RequiredBy sort.

I'm using Allen Browne's method for filtering that I found here:
http://allenbrowne.com/ser-62.html
and here
http://allenbrowne.com/ser-62code.html

It's been rock solid for years. I just wanted to add a small improvement...

 

[vbaInet]

Yes I know Allen Browne's site but I don't think it mentions anywhere to set the Record Source in code plus it's not related to your problem.

So set the Record Source via the property sheet and open up the query to ensure everything is fine, remove the Me.RecordSource code line, save the form and fire it up and you won't have a problem.

 

[Danick]

Yes I know Allen Browne's site but I don't think it mentions anywhere to set the Record Source in code plus it's not related to your problem.

So set the Record Source via the property sheet and open up the query to ensure everything is fine, remove the Me.RecordSource code line, save the form and fire it up and you won't have a problem.

Thanks for your suggestion but I don't want to remove the Me.RecordSource code line as it is working extremely well for all the combination of filters and sorts that the form is doing. The form is quite complicated and so I can't go back after many hours of work to get it working this way.

So if this can't be done with the way the database is now, then I'll just have to live with the null records sorting of the requiredby dates showing up at the top of the list.

Thanks for your help.

 

[vbaInet]

If I thought that it was going to break your db I won't ask you to do it. They both do the exactly the same thing with the exception of one (setting it in code) which sometimes Access finds difficulty resolving the field names whilst the form is loading.

Try it on a copy of your db.

By the way, did you build the SQL in the Query Designer and got it working?

 

[Danick]

If I thought that it was going to break your db I won't ask you to do it. They both do the exactly the same thing with the exception of one (setting it in code) which sometimes Access finds difficulty resolving the field names whilst the form is loading.

Try it on a copy of your db.

By the way, did you build the SQL in the Query Designer and got it working?

Well I did as you suggested and created a copy of the database and got rid of all the Record Source in code and just set the record source in the property sheet instead.

Surprisingly it is working very well. But I don't want to get ahead of myself yet. Still got a lot of testing for all the filters on that form. But so far so good.

Just had to make a small change to your code to include the DateValue as the form was not sorting correctly without it. So I changed it to this:

RequiredBySort: DateValue(Nz([Action Register].[RequiredBy],#1/12/2099#))

Not sure why I didn't initially do it this way. But I've been modifying it for years so it might be a remnant of Access 97. All I remember was that there was a lot of hours trying to get the thing to work. It wouldn't have been possible without these forums and folks like you, Allen, Arvin, Stephen, Ken and whole lot more...

Thanks again for all your help and patience.

 

[vbaInet]

That's strange about Nz() not returning a date. I tested it and it's returning a date type.

Is your RequiredBy field definitely a date/time field? And is Action Register a table?

 

[Danick]

That's strange about Nz() not returning a date. I tested it and it's returning a date type.

Is your RequiredBy field definitely a date/time field? And is Action Register a table?

Yes to both.
In fact, even in the property sheet, looking at the query in datasheet view, the field shows sorting by the first number as a string and not a date.
But it changes to the correct sorting once I added the DateValue.

 

[vbaInet]

Well yes but it's seems the Nz() function is returning a string and not a date type like mentioned. I tested this and it tells me the type returned is a Date.

Can you rustle up a quick test db? The table with just the date field, very few test records and the query. Make sure it's not sorting correctly in this test db as well.

 

[Danick]

Well yes but it's seems the Nz() function is returning a string and not a date type like mentioned. I tested this and it tells me the type returned is a Date.

Can you rustle up a quick test db? The table with just the date field, very few test records and the query. Make sure it's not sorting correctly in this test db as well.

Just made a quick test and found that it does indeed sort correctly without the DateValue code. So looks like there is still something broken somewhere with my other Database. And just when you think all is well...

 

[vbaInet]

Perform a test in the query and tell me what appears:

TypeName(Nz([Action Register].[RequiredBy],#1/12/2099#))

 

[Danick]

Perform a test in the query and tell me what appears:

TypeName(Nz([Action Register].[RequiredBy],#1/12/2099#))

I put it in a field on the property sheet query
It comes back as Date

 

[vbaInet]

Did you try it on the trouble query?

 

[Danick]

Did you try it on the trouble query?

Now that is really strange. I made a test copy of my database and made sure that the problem still existed. Then created another field in the "Action Register" table called SortDate. Unlike the test table on a new test database, this time the code RequiredBySort: (Nz([Action Register].[SortDate],#12/1/2099#)) does not sort by date. I works only after adding DateValue.

I then tried to just import the table and form from the test database into this troubled one and found that it worked fine without the DateValue.

So I guess this means that there is a problem with this RequiredBy field somewhere.

 

[vbaInet]

I need to see a sample db that isn't sorting properly.

 

[Danick]

I need to see a sample db that isn't sorting properly.

OK - I imported just two tables into a new database and made a sortquery that shows the problem. This is the same query that is in the property sheet of the form.

Take a look at the last two fields in the sort query.
RequiredBySort: (Nz([Action Register].[RequiredBy],#1/12/2099#))
and
RequiredBySorta: DateValue(Nz([Action Register].[RequiredBy],#1/12/2099#))

By changing the sort from one to the other, you will see that one with the DateValue sorts correctly while the other does not.

Hopefully you will see this as well and it's not just on my computer.

 

[vbaInet]

Are you using American or UK date format?

 

[Danick]

Are you using American or UK date format?

Both the computer and Database are set up using American date format.

 

[vbaInet]

So the problem is with Nz(), it will always return a String in a query. Even though the TypeName() shows date, the real type is String.

To preserve the date type use IIF() and IsNull() (or Is Null) in place of Nz()

 

[Danick]

So the problem is with Nz(), it will always return a String in a query. Even though the TypeName() shows date, the real type is String.

To preserve the date type use IIF() and IsNull() (or Is Null) in place of Nz()

OK so I changed the NZ to an IIF and came up with this:

RequiredBySortIF: IIf(IsNull([Action Register].[RequiredBy]),#1/12/2099#,[Action Register].[RequiredBy])

Just tried it in the test database and it appears to be working. But the DateValue also worked. Is there a reason why using IIF is better than DateValue?