Office中国论坛/Access中国论坛

标题: 如何将文本框里的条件字符串传递给SQL查询 [打印本页]

作者: tuhaiyin2008 时间: 2009-10-28 23:30
标题: 如何将文本框里的条件字符串传递给SQL查询
本帖最后由 tuhaiyin2008 于 2009-10-29 19:47 编辑

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

作者: kangking 时间: 2009-10-29 08:57
dim sql as string

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

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

作者: tuhaiyin2008 时间: 2009-10-29 19:49
还是不行,现已上传示例.请 KangKing 大侠帮忙解决.另外还有查询中条件格式闪烁怎么解决也请帮忙指点一下.

作者: tuhaiyin2008 时间: 2009-10-29 20:37
请帮忙指点一下.

作者: Henry D. Sy 时间: 2009-10-29 20:55
搜索刘小军

作者: tuhaiyin2008 时间: 2009-10-30 11:18
非常感谢!

作者: kangking 时间: 2009-10-30 13:48
将你的单击事件的代码用下面的代码替换试一下。

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 时间: 2009-10-30 17:24
关注一下

作者: cookyliang 时间: 2009-10-30 17:25
收藏了话题

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)