MS Access表:纠正Zip_CD字段中的非前导零 [英] MS Access Table: Correct for non leading zeroes in Zip_CD Field

问题描述

长时间休假后，我返回Access，遇到了一些困难.我有一个包含邮政编码字段的表.提供的某些邮政编码是5位数字(52186)，有些是带有尾随邻域代码(77005-1568)的10位数字类型.但是，前导零没有被保留，我需要重新插入它们.例如(04074)显示在(4070).但是，9位数的邮政编码包含前导零.我需要一种将前导零添加到5位邮政编码的方法.我不能使用诸如转换为数字然后格式化为"00000"之类的技巧，因为那样会造成9位数邮政编码的混乱.另外，由于我离开Access很久了，所以我不确定是否应该使用函数，查询，某种类型的VBA RecordSet或其他方式执行此操作.我不记得最好的方法.任何和所有建议将不胜感激.谢谢！

I'm back to Access after an extended leave and am running into some difficulty. I have a table that includes a Zip Code field. Some of the Zip codes that are supplied are 5 digits (52186) and some are the 10 digit type with trailing neighborhood code (77005-1568). However the leading zeroes have not been preserved and I need to reinsert them. For instance (04074) shows up at (4070). The 9 digit zip codes however have included the leading zeroes. I need a way to add the leading zeroes to the 5 digit zip codes. I cannot use a trick like converting to a number and then formatting to "00000" because that creates havoc with the 9 digit zips. Also since I've been absent from Access for so long I'm not sure if I should do this with a function, a query, some type of VBA RecordSet or what. I can't recall the best way to approach. Any and all advice would be greatly appreciated. Thank you!

-查理

推荐答案

您可以使用以下表达式:

You can use this expression:

TrueZip = IIf(IsNumeric([Zip]), Format([Zip], "00000"), Right("00" & [Zip], 10))

在查询中，它将(在GUI中)为:

In a query, it would (in the GUI) be:

TrueZip: IIf(IsNumeric([Zip]),Format([Zip],"00000"),Right("00" & [Zip],10))

作为SQL，它会像这样:

As SQL, it would go like this:

Select 
    *,
    IIf(IsNumeric([Zip]),Format([Zip],"00000"),Right("00" & [Zip],10)) As TrueZip
From
    YourTable

这篇关于MS Access表:纠正Zip_CD字段中的非前导零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！