Sort order with null values

[orange]

I fully underestimated the complexity of Access and the amount of programing that goes into a database.

I would suggest it is NOT the amount of programming, but
the importance of understanding your data And


getting your Tables and Relationships set up to support your business is the big issue, often underestimated, and often recognized too late.

People tend to ignore Database Design as a key first step.

[Alsail77]

June7,

Still a little confused on how to implement the sort order so that null values will come last. If I understand your suggestion, this would cause null values to be first on a descending sort order right? Is it possible to have null always last? Also, if I am implementing this code in the query, does this still allow me to set replacements for null for multiple fields and sort by the various fields no demand (it appears this might be yes based on your last post but want to be sure).

I have attached my DB file if needed, currently populated with a few BS records. (I had to use a zip file to upload it).

The biggest problem that I must solve is the failure of the NextMaturity field to at all. Then the Null values sort last (at least for ascending if not feasible for both) and getting the one button to sort ascending and descending (currently works when no null values exist.

Thanks,
Alex
Database 8-24.zip

[June7]

You understand correct. Doesn't matter if you leave the value as null or substitute another value with expression, the rules of sort order will be complied with. You just have to decide which you want to live with.

Yes, you can substitute nulls in each field and use those constructed fields for sorting.

[Alsail77]

Sorry, I am still highly confused as to where/how to implement this. Am I looking in the SQL view of the query or the VBA code for the formwhere the On click event code sits to sort the order of the form whose record source is the query? I believe I will need to give the NullCheck fields an alias as the sorting I am doing takes place using click events on the Form but this only adds to the confusion.

Is there any chance you could provide more detailed instructions on implementing this? Please keep in mind I know very little about the technical names associated with coding. If I am going into the SQL of the query, please be clear on where in that code stuff should be added.

am also still having a lot of trouble with the NextMaturity field not sorting into any logical fashion. I have been led to believe that Access is not viewing this properly as a date, since it is a calcualted field in a table, but I can't determine how to implement CDate() in the query (or if I need to do so elsewhere). At this point I don't care as much about the ability to sort descending with the same button (only my text fields will do this properly) but must have all fields be able to sort ascending when the Form's column label is double clicked (NextMaturity goes into an apparent random order)

Thanks again,
Alex

[June7]

Create fields in query designer with expressions on the Field row. Review
http://office.microsoft.com/en-us/ac...005188023.aspx
http://allenbrowne.com/casu-14.html
These constructed fields can now be used like any normal field. Use those fields in sorting. The query can be RecordSource for form or report.

I really don't know how to explain it any better.

Your db doesn't show any attempt to use my suggestion.

[Alsail77]

So I managed to solve several of my problems (Still one work out at the bottom. I actually managed to solve two at once so I can't be 100% sure what step fixed which problem. I deleted all spaces (" ") in field names. I think this did most of the job of getting fields to sort ascending and descending using one button but the next step could have played a part. I am also quite sure that this fixed the issue with my "NextMaturity" field not sorting.

To get null values to always sort last, I created a new field in my table for each field in my query that can have a Null value. I set this field as a calculated field IIF(isNull(FieldName),1,0). Then, in the VBA Code of the form, I used:

Private Sub TotalDebt_Label_DblClick(Cancel As Integer)
Me.OrderByOn = True
If Me.OrderBy = "TD_IsNull, TotalDebt" Then
Me.OrderBy = "TotalDebt" & " DESC"
Else
Me.OrderBy = "TD_IsNull, TotalDebt"
End If
End Sub

Still not sure what is causing a calculated text field to have a leading space (for deatil see post above from 8/23 @ 11:01). If I figure it out I will post.

[June7]

Spaces in field names has no bearing on the sorting of data within fields.

So you are doing in a calculated field what I suggested be done in a query.

Could have just said 'Post #7'.

The leading space in the calculated field is because the expression is including a space to separate StreetNumber and StreetName. If there is no StreetNumber then the concatenated space is the first character. Access will drop trailing spaces but not leading spaces. Use Trim function.

[Alsail77]

I, obviously, don't know as much as I should about Access, but deleting the spaces in field names and adding the above mentioned fields and code casued the reverse sort order to function (the If me.orderby Like ... Then now works). If there is a formating difference between how you must write a field name with spaces vs. without then that would be why this worked for me.

Fixed the leading space by adding Trim$() to the expression (Trim$(Str$([StreetNumber])+" "+[StreetName])). Apparently, Str$() includes a leading space so Str$(123)=" 123".

Yes, I decided to use the calculated field because I found it easier to implement. With no knowlege of the proper terminology associated with queries, and no knowledge/ability to work in the SQL view, I was unable, with the time I have, to figure it out.

Thanks for all your help,
Alex

(I have another, hopefully simple (for me), question but I am posting it as a new thread in Queries)

[June7]

Numbers have a leading space to allow for negative (-) sign. Apparently the Str$ function retains this space when converting positive number. Didn't you say street number was a number type field?

Names with spaces, special characters, punctuation (underscore is exception), or names that are reserved words must be enclosed with []. There are times when any name must have [], but there are times the [] can be omitted and code will still work if those other conditions are not involved.