Leading Zeros Match Query

[hycho]

Hi,

Let's say I have a column full of zip codes, and I want to see 5 digits. There are some entries where it only has 3 digits because the first 2 digits are zero's. So, instead of seeing "00123," I just see "123."

Is there a way I can force the column to show 5 digits, so that I can see "00123"? This way, I can run a match query for table_A against table_B, so that table_A with "123" will match table_B with "00123."

Thanks for your help.

-Henry

 

[John Big Booty]

With your Table in design view enter 00000 in the format property for that field.

 

[John Big Booty]

123 should match with 00123 so long as both fields are Data Type Number. If your Data Type is Text you can use the Criteria;

Like "*123"

 

[hycho]

With your Table in design view enter 00000 in the format property for that field.

Thanks for the useful tip. What happens if I want the zip-code to be in text? I tried going to the table design view and changing the data-type to text and setting the format as "00000" and 00000. The leading zeros are omitted when I turn the zip-code into text.

If you're curious, I actually have to apply this format to other columns such as specialty codes. And for some reason, certain software can only accept data as text.

Thanks again for the help.

 

[John Big Booty]

Zip codes are essentially numeric, so why store them as text that just confuses thing

 

[gemma-the-husky]

design the zip code as text, ratherr than numeric - then the leading zeroes will be retained.