Solved

sort by last five digits in query field

Posted on 2013-01-06
2
574 Views
Last Modified: 2013-01-07
MS Access 2007: i have a table I'm querying from a exported xls file that has a nasty field with the city,state (2 digit) and the zip code all combined. I need to sort on the zip code which is the last five digits of the field but in some cases, there is no zip code in some of the records. If it helps any, all of the records use the 2 digit state of Tx followed by the five digit zip code (for example: Dallas,Tx 12345), if available. I need to do this in the query or the applicable report which I derive from this query.
0
Comment
Question by:howcheat
2 Comments
 
LVL 10

Accepted Solution

by:
etech0 earned 500 total points
ID: 38749646
Add another field to the query, like this:

zip: Right([FieldContainingCityStateZip],5)

In the criteria row, put this:

<"a"

That will filter it so you only see the numeric ones.

Then, sort that field. You can uncheck Show if you don't want to see it.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38749657
add another column to your query with something like this

right([NameOfField],5)


ascending


-------------------------

to handle the records with no zip code, you can do this

iif(isnumeric(right([nameofField],5)), right([nameofField],5),"99999")
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now