Sorting output from a query

[rjkincaid]

I am using Access to analyze data in a very large real estate database. In the reports that I generate, I like to sort the properties by location. The fields I use are:

[ACCOUNT_NUMBER] Key field

[SITUS_STREETNAME] Name of the street, i.e., MAIN ST, UNION AVE, 1st ST, etc. On vacant land properties, this field is most likely blank


[SITUS_STREETDIRECTIONSUFFIX] The direction of the street, i.e. N MAIN vs. S MAIN, etc. On vacant land properties, this field is most likely blank and is also blank if it's not needed

[SITUS_HOUSENUMBER] The number of the building. On vacant land properties, this field is most likely blank. While I am not aware of any house numbers that are alpha-numeric, the host database treats this field as a text field. When conducting sorts based upon this field, Access treats the field as numeric and sorts correctly

What I want to do is also sort based upon the side of the street, even or odd house numbers.

I have written a simple formula in a query (design view):

EVEN_ODD: IIf(IsNull([SITUS_HOUSENUMBER])=0,[SITUS_HOUSENUMBER]/2-Int([SITUS_HOUSENUMBER]/2),-1)

This formula works with absolutely no problem returning a zero (0) for even numbered properties, 0.5 for odd numbered properties and a -1.0 for properties without street addresses.

My basic sort is:

1 - [SITUS_STREETNAME]
2 - [SITUS_STREETDIRECTION SUFFIX]
3 - [SITUS_HOUSENUMBER]

This sort works fine, but when I attempt to add the [EVEN_ODD] into the sort, I receive the following error:

***************
Data type mismatch in criteria expression. (Error 3464)

Applies to: Access 2013 | Office 2013

The criteria expression in a Find method is attempting to compare a field with a value whose data type does not match the field's data type.
***************

I have tried multiple variations of my formula including:
STR_NUM:IIf(IsNull([SITUS_HOUSENUMBER])=0,VAL([SITUS_HOUSENUMBER]),0)
and then using [STR_NUM] for my EVEN_ODD formula and insuring that the returned field is numeric field. Everything continues to work correctly until I attempt to sort.

I have made the sort query independent and link to it from other queries, but I am unable to use the [EVEN_ODD] field in a sort.

What am I missing?

 

[ranman256]

instead of doing an IF just convert the housenum to 0 using:

NZ([SITUS_HOUSENUMBER]) as HouseNum

 

[rjkincaid]

The IF statement was creating the EVEN-ODD sort key based upon the contents of the HOUSENUMER field.
While the NZ() function worked to convert the blank addresses to 0 I still need an IF statement to return the proper sort key.

I was successful up to that point, but when I tried to sort on \the EVEN_ODD field, I now receive an "ODBC Call Failed" message.

Just to check things out, I tried a new direction:

I designed a query that returned only two fields: [ACCOUNT_NUMBER] and [EVEN_ODD] and saved it as qry_EVEN_ODD

EVEN_ODD was an expression that returned a -1 for records without a street address, 0 for those properties with an even address, and a 0.5 for the odd addresses.

The query worked fine and I then linked that query into a larger query. When attempting to sort on the EVEN_ODD field, I recevied the Data type mismatch error.

I then ran the qry_EVEN_ODD query as a MAKE TABLE query and used the new table in my larger query. I was then successful in sorting by the EVEN_ODD field.

That's telling me that my problem is not on how the expressions are written, but rather the link between Access and the ODBC engine controlling the host database.