Count Leading Zero

Posted on 2004-10-15
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.
Question by:athomas79
  • 3

Expert Comment

ID: 12323106
left(cstr([qrylastmail]![zip],3) as 3digzip

Accepted Solution

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

Author Comment

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!

Expert Comment

ID: 12323406
no problem.

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

813 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

18 Experts available now in Live!

Get 1:1 Help Now