以文本方式查看主题

-  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
--  请教狐狸爸爸:身份证号码有效性验证,提取籍贯、性别、出生日期、年龄信息
 下载信息  [文件大小:   下载次数: ]
图片点击可在新窗口打开查看点击浏览该文件:身份证籍贯编码.xls

请教狐狸爸爸:身份证号码有效性验证,提取籍贯、性别、出生日期、年龄信息
以下是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"
Output.Show(ReadBirthDay(Identify))

上述代码的输出结果是“1971-9-17”,即1971年9月17日


--  作者:blackzhu
--  发布时间:2012/8/10 7:31:00
--  

ReadSex

身份证号码中读取性别。

语法

ReadSex(Value)

Value:身份证号码

例如:

Dim Identify As String = "410110197109172433"
Dim
Sex As String = ReadSex(Identify)
Output.Show(Sex)

上述代码的输出结果是“男”。


--  作者: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
--  

可惜我不是高手,帮不了你。不过我手上收藏了一位高手用表达式做的例子。你参考一下吧

 

 下载信息  [文件大小:   下载次数: ]
图片点击可在新窗口打开查看点击浏览该文件:身份证信息提取-擎天柱版.table


--  作者: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
   Return False
End If

Dim yzm As Integer() = {7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2}
Dim yzm2 As String = "10X98765432"

Dim yzm1 As String
Dim he As Integer = 0
For i As Integer = 0 To 16
    yzm1 = ysfz.Chars(i)
    he = he + cint(yzm1) * yzm(i)
Next
he = he Mod 11
Dim xsfz As String = ysfz.Remove(17,1) & yzm2.Chars(he)
\'MessageBox.show(ysfz)
\'MessageBox.show(xsfz)
If xsfz = ysfz
   Return True
Else
   Return False
End If

 

身份证中信息没有包含籍贯信息,只有发证机关的信息,要建立相关的数据库才能查询到,使用身份证的前6位