OrderBy Date and Null dates last

[Danick]

I have a continuous form where I press a button to show Open records and have them sorted by their required date. So I have this:

Me.OrderBy = "RequiredBy ASC"
Me.OrderByOn = True

This works well for records with a required by date, but for records that have no date, they show up first. I would like them to show up last and can't find a way to do this.

Any ideas?

Thanks

 

[vbaInet]

Create another field that you'll use for sorting and using the Nz() function set those null values to a date that's in the very distant future.

 

[Danick]

Create another field that you'll use for sorting and using the Nz() function set those null values to a date that's in the very distant future.

Thanks for the reply but I'm afraid it went way over my head...
Are you suggesting to actually change the null values to date in the distant future?

 

[vbaInet]

Thanks for the reply but I'm afraid it went way over my head...
Are you suggesting to actually change the null values to date in the distant future?

Yes but using the Nz() function:

http://www.techonthenet.com/access/functions/advanced/nz.php

 

[Danick]

Yes but using the Nz() function:

http://www.techonthenet.com/access/functions/advanced/nz.php

Yes but I don't want to change the date to an future date. It needs to be undated. Just show up at the end of the list. Otherwise, I could just force it to be a future date as a default when the record is created.

Is there no other way to make the fields with no dates appear last in an Me.OrderBy property. I saw somewhere the use of a Nulls Last. But it either isn't available in Access 2003 or I just don't know how to code it properly.

 

[vbaInet]

It's an alias field that you create in a query and it doesn't affect the date's value. All you're doing is coalescing Null to a future date. Have you read what's in the link?

 

[Danick]

Yes but I don't want to change the date to an future date. It needs to be undated. Just show up at the end of the list. Otherwise, I could just force it to be a future date as a default when the record is created.

Is there no other way to make the fields with no dates appear last in an Me.OrderBy property. I saw somewhere the use of a Nulls Last. But it either isn't available in Access 2003 or I just don't know how to code it properly.

I didn't read the link because I thought this method was going to try and update my records to some future date.

So using the example in the link, I changed to code to this.

Dim LOption As String
LOption = Nz(RequiredBy, "1/1/2099")
Me.OrderBy = "LOption ASC"
Me.OrderByOn = True

But now I get a dialog box asking for parameter LOption.

 

[pr2-eugin]

Okay do this, go tot the Form in design view. On the property sheet there should be a tab called Data, check what is under the Recordsource field. Paste it here.

 

[vbaInet]

I didn't read the link because I thought this method was going to try and update my records to some future date.

Seriously when we give you any resource (i.e. link, db etc) we're not just posting it for posting sake, there's a reason. If you had read the link and didn't understand what was said within it's easy to ask.

Like I mentioned already, the Nz() function should be used in the Query, not in the Order By property but the underlying query of your form. Then you sort on the field that's created by the Nz.

 

[Danick]

Seriously when we give you any resource (i.e. link, db etc) we're not just posting it for posting sake, there's a reason. If you had read the link and didn't understand what was said within it's easy to ask.

Like I mentioned already, the Nz() function should be used in the Query, not in the Order By property but the underlying query of your form. Then you sort on the field that's created by the Nz.

I didn't read it because I had asked:

Are you suggesting to actually change the null values to date in the distant future?

Which you then answered:

Yes but using the Nz() function

So I didn't go any further. Sorry about that - I misunderstood the concept.

In any case, I managed to get it working by changing the record source to this:

"ORDER BY Nz([TableName].RequiredBy, '99/99/9999') ASC ; "

Not sure if I'm doing it right, but it seems to be doing what I want.

Thanks for all your help.

 

[vbaInet]

Not quite.

What is the Record Source of your form? A query or a table?

 

[Danick]

Not quite.

What is the Record Source of your form? A query or a table?

I'm using an open event which looks something like this:
Me.RecordSource = "SELECT [TableName].Categories, [TableName].Id_Action," & _

and a whole lot more of that and then ends with

"ORDER BY Nz([TableName].RequiredBy, '99/99/9999') ASC ; "

Then I use the event dialog for all the other buttons on the form.

I'm not sure of all the consequences yet for making this change. But since it was a fairly easy one line change, it won't be too bad if I have to go back to the way it was...

 

[vbaInet]

I'm using an open event which looks something like this:
Me.RecordSource = "SELECT [TableName].Categories, [TableName].Id_Action," & _

Full code please. Half the code doesn't help.

And why aren't you setting the Record Source in the property sheet instead?

 

[Danick]

Full code please. Half the code doesn't help.

And why aren't you setting the Record Source in the property sheet instead?

Looks like my victory dance was a bit premature. Still not working correctly.
The property sheet has this code in it.

SELECT [Action Register].Categories, [Action Register].Id_Action,[Action Register].Subject, [Action Register].Issue,[Action Register].ActionPlanID,[Action Register].Open, [Action Register].Assigned, [Action Register].Required,[Action Register].Action, [Action Register].Closed, [Action Register].Cobra,IIf([Closed]>0,'Closed','Open') AS Status,[Action Register].Contact, [Action Register].ModifiedDate, [Action Register].RequiredBy, [tblContacts].ContactName FROM [tblContacts] Right JOIN [Action Register] ON [tblContacts].ContactID = [Action Register].Contact ORDER BY [Action Register].Open DESC;


This works well. But when I use the same code for a sort button, I want the order by to be by the RequiredBy date. I really would have liked to just copy and paste this code into the sort button and just change the Order By so that it sorts the form in ASC but keeps the RequiredBy dates that have no value at the end.

 

[vbaInet]

Open the query and add an extra field as already advised:

RequiredBySort: Nz([TableName].[RequiredBy], #1/12/[COLOR="Blue"]2099[/COLOR]#)

If you live that long

Now use RequiredBySort field in your code.

Nb: I hope your table isn't called TableName?

 

[Danick]

Open the query and add an extra field as already advised:

RequiredBySort: Nz([TableName].[RequiredBy], #1/12/[COLOR="Blue"]2099[/COLOR]#)

If you live that long

Now use RequiredBySort field in your code.

Nb: I hope your table isn't called TableName?

Looks good and actually works when I put it into the property sheet. But I can't seem to make it work in the RecordSource VBA of the sort button.

BTW, the Table is called "Action Register" and not TableName

 

[vbaInet]

Again, when you say something doesn't work, show us what doesn't work, i.e. the code.

Good to know

 

[Danick]

Again, when you say something doesn't work, show us what doesn't work, i.e. the code.

Good to know

Well I added the RequiredBySort to the RecordSource. I used the SQL from the property sheet as the guide after creating the RequiredBySort field. So now I've got this:

Me.RecordSource = "SELECT [Action Register].Categories, [Action Register].Id_Action," & _
"[Action Register].Subject, [Action Register].Issue,[Action Register].ActionPlanID," & _
"[Action Register].Open, [Action Register].Assigned, [Action Register].Required," & _
"[Action Register].Action, [Action Register].Closed, [Action Register].Cobra," & _
"IIf([Closed]>0,'Closed','Open') AS Status," & _
"Nz([Action Register].[RequiredBy],#1/12/2099#) AS RequiredBySort," & _
"[Action Register].Contact, [Action Register].ModifiedDate, [Action Register].RequiredBy, [tblContacts].ContactName " & _
"FROM [tblContacts] Right JOIN [Action Register] ON [tblContacts].ContactID = [Action Register].Contact " & _
"ORDER BY RequiredBySort ASC; "

But I get a parameter value dialog asking for the RequiredBySort Field

Edit: I think I found at least one of the problems in that I forgot to update the form to have a field with the new RequiredBySort.

I'm thinking this is becoming more trouble than it's worth...:banghead:

 

[vbaInet]

But I get a parameter value dialog asking for the RequiredBySort Field

Edit: I think I found at least one of the problems in that I forgot to update the form to have a field with the new RequiredBySort.

There you go. Anytime you get a parameter of such nature the first thing you should think about is whether that field actually exists in the Record Source.

 

[Danick]

There you go. Anytime you get a parameter of such nature the first thing you should think about is whether that field actually exists in the Record Source.

Ya but I still can't get this line to work in the Me.RecordSource = dialog

"Nz([Action register].RequiredBy,#01/12/2099#) AS RequiredBySort," & _

I'm still getting a parameter error for ReguiredBySort

Do you see anything obvious with this code?



Me.RecordSource = "SELECT [Action Register].Categories, [Action Register].Id_Action," & _
"[Action Register].Subject, [Action Register].Issue,[Action Register].ActionPlanID," & _
"[Action Register].Open, [Action Register].Assigned, [Action Register].Required," & _
"[Action Register].Action, [Action Register].Closed, [Action Register].Cobra," & _
"IIf([Closed]>0,'Closed','Open') AS Status," & _
"Nz([Action register].RequiredBy,#01/12/2099#) AS RequiredBySort," & _
"[Action Register].Contact, [Action Register].ModifiedDate, [Action Register].RequiredBy, [tblContacts].ContactName " & _
"FROM [tblContacts] Right JOIN [Action Register] ON [tblContacts].ContactID = [Action Register].Contact " & _
"ORDER BY [Action Register].RequiredBySort ASC; "


I've even tried
"ORDER BY RequiredBySort ASC; "

But that doesn't work either