请帮助看看这种动态条件查询如何做?

yanwei82123300

请帮助看看这种动态条件查询如何做
我在窗体Totaldemand中的demand文本框中输入一个条件 例如：=[200923]+[200924]+[200925]后
在查询qryTotaldemand的total字段自动将结果算出
请问如何做？谢谢

Henry D. Sy

demand文本框在那里？？

yanwei82123300

在窗体中，标签名为total

Henry D. Sy

Dim strArray() As String
    Dim strTemp As String
    Dim strSQL As String
    Dim Qdf As DAO.QueryDef
    Dim i As Integer
    If IsNull(Me.demand) Then Exit Sub
    strArray = Split(Me.demand, "+")
    For i = 0 To UBound(strArray)
        strTemp = strTemp & "Nz(" & strArray(i) & ")+"
    Next
    strTemp = Left(strTemp, Len(strTemp) - 1)
    strSQL = "SELECT Table1.*, " & strTemp & " AS total FROM Table1"
    Set Qdf = CurrentDb.QueryDefs("qryTotaldemand")
    Qdf.SQL = strSQL
    Qdf.Close
    Set Qdf = Nothing
    Me.RecordSource = "qryTotaldemand"

yanwei82123300

老师请问是输入格式或方法Nz([200924])+Nz([200925])+Nz([200926])吗有没有办法直接输入200924 +20025+200926格式自动添加的？

Henry D. Sy

1. Dim strArray() As String
   
2.     Dim strTemp As String
   
3.     Dim strSQL As String
   
4.     Dim Qdf As DAO.QueryDef
   
5.     Dim i As Integer
   
6.     If IsNull(Me.demand) Then Exit Sub
   
7.     strArray = Split(Me.demand, "+")
   
8.     For i = 0 To UBound(strArray)
   
9.         strTemp = strTemp & "Nz([" & strArray(i) & "])+"
   
10.     Next
    
11.     strTemp = Left(strTemp, Len(strTemp) - 1)
    
12.     strSQL = "SELECT Table1.*, " & strTemp & " AS total FROM Table1"
    
13.     Set Qdf = CurrentDb.QueryDefs("qryTotaldemand")
    
14.     Qdf.SQL = strSQL
    
15.     Qdf.Close
    
16.     Set Qdf = Nothing
    
17.     Me.RecordSource = "qryTotaldemand"

复制代码

直接输入200922+200923

yanwei82123300

thank you teacher help me

yanwei82123300

6d老师：
我还有一个问题：想在Totaldemand中的demand文本框中输入一个条件：200923 to 200927
怎样编写？
谢谢

Henry D. Sy

6d老师：
我还有一个问题：想在Totaldemand中的demand文本框中输入一个条件：200923 to 200927
怎样编写？
谢谢
yanwei82123300 发表于 2009-4-5 15:10

1. Private Sub demand_AfterUpdate()
   
2.     Dim strArray() As String
   
3.     Dim strTemp As String
   
4.     Dim strSQL As String
   
5.     Dim Qdf As DAO.QueryDef
   
6.     Dim i As Long
   
7.     Dim BeginVal As Long, EndVal As Long
   
8.     If IsNull(Me.demand) Then Exit Sub
   
9.     strArray = Split(Me.demand, "to")
   
10.     BeginVal = Val(strArray(0))
    
11.     EndVal = Val(strArray(UBound(strArray)))
    
12.     For i = BeginVal To EndVal
    
13.         strTemp = strTemp & "Nz([" & i & "])+"
    
14.     Next
    
15.     strTemp = Left(strTemp, Len(strTemp) - 1)
    
16.     strSQL = "SELECT Table1.*, " & strTemp & " AS total FROM Table1"
    
17.     Set Qdf = CurrentDb.QueryDefs("qryTotaldemand")
    
18.     Qdf.SQL = strSQL
    
19.     Qdf.Close
    
20.     Set Qdf = Nothing
    
21.     Me.RecordSource = "qryTotaldemand"
    
22. End Sub

复制代码

阿六爱狗

学习了