SQL错误3075 - 字符串变量不起作用？ [英] SQL error 3075 - string variables not working?
问题描述
我已经编写了一段时间，并且承认我是一个黑客/学习你的类型的人，所以我相信这个代码不漂亮，任何帮助将不胜感激，我一直坚持了几天，并尝试了我所知道的所有不同方式。
此代码的目标是点击：
1. check查看组合框是否为空 - 如果是，则返回关联字段的所有值
2.如果组合框有值，则将其传递给要在代码中使用的字符串值调用显示在主窗体子窗体中的查询。
当我尝试将其应用于子窗体/查询时，我在SQL中收到错误。但是，当我只使用textbox.value代替变量时，相同的代码有效 - 我怀疑这与引号有关，但无法找到我的错误。
任何帮助不胜感激。提前致谢！
-------------------------------------- ----------------------------------
Private Sub CmdRunQuery_Click（）
Dim db作为DAO.Database
Dim qdf作为DAO.QueryDef
Dim strSQL As String
Dim strBuilding As String
Dim strFloor As String
Dim strFEMASysCode As String
Dim strFEMASubSys As String
设置db = CurrentDb
设置qdf = db.QueryDefs（" qrySelectAll"）
''确定组合框是否为空或者是否已赋值，如果为空，则为应该返回所有行而不管字段的值
如果IsNull（ComboBuilding.Value）那么
strBuilding ="喜欢 ''*？
Else
strBuilding ="''" &安培; Me.BuildingText.Value& ？
结束如果
如果IsNull（ComboFloor.Value）那么
strFloor ="喜欢 ''*？
Else
strFloor ="''" &安培; Me.FloorText.Value& ？
结束如果
如果IsNull（ComboFEMASystemCSI.Value）那么
strFEMASysCode ="喜欢 ''*？
Else
strFEMASysCode ="''" &安培; Me.FEMASystemText.Value& ？
结束如果
如果是IsNull（ComboFEMASubSystemCSI.Value）那么
strFEMASubSys ="喜欢 ''*？
Else
strFEMASubSys ="''" &安培; Me.ComboFEMASubSystemCSI.Value& "？"
结束如果
''以下代码行是来自所有变量的SQL构建（在一行上）。我已经在多行和单行上尝试了它，也没有使用字符串变量。
strSQL =" SELECT * FROM wrkReportTotalsUnionAll WHERE wrkReportTotalsUnionAll。[BuildingNumber] =" &安培; strBuilding& "和wrkReportTotalsUnionAll。[Floor] =" &安培; strFloor& " AND wrkReportTotalsUnionAll。[FEMASystemCSICode] =" &安培; strFEMASysCode& " AND wrkReportTotalsUnionAll。[FEMASubSystemCSICode] =" &安培; strFEMASubSys& ORDER BY wrkReportTotalsUnionAll。[PK]，wrkReportTotalsUnionAll。[Vendor];"
''以下行替换字符串w /文本框值（具有它的相关组合框的控制源）。这行代码正常工作，除非有一个空值（即没有使用其中一个组合框。
''strSQL =" SELECT * FROM wrkReportTotalsUnionAll WHERE wrkReportTotalsUnionAll。[BuildingNumber] =' '& Me.BuildingText.Value&"''AND wrkReportTotalsUnionAll。[Floor] =''"& Me.FloorText.Value&"''AND wrkReportTotalsUnionAll。[FEMASystemCSICode] =''" ;& Me.FEMASystemText.Value&"''AND wrkReportTotalsUnionAll。[FEMASubSystemCSICode] =''"& Me.FEMASubSystemText.Value&"''ORDER BY wrkReportTotalsUnionAll。[Floor];"
MsgBox（strSQL）
qdf.SQL = strSQL
''测试底层控件源查询是否适用于子表单
DoCmd.OpenQuery" qrySelectAll"
''将查询参数推送到子窗体以显示数据结果 - 如果使用textbox.values，则此方法有效在一个问题y $ />
表格！[FrmDisplayData]！[qrySelectAll] .Form.RecordSource = strSQL
设置qdf = Nothing
设置db = Nothing
End Sub
I have been coding for a while, and admittedly am a bit of a hack / learn as you go type of person, so I am sure this code is not pretty, any help would be greatly appreciated, I''ve been stuck for a couple of days on this and have tried all the different ways I know.
The goal of this code is to on click:
1. check to see if a combo box is blank - if it is, then return all values for the associated field
2. if the combo box has a value, pass it to a string value to be used in code to call a query which is displayed in a subform of the main form.
I am receiving an error in my SQL when I try to apply it to the subform / query. However, the same code works when I simply apply a textbox.value in place of the variable - I have a suspicion this is related to quotes, but cannot find my mistake.
Any help would be appreciated. Thanks in advance!
------------------------------------------------------------------------
Private Sub CmdRunQuery_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBuilding As String
Dim strFloor As String
Dim strFEMASysCode As String
Dim strFEMASubSys As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qrySelectAll")
'' determines if combo box is null or has been assigned a value, if it is null, it should return all rows regardless of value for the field
If IsNull(ComboBuilding.Value) Then
strBuilding = " Like ''*? "
Else
strBuilding = "''" & Me.BuildingText.Value & "?"
End If
If IsNull(ComboFloor.Value) Then
strFloor = " Like ''*? "
Else
strFloor = "''" & Me.FloorText.Value & "?"
End If
If IsNull(ComboFEMASystemCSI.Value) Then
strFEMASysCode = " Like ''*? "
Else
strFEMASysCode = "''" & Me.FEMASystemText.Value & "?"
End If
If IsNull(ComboFEMASubSystemCSI.Value) Then
strFEMASubSys = " Like ''*? "
Else
strFEMASubSys = "''" & Me.ComboFEMASubSystemCSI.Value & "?"
End If
''The following line(s) of code is the SQL build from all of the variables (on one line). I have tried it on multiple lines and on a single line, neither work w/ the string variable.
strSQL = "SELECT * FROM wrkReportTotalsUnionAll WHERE wrkReportTotalsUnionAll.[BuildingNumber] = " & strBuilding & " AND wrkReportTotalsUnionAll.[Floor] = " & strFloor & " AND wrkReportTotalsUnionAll.[FEMASystemCSICode] = " & strFEMASysCode & " AND wrkReportTotalsUnionAll.[FEMASubSystemCSICode] = " & strFEMASubSys & "ORDER BY wrkReportTotalsUnionAll.[PK], wrkReportTotalsUnionAll.[Vendor];"
''The following line(s) replace the string w/ a text box value (which has a control source of its related combo box). This line of code works correctly, unless there is a null value (i.e. one of the combo boxes is not used.
''strSQL = "SELECT * FROM wrkReportTotalsUnionAll WHERE wrkReportTotalsUnionAll.[BuildingNumber] =''" & Me.BuildingText.Value & "'' AND wrkReportTotalsUnionAll.[Floor]=''" & Me.FloorText.Value & "'' AND wrkReportTotalsUnionAll.[FEMASystemCSICode]=''" & Me.FEMASystemText.Value & "'' AND wrkReportTotalsUnionAll.[FEMASubSystemCSICode]=''" & Me.FEMASubSystemText.Value & "'' ORDER BY wrkReportTotalsUnionAll.[Floor];"
MsgBox (strSQL)
qdf.SQL = strSQL
'' test to see if underlying control source query works for the subform
DoCmd.OpenQuery "qrySelectAll"
''Push the query parameters to the subform to display the data results - this works if the textbox.values are used in a query
Forms![FrmDisplayData]![qrySelectAll].Form.RecordSource = strSQL
Set qdf = Nothing
Set db = Nothing
End Sub
推荐答案
LIKE的标准SQL语法是
Standard SQL syntax for LIKE is
LIKE的标准SQL语法是
...
请注意，通配符是％而不是*
Standard SQL syntax for LIKE is
...
Note that the wildcard character is % and not *
是吗？！使用Access查询时，星号一直对我有用。也许Access允许这两者或什么？
Is it?! The asterisk has always worked for me when working with Access queries. Perhaps Access allows both, or something?
虽然对于一些更标准的数据库系统来说也是如此 - 但是对于Access来说却不是这样。
我怀疑问题是你如何编辑你的代码 - 或者更确切地说是你编辑它的内容（就像我怀疑的那样）。
Word有一个选项可以转换简单的引号（''）用于倾斜撇号。
你的代码中有一些代替了引号。
我只看到这个，因为我必须将它复制/粘贴到文本编辑器才能查看因为它不在CODE标签内。
While that is true for some, more standard, database systems - it is not true for Access.
I suspect that the problem is how you edited your code - or more precisely what you edited it in (Like Word I suspect).
Word has an option for converting simple quotes ('') for slanted apostrophes.
Your code has some of these in it in place of quotes.
I only saw this as I had to copy / paste it to a text editor to view it as it was not within CODE tags.
这篇关于SQL错误3075 - 字符串变量不起作用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
