相传欧阳修任职翰林院时,一次与三个下属出游,忽见路旁有匹飞驰的马踩死了一只狗。一人曰:“有黄犬卧于道,马惊,奔逸而来,蹄而死之”,另一人曰:“有黄犬卧于通衢,逸马蹄而杀之。”,第三人曰:“有犬卧于通衢,逸马遭之而毙。” 欧阳修曰:“‘逸马杀犬于道’,六字足矣!”
今有版友名草儿者做一函数五十余句,老汉观之甚为繁琐,便颇不以为然,便递次简化,终以三句搞定。
草儿同志原函数如下: 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")
|
|
|