Order query (1 Viewer)

[mike6271]

I have a listbox on a form which displays properties(realty) that match the CityID of the property applicant.
I also have an areaID within the city.

I want to create a query which will return all property in the city the applicant is interested in but will order by the properties which match AreaID first.

So someone interested in Hampstead, London would see all London properties but Hampstead at the top of the list.

I hope i have made myself clear, any advice or pointers greatly appreciated.

Thank you

 

[vbaInet]

Within your query create a new field that uses the Switch() function that will return 1 for "Hampstead". Then Sort under this new field in Descending order, followed by any other fields you would like to sort on.

 

[mike6271]

Thank you for your reply will try to implement and see how we get on.

Will I be able to use this dynamically? Or will I have to type the switch query for each Area in the database?

Thank you

 

[MSAccessRookie]

As vbaInet suggested, you could add a new field called UserPreference that is based on the result of an IIf() that checks the Record's Selected area and returns a value of 1 if the AreaID matches the User Selected one, or 0 for any other value. If you then sort by all required Fields (perhaps UserPreference, AreaID, StreetName, etc., you can see how flexible this could be.

-- Rookie

 

[vbaInet]

returns a value of 1 if the AreaID matches the User Selected one, or 0 for any other value.

Or better still Null.