如何将文本框里的条件字符串传递给SQL查询

tuhaiyin2008

SQL语句为：SELECT  单元，数量　FROM 电表 　WHERE ([forms]![frm_能耗报表]![txt_strwhere]);
其中：[forms]![frm_能耗报表]![txt_strwhere]中文本内容为一条件字符串，由VBA根据判断条件产生，如：（[电表]![年] =2009 And [电表]![月]=9），但是查询结果不对，where 后面的条件起不到限制作用。请帮忙解决。

kangking

dim sql as string

sql="SELECT  单元，数量　FROM 电表 　WHERE " &  ([forms]![frm_能耗报表]![txt_strwhere]) & ";"
然后执行sql.

这样试试，不行的话请上传示例。

tuhaiyin2008

还是不行,现已上传示例.请 KangKing 大侠帮忙解决.另外还有查询中条件格式闪烁怎么解决也请帮忙指点一下.

tuhaiyin2008

请帮忙指点一下.

Henry D. Sy

搜索刘小军

tuhaiyin2008

非常感谢!

kangking

将你的单击事件的代码用下面的代码替换试一下。


    Dim sql As String
    Dim qd As DAO.QueryDef

    Dim strWhere As String  '定义条件字符串




    Set qd = CurrentDb.QueryDefs("qry_电")



    strWhere = ""    '设定初始值－空字符串

    If IsNull(Me.cmb_Year) And IsNull(Me.cmb_month) Then

        strWhere = ""

    ElseIf Not IsNull(Me.cmb_Year) And IsNull(Me.cmb_month) Then

        strWhere = "[年]=" & Me.cmb_Year

    ElseIf Not IsNull(Me.cmb_Year) And Not IsNull(Me.cmb_month) And IsNull(Me.cmb_add) Then

        strWhere = "[年]=" & Me.cmb_Year & " And [月]=" & Me.cmb_month
    ElseIf Not IsNull(Me.cmb_Year) And Not IsNull(Me.cmb_month) And Not IsNull(Me.cmb_add) Then
        strWhere = "[年]=" & Me.cmb_Year & " And ( [月]>=1" & " And [月]<=" & Me.cmb_month & ")"



    End If


    Me.txt_Product = DSum("[产量_吨]", "[产量表]", strWhere)
    Me.txt_strwhere = strWhere

    sql = "SELECT 电表.单元, Sum(电表.数量_度) AS [数量(度)], Sum(电表.费用_元) AS [费用(元)], [费用(元)]/" & [Forms]![frm_能耗报表]![txt_Product] & " AS [吨麦芽耗电(元)], [数量(度)]/" & [Forms]![frm_能耗报表]![txt_Product] & " AS [吨麦芽耗电(度)], 系统表.系统" & _
        " FROM 电表 LEFT JOIN 系统表 ON 电表.单元 = 系统表.单元" & _
        " WHERE " & ([Forms]![frm_能耗报表]![txt_strwhere]) & _
        " GROUP BY 电表.单元, 系统表.系统 " & _
        " UNION ALL SELECT """" as 单元, Sum(电表.数量_度) AS [数量(度)], Sum(电表.费用_元) AS [费用(元)], [费用(元)]/" & [Forms]![frm_能耗报表]![txt_Product] & " AS [吨麦芽耗电(元)], [数量(度)]/" & [Forms]![frm_能耗报表]![txt_Product] & " AS [吨麦芽耗电(度)], 系统表.系统 & ""合计"" as 系统" & _
        " FROM 电表 LEFT JOIN 系统表 ON 电表.单元 = 系统表.单元" & _
        " WHERE " & ([Forms]![frm_能耗报表]![txt_strwhere]) & _
        " GROUP BY 系统表.系统" & _
        " UNION ALL SELECT """" as 单元, Sum(电表.数量_度) AS [数量(度)], Sum(电表.费用_元) AS [费用(元)], [费用(元)]/" & [Forms]![frm_能耗报表]![txt_Product] & " AS [吨麦芽耗电(元)], [数量(度)]/" & [Forms]![frm_能耗报表]![txt_Product] & " AS [吨麦芽耗电(度)], "" 电总计"" as 系统" & _
        " FROM 电表 LEFT JOIN 系统表 ON 电表.单元 = 系统表.单元" & _
        " WHERE " & ([Forms]![frm_能耗报表]![txt_strwhere]) & ";"

    qd.sql = sql
    qd.Close
    Set qd = Nothing

    Me.Refresh
    DoEvents

cookyliang

关注一下

cookyliang

收藏了话题