Sorting output from a query

rjkincaid

New Member
Joined
Apr 24, 2009
Messages
7
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?
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,971
instead of doing an IF just convert the housenum to 0 using:

NZ([SITUS_HOUSENUMBER]) as HouseNum
 

rjkincaid

New Member
Joined
Apr 24, 2009
Messages
7
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,875
Messages
5,627,407
Members
416,245
Latest member
Xterminat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top