Office中国论坛/Access中国论坛

标题: 【Access小品】逸马杀犬于道---代码简化示例 [打印本页]

作者: todaynew 时间: 2011-2-13 17:45
标题: 【Access小品】逸马杀犬于道---代码简化示例
　　相传欧阳修任职翰林院时，一次与三个下属出游，忽见路旁有匹飞驰的马踩死了一只狗。一人曰：“有黄犬卧于道，马惊，奔逸而来，蹄而死之”，另一人曰：“有黄犬卧于通衢，逸马蹄而杀之。”，第三人曰：“有犬卧于通衢，逸马遭之而毙。” 欧阳修曰：“‘逸马杀犬于道’，六字足矣！”

　　今有版友名草儿者做一函数五十余句，老汉观之甚为繁琐，便颇不以为然，便递次简化，终以三句搞定。

草儿同志原函数如下：
Public Function GetBarCode(BarCode As String, Optional A1 As String = "*", Optional A2 As String = "%") As String
'用查找数据库中"Usys商品信息全部"表中商品条码或商品编码
On Error GoTo GetRs_Error
Dim StrQuery As String  '变量SQL查询语句
Dim Rs As New ADODB.Recordset '新建Recordset对象
Dim Conn As New ADODB.Connection '新建Connection对象
'用于将"%"字符转变成"*"
      Dim i As Integer
      Dim B, C As String
      C = ""
      BarCode = Trim(BarCode)
      For i = 1 To Len(BarCode)
      B = CStr(Mid(BarCode, i, 1)) '返回符串中左边第I起后的1个字符
      If B = A1 Then B = A2  '将*改为%用于SQL查询
      C = C & B
      Next i
      BarCode = C
Set Conn = CurrentProject.Connection  '打开当前连接
GetBarCode = 0
'先查询编码
   StrQuery = "Select 商品条码, 商品编码  FROM Usys商品信息全部 Where (((商品编码) Like '" & BarCode & "')) orDER BY 停产 DESC , 促销 DESC , 销售 DESC , 采购 DESC , 登记日期 DESC;"
Rs.Open (StrQuery), Conn, adOpenForwardOnly, adLockBatchOptimistic
If Not Rs.EOF Then
      With Rs
      GetBarCode = Rs(0)
      End With
      Else '查询条码前面部分可不输入
         StrQuery = "Select 商品条码 FROM Usys商品信息全部 Where (((商品条码) Like '%" & BarCode & "')) orDER BY 停产 DESC , 促销 DESC , 销售 DESC , 采购 DESC , 登记日期 DESC;"
         'Rs.Close
         Rs.Open (StrQuery), Conn, adOpenForwardOnly, adLockBatchOptimistic
         If Not Rs.EOF Then
            With Rs
            GetBarCode = Rs(0)
            End With
         Else
            '查询名称是否包涵字符
            StrQuery = "Select 商品条码, 商品名称  FROM Usys商品信息全部 Where (((商品名称) Like '%" & BarCode & "%')) orDER BY 停产 DESC , 促销 DESC , 销售 DESC , 采购 DESC , 登记日期 DESC;"
         ' Rs.Close
            Rs.Open (StrQuery), Conn, adOpenKeyset, adLockBatchOptimistic
         If Not Rs.EOF Then
            With Rs
            GetBarCode = Rs(0)
            End With
         Else
            GetBarCode = "1"  '查无记录返回 “1”
         End If
         End If
End If
Rs.Close
Getrs_exit:
Set Rs = Nothing
Set Conn = Nothing
Exit Function

GetRs_Error:
GetBarCode = "2"  '错误返回 “2”
MsgBox (Err.Description)
Resume Getrs_exit
End Function
------------------------------------------------------------------------------------------------

1、简单一些可以：
Public Function GetBarCode(BarCode As String) As String
Dim Rs As New ADODB.Recordset
Dim StrQuery As String
StrQuery = "Select 商品条码,商品编码,商品名称  FROM Usys商品信息全部"
Rs.Open StrQuery, CurrentProject.Connection, adOpenForwardOnly, adLockBatchOptimistic
Rs.Find "商品编码 Like '" & BarCode & "'"
If Rs.EOF = False Then
GetBarCode = Rs(0).Value
Else
Rs.MoveFirst
Rs.Find "商品条码 Like '%" & BarCode & "'"
If Rs.EOF = False Then
      GetBarCode = Rs(0).Value
Else
      Rs.MoveFirst
      Rs.Find "商品名称 Like '%" & BarCode & "%'"
      If Rs.EOF = False Then
         GetBarCode = Rs(0).Value
      Else
         GetBarCode = "1"
      End If
End If
End If
Rs.Close
Set Rs = Nothing
End Function
-----------------------------------------------------------------------------------------------
2、再简单一些可以：
Public Function GetBarCode(BarCode As String) As String
Dim Rs As New ADODB.Recordset
Dim StrQuery As String
StrQuery = "Select 商品条码,商品编码,商品名称  FROM Usys商品信息全部 "
StrQuery = StrQuery & " where 商品编码 Like '" & BarCode & "' or 商品条码 Like '%" & BarCode & "' or 商品名称 Like '%" & BarCode & "%'"
Rs.Open StrQuery, CurrentProject.Connection, adOpenForwardOnly, adLockBatchOptimistic
If Rs.EOF = False Then
GetBarCode = Rs(0).Value
Else
GetBarCode = "1"
End If
Rs.Close
Set Rs = Nothing
End Function
-----------------------------------------------------------------------------------------------
3、还想简单一些可以：
Public Function GetBarCode(BarCode As String) As String
Dim Rs As New ADODB.Recordset
Dim StrQuery As String
StrQuery = "Select 商品条码,商品编码,商品名称  FROM Usys商品信息全部 "
StrQuery = StrQuery & " where 商品条码 & 商品编码 & 商品名称 Like '%" & BarCode & "%'"
Rs.Open StrQuery, CurrentProject.Connection, adOpenForwardOnly, adLockBatchOptimistic
If Rs.EOF = False Then
GetBarCode = Rs(0).Value
Else
GetBarCode = "1"
End If
Rs.Close
Set Rs = Nothing
End Function
-------------------------------------------------------------------------------------------------
4、最后终极简单的可以：
Public Function GetBarCode(BarCode As String) As String
dim str as string
str="商品条码 & 商品编码 & 商品名称 Like '*" & BarCode & "*'"
GetBarCode=Nz(Dlookup("商品条码","Usys商品信息全部",str),"1")
End Function
-------------------------------------------------------------------------------------------------
5、世界末日般的简单可以：
dim str as string
str="商品条码 & 商品编码 & 商品名称 Like '*" & BarCode & "*'"
str=Nz(Dlookup("商品条码","Usys商品信息全部",str),"1")

作者: xuwenning 时间: 2011-2-14 08:24
千古奇冤----------签到

作者: roych 时间: 2011-2-14 09:09
千古奇冤，AccCN一叶，同室操作，删帖太急~~开玩笑了，希望两个红尘和老汉别介意~~~支持下。

作者: wancheng 时间: 2011-2-15 02:27

作者: 玉树TMD临风 时间: 2015-5-31 17:04
老汉每次发贴总要旁征引博两句古语

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)