|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- Sub AA()
- StrNum = "金额,数量"
- StrDate = "日期"
- Set SHX = Worksheets("Sheet2")
- StrSQL = "SELECT [ID],[单号],[摘要],[金额],[日期]"
- StrSQL = StrSQL & " FROM [" & SHX.Name & "$A1:10]"
- StrSQL = StrSQL & " WHERE 1=1"
- For IROW = 14 To 17
- If SHX.Cells(IROW, "C").Value <> "" Then '//条件值非空白
- If InStr("," & StrNum & ",", "," & SHX.Cells(IROW, "B").Value & ",") > 0 Then
- Rem 数字类型
- If Val(SHX.Cells(IROW, "C").Value) <> 0 Then
- StrSQL = StrSQL & " AND [" & SHX.Cells(IROW, "B").Value & "]>=" & SHX.Cells(IROW, "C").Value
- End If
- If Val(SHX.Cells(IROW, "E").Value) <> 0 Then
- StrSQL = StrSQL & " AND [" & SHX.Cells(IROW, "B").Value & "]<=" & SHX.Cells(IROW, "E").Value
- End If
- Else
- If InStr("," & StrDate & ",", "," & SHX.Cells(IROW, "B").Value & ",") > 0 Then
- Rem 日期类型 第一个日期>=日期
- StrSQL = StrSQL & " AND DATEDIFF('D','" & SHX.Cells(IROW, "C").Value & "',[" & SHX.Cells(IROW, "B").Value & "])>=0"
- Rem 第二个日期
- If SHX.Cells(IROW, "E").Value <> "" Then StrSQL = StrSQL & " AND DATEDIFF('D',[" & SHX.Cells(IROW, "B").Value & "],'" & SHX.Cells(IROW, "E").Value & "')>=0"
- Else
- Rem 文本类型 Instr=包含关系 张,张三,张三丰
- StrSQL = StrSQL & " AND INSTR([" & SHX.Cells(IROW, "B").Value & "],'" & SHX.Cells(IROW, "C").Value & "')>0"
- End If
- End If
- End If
- Next
- MsgBox StrSQL
- End Sub
复制代码
|
|