Solved

Count Leading Zero

Posted on 2004-10-15
4
443 Views
Last Modified: 2012-06-27
I'm using zip codes to sort and compile information and I have a query where I run an inventory of people in a 3 digit zip code range like this

Left([qryLastMail]![zip],3) AS 3DigZip

The problem I'm having is in the zip codes where there is a leading zero like in MA 01001 the query returns 100 instead of the 010. How can I get it to count the leading zero. The field is already formatted with 5 digits and when I do the query to show all five it returns the numbers with the leading zero just fine.
0
Comment
Question by:athomas79
  • 3
4 Comments
 
LVL 6

Expert Comment

by:mcorrente
ID: 12323106
left(cstr([qrylastmail]![zip],3) as 3digzip
0
 
LVL 6

Accepted Solution

by:
mcorrente earned 500 total points
ID: 12323177
if not, try left(format([qrylastmail]![zip],"00000"), 3) as 3digzip
0
 

Author Comment

by:athomas79
ID: 12323385
The changing it to a string didn't work, but formatting it again in the query before the taking left three digits worked great, Thanks!
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12323406
no problem.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

746 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

10 Experts available now in Live!

Get 1:1 Help Now