以文本方式查看主题 - Foxtable(狐表) (http://foxtable.net/bbs/index.asp) -- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2) ---- 请教狐狸爸爸:身份证号码有效性验证,提取籍贯、性别、出生日期、年龄信息 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=22356) |
||||
-- 作者:lipiti -- 发布时间:2012/8/9 23:13:00 -- 请教狐狸爸爸:身份证号码有效性验证,提取籍贯、性别、出生日期、年龄信息
请教狐狸爸爸:身份证号码有效性验证,提取籍贯、性别、出生日期、年龄信息 以下是Access的窗体VBA代码,拿来用到狐表中,需要如何编写成狐表的代码! Option Explicit \'=================================================================== \'函数名称: IsIDNumber \'功能描述: 根据GB11643-1999编码规则验证18位身份证号码是否有效 \'输入参数: 参数1:IDNumber 必需的,18位身份证号码 \'返回参数: 有效返回True,无效返回False \'兼 容 性: \'使用示例: IsIDNumber("370284197901130819") \'返回True \'相关调用: \'作 者: 红尘如烟 \'创建日期: 20010-4-25 \'=================================================================== Function IsIDNumber(ByVal IDNumber As String) As Boolean Const W As String = "79058421637905842" \'加权因子 Const C As String = "10X98765432" \'校验码 Dim S As Integer, i As Integer, T As Integer IDNumber = UCase(Trim(IDNumber)) If Len(IDNumber) <> 18 Then Exit Function If Not IsNumeric(Mid(IDNumber, 1, 17)) Then Exit Function If Not IDNumber Like "*[0-9X]" Then Exit Function For i = 1 To 17 T = Mid(W, i, 1) If T = 0 Then T = 10 S = S + Mid(IDNumber, i, 1) * T Next T = S Mod 11 If Right(IDNumber, 1) = Mid(C, T + 1, 1) Then IsIDNumber = True End Function Private Sub Form_Load() End Sub Private Sub txtAge_BeforeUpdate(Cancel As Integer) End Sub Private Sub txtIDNumber_AfterUpdate() If Nz(Me.txtIDNumber) = "" Then Exit Sub \'验证输入的是否为一个有效的身份证号码 If Not IsIDNumber(Nz(Me.txtIDNumber)) Then MsgBox "无效的身份证号码!", vbInformation Me.txtIDNumber = Null Me.txtBirthday = Null Me.txtSex = Null Me.txtNativePlace = Null Else \'从表中取得籍贯 Me.txtNativePlace = DLookup("[FAddress]", "tblNativePlaceList", "[FNumber]=" & Left(Me.txtIDNumber, 6)) \'提取出生日期(第7-14位) Me.txtBirthday = DateSerial(Mid(Me.txtIDNumber, 7, 4), Mid(Me.txtIDNumber, 11, 2), Mid(Me.txtIDNumber, 13, 2)) \'提取性别(第17位,奇数为男,偶数为女) Me.txtSex = IIf(Mid(Me.txtIDNumber, 17, 1) Mod 2 = 0, "女", "男") \'根据出生日期计算年龄,精确到年 \' Me.txtAge = DateDiff("yyyy", Me.txtBirthday, Date) \'根据出生日期计算年龄,精确到月 Me.txtAge = DateDiff("m", Me.txtBirthday, Date) \\ 12 \'根据出生日期计算年龄,精确到天 \' Me.txtAge = DateDiff("d", Me.txtBirthday, Date) \\ 365.25 End If End Sub [此贴子已经被作者于2012-8-10 12:35:25编辑过]
|
||||
-- 作者:blackzhu -- 发布时间:2012/8/10 7:30:00 -- ReadBirthDay从身份证号码中读取出生日期。 语法 ReadBirthDay(Value) Value:身份证号码 例如: Dim Identify As
String = "410110197109172433" 上述代码的输出结果是“1971-9-17”,即1971年9月17日 |
||||
-- 作者:blackzhu -- 发布时间:2012/8/10 7:31:00 -- ReadSex从身份证号码中读取性别。 语法 ReadSex(Value) Value:身份证号码 例如: Dim Identify As
String = "410110197109172433" 上述代码的输出结果是“男”。 |
||||
-- 作者:wjl-se -- 发布时间:2012/8/10 9:13:00 -- 楼主现在只缺提取籍贯和有效性验证了吧 |
||||
-- 作者:lipiti -- 发布时间:2012/8/10 10:07:00 -- If e .DataCol . Name = "身份证号码" Then \'如果更改的是身份证号码列 If e .DataRow . IsNull( "身份证号码" ) Then \'身份证号码是否为空 e .DataRow ( "出生日期" ) = Nothing \'如果为空,则清除出生日期 e .DataRow ( "性别") = Nothing Else \'否则从身份证号码列中提取出生日期 e .DataRow ( "出生日期" ) = ReadBirthday ( e. DataRow ("身份证号码" )) e .DataRow ( "性别") = ReadSex (e .DataRow ( "身份证号码" )) End If End If If e.DataCol.name = "出生日期" Then \'如果更改的是出生日期列 If e.DataRow.IsNull("出生日期") Then \'出生日期是否为空 e.DataRow("年龄") = Nothing \'如果为空,则清除年龄 Else \'否则从出生日期列中提取出年龄 Dim n As Integer= Date.Today.Year - e.DataRow("出生日期").Year If e.DataRow("出生日期").AddMonths(n*12) > Date.Today Then n = n -1 End If e.DataRow("年龄") = n End If End If ------------------------------------------------------------------------------------------------------ 以上是从身份证号码提取、性别、出生日期、年龄信息 现在缺少从身份证号码有效性验证和提取籍贯
|
||||
-- 作者:lipiti -- 发布时间:2012/8/10 10:07:00 -- 4楼的兄弟你太懂我了! 现在缺少从身份证号码有效性验证和提取籍贯!
[此贴子已经被作者于2012-8-10 10:07:19编辑过]
|
||||
-- 作者:wjl-se -- 发布时间:2012/8/10 10:27:00 -- 可惜我不是高手,帮不了你。不过我手上收藏了一位高手用表达式做的例子。你参考一下吧
|
||||
-- 作者:lipiti -- 发布时间:2012/8/10 11:24:00 -- 谢谢7楼兄弟,这个是用表达式解决的! 我们现在需要使用表事件DataColChanged的代码解决,有高手能解决这个问题吗?
|
||||
-- 作者:lipiti -- 发布时间:2012/8/10 11:35:00 -- IIF(LEN([ID])=18, IIF( ((Convert(SUBSTRING([ID],1,1),System.Int16)*7+ Convert(SUBSTRING([ID],2,1),System.Int16)*9+ Convert(SUBSTRING([ID],3,1),System.Int16)*10+ Convert(SUBSTRING([ID],4,1),System.Int16)*5+ Convert(SUBSTRING([ID],5,1),System.Int16)*8+ Convert(SUBSTRING([ID],6,1),System.Int16)*4+ Convert(SUBSTRING([ID],7,1),System.Int16)*2+ Convert(SUBSTRING([ID],8,1),System.Int16)*1+ Convert(SUBSTRING([ID],9,1),System.Int16)*6+ Convert(SUBSTRING([ID],10,1),System.Int16)*3+ Convert(SUBSTRING([ID],11,1),System.Int16)*7+ Convert(SUBSTRING([ID],12,1),System.Int16)*9+ Convert(SUBSTRING([ID],13,1),System.Int16)*10+ Convert(SUBSTRING([ID],14,1),System.Int16)*5+ Convert(SUBSTRING([ID],15,1),System.Int16)*8+ Convert(SUBSTRING([ID],16,1),System.Int16)*4+ Convert(SUBSTRING([ID],17,1),System.Int16)*2)%11)=2,\'X\', IIF( ((Convert(SUBSTRING([ID],1,1),System.Int16)*7+ Convert(SUBSTRING([ID],2,1),System.Int16)*9+ Convert(SUBSTRING([ID],3,1),System.Int16)*10+ Convert(SUBSTRING([ID],4,1),System.Int16)*5+ Convert(SUBSTRING([ID],5,1),System.Int16)*8+ Convert(SUBSTRING([ID],6,1),System.Int16)*4+ Convert(SUBSTRING([ID],7,1),System.Int16)*2+ Convert(SUBSTRING([ID],8,1),System.Int16)*1+ Convert(SUBSTRING([ID],9,1),System.Int16)*6+ Convert(SUBSTRING([ID],10,1),System.Int16)*3+ Convert(SUBSTRING([ID],11,1),System.Int16)*7+ Convert(SUBSTRING([ID],12,1),System.Int16)*9+ Convert(SUBSTRING([ID],13,1),System.Int16)*10+ Convert(SUBSTRING([ID],14,1),System.Int16)*5+ Convert(SUBSTRING([ID],15,1),System.Int16)*8+ Convert(SUBSTRING([ID],16,1),System.Int16)*4+ Convert(SUBSTRING([ID],17,1),System.Int16)*2)%11) IN (\'1\',\'0\') ,1- ((Convert(SUBSTRING([ID],1,1),System.Int16)*7+ Convert(SUBSTRING([ID],2,1),System.Int16)*9+ Convert(SUBSTRING([ID],3,1),System.Int16)*10+ Convert(SUBSTRING([ID],4,1),System.Int16)*5+ Convert(SUBSTRING([ID],5,1),System.Int16)*8+ Convert(SUBSTRING([ID],6,1),System.Int16)*4+ Convert(SUBSTRING([ID],7,1),System.Int16)*2+ Convert(SUBSTRING([ID],8,1),System.Int16)*1+ Convert(SUBSTRING([ID],9,1),System.Int16)*6+ Convert(SUBSTRING([ID],10,1),System.Int16)*3+ Convert(SUBSTRING([ID],11,1),System.Int16)*7+ Convert(SUBSTRING([ID],12,1),System.Int16)*9+ Convert(SUBSTRING([ID],13,1),System.Int16)*10+ Convert(SUBSTRING([ID],14,1),System.Int16)*5+ Convert(SUBSTRING([ID],15,1),System.Int16)*8+ Convert(SUBSTRING([ID],16,1),System.Int16)*4+ Convert(SUBSTRING([ID],17,1),System.Int16)*2)%11) ,12- ((Convert(SUBSTRING([ID],1,1),System.Int16)*7+ Convert(SUBSTRING([ID],2,1),System.Int16)*9+ Convert(SUBSTRING([ID],3,1),System.Int16)*10+ Convert(SUBSTRING([ID],4,1),System.Int16)*5+ Convert(SUBSTRING([ID],5,1),System.Int16)*8+ Convert(SUBSTRING([ID],6,1),System.Int16)*4+ Convert(SUBSTRING([ID],7,1),System.Int16)*2+ Convert(SUBSTRING([ID],8,1),System.Int16)*1+ Convert(SUBSTRING([ID],9,1),System.Int16)*6+ Convert(SUBSTRING([ID],10,1),System.Int16)*3+ Convert(SUBSTRING([ID],11,1),System.Int16)*7+ Convert(SUBSTRING([ID],12,1),System.Int16)*9+ Convert(SUBSTRING([ID],13,1),System.Int16)*10+ Convert(SUBSTRING([ID],14,1),System.Int16)*5+ Convert(SUBSTRING([ID],15,1),System.Int16)*8+ Convert(SUBSTRING([ID],16,1),System.Int16)*4+ Convert(SUBSTRING([ID],17,1),System.Int16)*2)%11) )) ,IIF(LEN([ID])=15, IIF( ((Convert(SUBSTRING([ID],1,1),System.Int16)*7+ Convert(SUBSTRING([ID],2,1),System.Int16)*9+ Convert(SUBSTRING([ID],3,1),System.Int16)*10+ Convert(SUBSTRING([ID],4,1),System.Int16)*5+ Convert(SUBSTRING([ID],5,1),System.Int16)*8+ Convert(SUBSTRING([ID],6,1),System.Int16)*4+ 2+ 9+ Convert(SUBSTRING([ID],7,1),System.Int16)*6+ Convert(SUBSTRING([ID],8,1),System.Int16)*3+ Convert(SUBSTRING([ID],9,1),System.Int16)*7+ Convert(SUBSTRING([ID],10,1),System.Int16)*9+ Convert(SUBSTRING([ID],11,1),System.Int16)*10+ Convert(SUBSTRING([ID],12,1),System.Int16)*5+ Convert(SUBSTRING([ID],13,1),System.Int16)*8+ Convert(SUBSTRING([ID],14,1),System.Int16)*4+ Convert(SUBSTRING([ID],15,1),System.Int16)*2)%11)=2,\'X\', IIF( ((Convert(SUBSTRING([ID],1,1),System.Int16)*7+ Convert(SUBSTRING([ID],2,1),System.Int16)*9+ Convert(SUBSTRING([ID],3,1),System.Int16)*10+ Convert(SUBSTRING([ID],4,1),System.Int16)*5+ Convert(SUBSTRING([ID],5,1),System.Int16)*8+ Convert(SUBSTRING([ID],6,1),System.Int16)*4+ 2+ 9+ Convert(SUBSTRING([ID],7,1),System.Int16)*6+ Convert(SUBSTRING([ID],8,1),System.Int16)*3+ Convert(SUBSTRING([ID],9,1),System.Int16)*7+ Convert(SUBSTRING([ID],10,1),System.Int16)*9+ Convert(SUBSTRING([ID],11,1),System.Int16)*10+ Convert(SUBSTRING([ID],12,1),System.Int16)*5+ Convert(SUBSTRING([ID],13,1),System.Int16)*8+ Convert(SUBSTRING([ID],14,1),System.Int16)*4+ Convert(SUBSTRING([ID],15,1),System.Int16)*2)%11) IN (\'1\',\'0\') ,1- ((Convert(SUBSTRING([ID],1,1),System.Int16)*7+ Convert(SUBSTRING([ID],2,1),System.Int16)*9+ Convert(SUBSTRING([ID],3,1),System.Int16)*10+ Convert(SUBSTRING([ID],4,1),System.Int16)*5+ Convert(SUBSTRING([ID],5,1),System.Int16)*8+ Convert(SUBSTRING([ID],6,1),System.Int16)*4+ 2+ 9+ Convert(SUBSTRING([ID],7,1),System.Int16)*6+ Convert(SUBSTRING([ID],8,1),System.Int16)*3+ Convert(SUBSTRING([ID],9,1),System.Int16)*7+ Convert(SUBSTRING([ID],10,1),System.Int16)*9+ Convert(SUBSTRING([ID],11,1),System.Int16)*10+ Convert(SUBSTRING([ID],12,1),System.Int16)*5+ Convert(SUBSTRING([ID],13,1),System.Int16)*8+ Convert(SUBSTRING([ID],14,1),System.Int16)*4+ Convert(SUBSTRING([ID],15,1),System.Int16)*2)%11) ,12- ((Convert(SUBSTRING([ID],1,1),System.Int16)*7+ Convert(SUBSTRING([ID],2,1),System.Int16)*9+ Convert(SUBSTRING([ID],3,1),System.Int16)*10+ Convert(SUBSTRING([ID],4,1),System.Int16)*5+ Convert(SUBSTRING([ID],5,1),System.Int16)*8+ Convert(SUBSTRING([ID],6,1),System.Int16)*4+ 2+ 9+ Convert(SUBSTRING([ID],7,1),System.Int16)*6+ Convert(SUBSTRING([ID],8,1),System.Int16)*3+ Convert(SUBSTRING([ID],9,1),System.Int16)*7+ Convert(SUBSTRING([ID],10,1),System.Int16)*9+ Convert(SUBSTRING([ID],11,1),System.Int16)*10+ Convert(SUBSTRING([ID],12,1),System.Int16)*5+ Convert(SUBSTRING([ID],13,1),System.Int16)*8+ Convert(SUBSTRING([ID],14,1),System.Int16)*4+ Convert(SUBSTRING([ID],15,1),System.Int16)*2)%11) )) ,\'\')) ----------------------------------------------------------------------------------------------------------------------- 以上是校正码表达式代码,谁能帮忙改成用表事件DataColChanged的代码解决
|
||||
-- 作者:woiz -- 发布时间:2012/8/10 12:15:00 -- 这是我编的一个验证身份证号码的内部函数,参数: 身份证号码 string 返回 true 校验码正确 返回 false 校验码错
Dim ysfz As String = args(0) If ysfz.length <> 18 Then Dim yzm As Integer() = {7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2} Dim yzm1 As String
身份证中信息没有包含籍贯信息,只有发证机关的信息,要建立相关的数据库才能查询到,使用身份证的前6位 |