Making null a higher number

[Briansmi1116]

Hello,

I am tring to do a sort in a form where there are numbers from 1 to 10. There is also a lot of nulls in there as well. When I sort them from 1 to 10, the nulls are first. Is there a way to make the nulls come after 10?

Let me know either the code to make it equal like 11 or how to make it show them last.

Thanks,

Brian Smith

[Rabbit]

Nz(Value, ValueIfNull)

So:

Nz(Field1, 99)
 

All nulls will have the value 99.

[Briansmi1116]

After I entered that code, I get a "compile error: Expected:=" Message This is what I put in the code as:

Private Sub Priority_Click()
Nz (Priority,99)
End Sub

Do I need to do something diffrent? Let me know.

Brian Smith

[Rabbit]

You have to use a query to convert and order the records and use that as the record source for the form.

[Briansmi1116]

I guess I'm not sure how I would do that. I made a querry, but I can't get the nulls to come after 10.

[Rabbit]

What's the query you used?

[Briansmi1116]

I made a querry to pull all the information out of the table. I tried the Nz in the criteria, but it doesn't work there. everytime I do a sort, it doesn't show any information in my form or in the querry.

[Rabbit]

No, I meant what's the SQL you used? In query design view, right click the title bar and click SQL View.

[Briansmi1116]

Sorry, I didn't know that.

SELECT Issues.[Issue Index], Issues.[Product Line], Issues.[Product(s)], Issues.Feature, Issues.[Conditions of Occurrence], Issues.Resolution, Issues.[Problem Description], Issues.[Problem nickname], Issues.Importance, Issues.Priority, Issues.Owner, Issues.Status, Issues.[Issue Type], Issues.[Fike part #], Issues.[Product Version(s)], Issues.[Found By], Issues.[Date Found], Issues.[Initial Fike Contact], Issues.[Date Entered], Issues.[Date Started], Issues.[Date Completed], Issues.[Completed By], Issues.[Completion Verified By], Issues.[Product version issue resolved in], Issues.Hyperlink, Issues.Notes
FROM Issues;
 

Priority is the one I need to sort with the nulls after 10.

[Rabbit]

Sorry, I didn't know that.

SELECT Issues.[Issue Index], Issues.[Product Line], Issues.[Product(s)], Issues.Feature, Issues.[Conditions of Occurrence], Issues.Resolution, Issues.[Problem Description], Issues.[Problem nickname], Issues.Importance, Issues.Priority, Issues.Owner, Issues.Status, Issues.[Issue Type], Issues.[Fike part #], Issues.[Product Version(s)], Issues.[Found By], Issues.[Date Found], Issues.[Initial Fike Contact], Issues.[Date Entered], Issues.[Date Started], Issues.[Date Completed], Issues.[Completed By], Issues.[Completion Verified By], Issues.[Product version issue resolved in], Issues.Hyperlink, Issues.Notes
FROM Issues;
 

Priority is the one I need to sort with the nulls after 10.

I thought you said you used Nz? I don't see it in there.

SELECT Issues.[Issue Index], Issues.[Product Line], Issues.[Product(s)], Issues.Feature, Issues.[Conditions of Occurrence], Issues.Resolution, Issues.[Problem Description], Issues.[Problem nickname], Issues.Importance, Issues.Priority, Issues.Owner, Issues.Status, Issues.[Issue Type], Issues.[Fike part #], Issues.[Product Version(s)], Issues.[Found By], Issues.[Date Found], Issues.[Initial Fike Contact], Issues.[Date Entered], Issues.[Date Started], Issues.[Date Completed], Issues.[Completed By], Issues.[Completion Verified By], Issues.[Product version issue resolved in], Issues.Hyperlink, Issues.Notes, Nz([Priority], 99) AS NewPriority
FROM Issues
ORDER BY Nz([Priority], 99);

[Briansmi1116]

I didn't know where to put Nz, and where I put it caused problems so I deleted it. Now that shows the order in the form with 1 then 10 then 2, and s on. I also want it to be ordered this way everytime I open the form. I would like to do the sort either by hitting the sort assending button, or creatting one. Is this possible?

[Rabbit]

The reason it's ordering it 1, 10, 2 ... is because the field is text. If you want to order it 1,2,3, ... then you need to change your field to numeric or use a query to create a numeric column.

Yes, you can create a button that will sort your records. You'll have to use the OrderBy and OrderByOn properties of the form.

[Briansmi1116]

Can you please give me an example of what it would look like in VB?

[Rabbit]

Me.OrderBy = "NewPriority"
Me.OrderByOn = True
 

[Scott Price]

Also, Brian, just for information. The title of the thread makes it appear that you are assuming Null to be a number. Null is not a number! Please read through this article: What is Null? for more information.

Regards,
Scott

[Briansmi1116]

Thanks for the information Scott.

I still can't get it to work. Let me see if I can explain this a little better. My engineer would like to apply some filters to a form. Then he would like to sort the Priority ascending to show 1 first and null after 10. If I do this with a query, the filter of the form changes the query, so I need to be able to do this with a button on the form. I have tried several things including your suggestions, but I am unable to get it to work.

Thanks for bearing with me.

Brian

[Rabbit]

Thanks for the information Scott.

I still can't get it to work. Let me see if I can explain this a little better. My engineer would like to apply some filters to a form. Then he would like to sort the Priority ascending to show 1 first and null after 10. If I do this with a query, the filter of the form changes the query, so I need to be able to do this with a button on the form. I have tried several things including your suggestions, but I am unable to get it to work.

Thanks for bearing with me.

Brian

Post number 14 should work.

[Briansmi1116]

When I put both the query like you mentioned, and put the VB code in, it comes up "Enter Parameter Value" "NewPriority" empty space to enter, ok and cancel. How do I do this?

[Rabbit]

Table1
ValueField; Text

Query1

SELECT Val(Nz(ValueField, 99)) AS NoNulls, Val(ValueField) AS WithNulls
FROM Table1;
 

Report1
Record Source = Query1
but_Sort
but_Unsort

Private Sub but_Sort_Click()
   Me.OrderBy = "NoNulls"
   Me.OrderByOn = True
End Sub
 
Private Sub but_Unsort_Click()
   Me.OrderBy = "WithNulls"
   Me.OrderByOn = True
End Sub
 

[Briansmi1116]

I guess I can't do what I want, or I'm not explaining it good enough. Thanks for your help and patients.

[Rabbit]

What I laid out should work.
I created the table, the query, and the form. Then I applied a filter to the form. And I clicked the button on the form to sort with the nulls at the end.

[Briansmi1116]

OHH! I figured it out what I did wrong. It works great thanks a bunch.

[Rabbit]

Not a problem, good luck.