Sort order with null values

[Alsail77]

I have built an access database from scratch having never used Access before and need a little help putting the final pieces together



I have a Form which presents the results from a query. Upon clicking the header of each column of data the records are sorted A-Z and Z-A using the code below. This works fine when all records have data in the relevant field but not when several records have a value of null. It appears that when null values are present the "If Me.OrderBy = "property" & " DESC"" always evaluates as not true, even though all the null values are on top and all the filled in values are in appropriate order.


A second issue is one of my fields is not sorting properly at all. I have two date fields, one sorts the other does not. The one that sorts is a user entered date (8/22/2012) which defaults to "9/9/9999" if the user does not enter a different value. The other date field which does not sort properly at all is a calculated date field which chooses the earlier of two possible dates (both associated with the same record) using IIf([Senior Mortgage Maturity]>[Mezz Maturity],[Mezz Maturity],[Senior Mortgage Maturity])


This code is repeated for each of the six fields with the relevant field name in place of 'property'.

Private Sub Property_Label_DblClick(Cancel As Integer)
Me.OrderByOn = True
If Me.OrderBy Like "Property" Then
Me.OrderBy = "property" & " DESC"
Else
Me.OrderBy = "property"
End If
End Sub

[June7]

Since you are not using wildcard, might as well use = operator instead of Like. Try:

If Me.OrderBy & "" = "Property" Then

Is the result of the IIf aligned left or right? If left then the result is a text string, not date. Use CDate function to make sure the result is a date value.

[Alsail77]

Unfortunately neither idea seems to work. Not exactly sure what you mean by using "wildcard". I do use the Like "*" in the criteria for the query which populates the form in question.

using '& "" =' instead of 'Like' makes no difference. The null values still always appear on top with the filled in records in accending order. If it makes a difference I am testing this on a currency field.

For the calculated date field - when looking at the table containing all the data, the calculated date field is told to be of the short date type and is justified right. The Cdate() function tells me it can't be used in a calcualted field.

A third issue I have noticed is with another calcluated text field. This field uses a compound IIF statement to determine the condition of two other fields, one is type number (long integer) with a default value of ="999" and the other is of type Text with a default value of "Fake St." The calculation outputs the the contents of both as a single string using (Str$([StreetNumber])+" "+[StreetName]). For an unknown reason the calculated text field adds a leading " " which I would also like to prevent since this is the default value of another field if the user does not provide another value.

Thanks,
Alex

[orange]

Show some of the code that is giving you these errors.

, one is type number (long integer) with a default value of ="999"

A long integer field will NOT default to "999". No quotes for Long integer values.

[Alsail77]

Orange,

the default value for the long integer was previously 999. I added the quotes to see if that would make a diffence and it had no effect. Either way, the issue with the leading " " does not appear to be with the long integer field as no space leads the number when it is viewed in its own field. I have simplified everything by simply requiring values for Street Number and Street Name which allowed me to do away with the IIF() and simply set the [Address] to Str$([StreetNumber])+" "+[StreetName] (this is the only code I know of associated with the generation of [Address]). The leading " " only shows up in the [Address] field and the [Property] field which defaults to [Address] if nothing is entered by the user.

The code for the issues associated with sorting the data is in my original post and below. I have tried 'If Me.OrderBy & "" = "Property" Then, as suggested by June7, but this had no effect on the results. The IF allways appears to evaluate as False.

This code is repeated for each of the six fields with the relevant field name in place of 'property'.

Private Sub Property_Label_DblClick(Cancel As Integer)
Me.OrderByOn = True
If Me.OrderBy Like "Property" Then
Me.OrderBy = "property" & " DESC"
Else
Me.OrderBy = "property"
End If
End Sub

For the Date field which does not sort at all: This is calculated by IIf([Senior Mortgage Maturity]>[Mezz Maturity],[Mezz Maturity],[Senior Mortgage Maturity]) and is set to Result Type = Date/Time and Format Short Date in the table.

If you need to see other code, let me know, but I can't think of anything that should have an effect.

[orange]

We can only work from what you show us or write to us.
We would like to help but you're showing code for one event and referring to Iif statements that we know nothing about.

You might wish to make an mdb copy of your database and post it for review.

[June7]

Agree with Orange, not following your descriptions of issue. Would be best if we could analyze the db.

[Alsail77]

Sorry, I think I am overcomplicating this by trying to talk about three issues together. Also, I am likely failing to use to proper naming conventions since I am literally teaching myself how to do everything as I do it. Let me try to break this one more time and if there is still issues I will try and provide a copy of the database. Not exactly sure how to create a mdb file though.

Issue 1: An leading space is added to a calculated text field.

Associated fields (all from one table):
StreetNumber: Data type = Number - Long Integer, default value = 999
StreetName: Data type = Text, default value = "Fake St."
Address: Data type = Calculated, expression = Str$([StreetNumber])+" "+[StreetName], Result Type = Text

The address field adds a space before the street number. As a result the default value becomes " 999 Fake St." instead of "999 Fake St." This case holds true even if the user enters a different street number and street name. This problem was noticed because I also have a field called Property set to equal address if no other property name is entered. The space can be deleted from the property field but, because Address is calculated, I can not delete it from the Address field. The below VBA code is used upon command button click to check if Property should be set to Address.

If Me.Property = "" Then
Me.Property = Address
End If


Problem 2a: Sort ascending and descending using one "button" on a Form.

I have a form "PropQuery" This form is a continuous form whose Record Source is a query. It returns all the records based on a search. Upon clicking the label above each column of data the records sort. If they are already sorted ascending I want them to sort descending.

The code I have found to do this is below and appears to work if all records have data for the field I am trying to sort by (I have six options). The problem appears to be that when null values are present for the field I am sorting by, the program will not switch and sort descending. The "If Me.OrderBy Like "Property" Then" appears to never evaluate as true when null values are present.

This code is adjusted for each of the six fields I can sort by with all instances of Property being changed to the appropiate field name.
Private Sub Property_Label_DblClick(Cancel As Integer)
Me.OrderByOn = True
If Me.OrderBy Like "Property" Then
Me.OrderBy = "property" & " DESC"
Else
Me.OrderBy = "property"
End If
End Sub

2B: All the above Form and Sort By information from 2A holds true here. One of my fields, Next Maturity, will not sort properly at all. This is a calcualted field which chooses the earlier of two date fields.

Associated fields:
Next Maturity: type = Calculated, expression = IIf([Senior Mortgage Maturity]>[Mezz Maturity],[Mezz Maturity],[Senior Mortgage Maturity]), Result type = Date/Time and Formate = Short Date
[Senior Mortgage Maturity] and [Mezz Maturity] = Both are of type Date/Time & Format = Short Date. Default value is set to #9/9/9999# (Access added the # when in design view for the table).

When clicked this column will "sort" but there is no logic to the order.

2C: It would be nice if, when sorting null values are always presented last although I have not tried to do this myself yet as I am trying to fix the other sort features first.

[orange]

Perhaps you are over-complicating things. For example, why did you decide to use

StreetNumber: Data type = Number - Long Integer, default value = 999

?

Numbers are things we add, subtract, multiply etc. But a street number is not one of those things. You would not say let's divide the street number of a house/address by 7.
Street number for those of us who have developed some naming consistency and databases, would be generally referred to as an Identifier. Some set of characters that "identify" something from other similar things.
Property has a special meaning in Access, and could get confusing when you use that term as a field ina table --NOT wrong, but potentially very confusing.

Why do you have NULL values? What do they mean to you and your application?

It is also recommended that you adopt a Naming convention that does not have spaces and special characters in field and object names. Use alphanumerics and "_" and you will save yourself from a lot of hurt and frustration.

I think you're going to have to post a database (preferably mdb), if your issues are going to be resolved.

[June7]

If you sort ASCENDING, nulls will be at top, if you sort DESCENDING, nulls will be at bottom. In other words, nulls are always first, then empty strings (I don't allow empty strings in tables), then punctuation/special characters, then numbers, then alphas.

You can provide a substitute for the null in a query by expression and then use that constructed field for sorting. Example with text type field:

SELECT *, Nz(somefield,"ZZZZZ") As SortField FROM tablename ORDER BY Nz(somefield,"ZZZZZ");

Agree with orange, avoid spaces, punctuation (underscore is exception) or special characters in names as well as reserved words as names. If used, enclose in []. Sometime Access will provide the [] but not always and VBA never will.

[Alsail77]

I am using a number field for the "Street Number" because it seemed logical when I started this to have a field that I only wanted to contain digits be a number field. I was not thinking in math terms but simply in visual representation terms. Not knowing Access at all when I started this seemed logical.

Since I started this I found the potential for issue with naming fields with spaces. By that time I was afraid to start changing everything because I did not know if everything would auto-update to the new field names. Fields since added have not included spaces.

Null values simply mean the user did not fill in the information for the field, either because the value is unknown or not currently relevent. Basically, my database tracks mortgage information for real estate. A property may or may not have a mortgage on it. If it does not, then the mortgage fields need to be blank. If a mortgage is added, these fields will be filled in.

I looked up how to save as a mdb file and it appears I Save As, Access 2002-2003 Database (if this is wrong let me know). If this is the way, I am unable to save as an mdb file, I presume, based on what I read, in part beacue I have calculated fields in the table.

[June7]

Okay, but I can view Access 2010 files.

I added some more comments about naming convention.

Renaming fields will carry through to queries, except where used in expression.

I use Rick Fisher's Find and Replace when global design changes needed due to renaming something. It has also been helpful to track down anomalies in design. Costs about $50.

Suggestion about null substitute helpful?

[Alsail77]

June7,

Your sort order explanation has made me wonder if if the values are truly NULL or simply "empty strings". I have also realized that what I have been calling Null values really only exist in for fields that at values of $, %, or dates (so I am guessing they can't be empty strings since they are not of type text). I assume that in your code (SELECT *, Nz(somefield,"ZZZZZ") As SortField FROM tablename ORDER BY Nz(somefield,"ZZZZZ") the "ZZZZ" would be replaced by "9999"

I have looked at you provided for the null substitue and am confused by it, although I have not tried to figure it out or implement it yet, that is a job for tomorrow at this point. Not exactly sure where to "query by expression" either. Is this simply using the criteria field when viewing the criteria, added to the text in the SQL view, or somehow to the VBA code? If its to the SQL I think I will need some more help as I have no idea how to use it. I made one change to it and, while it appears to work, I realize now it may be causing my sort order problem with the Date that is not sorting.

MY SQL code is below. I used the design view to get to get all the black text to generate and added the red text myself (although Access added the ([PropData].[NextMaturity]) itself. This caused a column to appear in design view with the field name as the red text and <>False as the criteria

SELECT PropData.Property, PropData.Address, PropData.[Senior Mortgage All-in Rate], PropData.[Senior Mortgage Maturity], PropData.[TOTAL Outstanding Loans], PropData.StreetNumber, PropData.StreetName, PropData.NextMaturity, PropData.Owner
FROM PropData
WHERE (((PropData.Property) Like "*" & [Forms]![Main].[PropSearch] & "*") AND ((PropData.Address) Like "*" & [Forms]![Main].[AddressSearch] & "*") AND ((PropData.Owner) Like "*" & [Forms]![Main].[OwnerSearch] & "*") AND ((IIf([Forms]![Main]![Check40]=-1,(([PropData].[NextMaturity]) Between [Forms]![Main].[DateMin] And [Forms]![Main].[DateMAx]),True))<>False))
ORDER BY PropData.Property, PropData.Address, PropData.NextMaturity;



Sorry for all the confusion I am causing by not knowing all the terminology,
Alex

[Alsail77]

Also, I will try and provide the database file tomorrow morning. I just want to clean up the layout of the forms. I have added some new fields since they were created.

Thanks again, everyone, for helping me finish this thing. I fully underestimated the complexity of Access and the amount of programing that goes into a database.

[June7]

Yes, only text type field can have empty (zero length) string. However, that is option I don't allow. So, in my tables, no matter what type field, if no data entered the field is Null.

My suggestion is to use an expression in the ORDER BY clause of a query to sort records so that the Nulls (via the substitute value) will always be at the end for ASCENDING order - not 'query by expression' (that phrase is out of context of my original statement, which, sorry, may be poorly worded). The expression can be given an alias name so that it will show in the query just like any native field and be available in subsequent queries or forms or reports or it can exist only in the ORDER BY clause, not visible in the Datasheet view.

Number fields would have a huge number (without quote marks) in place of the Zs. Date field would have a date into the far future: Nz(fieldname,#12/31/2900#). Not sure about Yes/No type. Normally yes/no would have only one of two values (-1,0), however, triple state is possible with Yes/No type field but I have never applied it.