Solved

Count Leading Zero

Posted on 2004-10-15
4
446 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 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