我有3个combo控件,C1代表年,C2月,C3日
如果C1为空,那么select * from a
如果C1不为空,C2,C3为空,那就只查找C1年的所有数据
如果C1,C2不为空,C3为空,也只查所代表的月份所有数据
同理,全不为空,就查当日的数据
那么,能不能只用一条select 语句查出以上四种情况?该怎么写?
如果C1为空,那么select * from a
如果C1不为空,C2,C3为空,那就只查找C1年的所有数据
如果C1,C2不为空,C3为空,也只查所代表的月份所有数据
同理,全不为空,就查当日的数据
那么,能不能只用一条select 语句查出以上四种情况?该怎么写?
iif(c1 = "", "", "and year=" & c1 & " ") & _
iif(c2 = "", "", "and month=" & c2 & " ") & _
iif(c3 = "", "", "and day=" & c3 & " ") & _
Private Sub Command1_Click()
Dim SQL As String, MaxDay As Long
SQL = ""
If Len(C1.Text) > 0 Then
If Len(C2.Text) > 0 Then
If Int(C2.Text) < 7 Then
If Int(C2.Text) Mod 2 = 0 Then
If Int(C2.Text) = 2 Then
If (Int(C1.Text) - 2000) Mod 4 = 0 Then
MaxDay = 29
Else
MaxDay = 28
End If
Else
MaxDay = 30
End If
Else
MaxDay = 31
End If
Else
If (Int(C2.Text) - 1) Mod 2 = 0 Then
MaxDay = 30
Else
MaxDay = 31
End If
End If
If Len(C3.Text) > 0 Then
SQL = "(日期>=#" & C1.Text & "-" & C2.Text & "-" & C3.Text & " 00:00:00# And 日期>=#" & C1.Text & "-" & C2.Text & "-" & C3.Text & " 23:59:59#)"
Else
SQL = "(日期>=#" & C1.Text & "-" & C2.Text & "-1 00:00:00# And 日期>=#" & C1.Text & "-" & C2.Text & "-" & MaxDay & " 23:59:59#)"
End If
Else
SQL = "(日期>=#" & C1.Text & "-1-1 00:00:00# And 日期<=#" & C1.Text & "-12-31 23:59:59#)"
End If
End If
If Len(SQL) = 0 Then
SQL = "select * from a"
Else
SQL = "select * from a Where " & SQL
End If
MsgBox SQL
End Sub
这里程序补上Private Sub Command1_Click()
Dim SQL As String, MaxDay As Long
SQL = ""
If Len(C1.Text) > 0 Then
If Len(C2.Text) > 0 Then
If Int(C2.Text) < 7 Then
If Int(C2.Text) Mod 2 = 0 Then
If Int(C2.Text) = 2 Then
If (Int(C1.Text) - 2000) Mod 4 = 0 Then
MaxDay = 29
Else
MaxDay = 28
End If
Else
MaxDay = 30
End If
Else
MaxDay = 31
End If
Else
If (Int(C2.Text) - 1) Mod 2 = 0 Then
MaxDay = 30
Else
MaxDay = 31
End If
End If
If Len(C3.Text) > 0 Then
SQL = "(日期>=#" & C1.Text & "-" & C2.Text & "-" & C3.Text & " 00:00:00# And 日期>=#" & C1.Text & "-" & C2.Text & "-" & C3.Text & " 23:59:59#)"
Else
SQL = "(日期>=#" & C1.Text & "-" & C2.Text & "-1 00:00:00# And 日期>=#" & C1.Text & "-" & C2.Text & "-" & MaxDay & " 23:59:59#)"
End If
Else
SQL = "(日期>=#" & C1.Text & "-1-1 00:00:00# And 日期<=#" & C1.Text & "-12-31 23:59:59#)"
End If
Else
SQL = "(日期>=#" & Date & " 00:00:00# And 日期<=#" & Date & " 23:59:59#)"
End If
If Len(SQL) = 0 Then
SQL = "select * from a"
Else
SQL = "select * from a Where " & SQL
End If
MsgBox SQL
End Sub
If Len(SQL) = 0 Then
SQL = "select * from a"
Else
SQL = "select * from a Where " & SQL
End If
就没必要判断了,直接用
SQL = "select * from a Where " & SQL
这样就好了
不过我觉得1楼的更好,简单点,用IIF和DATEDIFF函数能很好的解决问题
谢谢了