调试帮助。 SQL / VBA融合 [英] Debugging help. SQL/VBA fusion
问题描述
软件： Access 2000
操作系统： Windows XP Professional SP3
问题：基于过滤器的错误2001是ModelYear< = 2006。
解决方案：表的数据类型是SQL预期数字时的文本。
我''我正在调试一些动态SQL代码的问题，我想我可能已经错过了一些重要的规则。当我尝试使用它时，会弹出一般语法错误（Runtime Error 2001）。当我有函数 Me.frmSubForm.Form.Filter = SQLFilter 时出现我的错误，即使SQLFilter =" ModelYear< = 2006"。这里是相关的功能信息：
主要功能：
错误消息是什么？在发布的各种代码中，这一行出现在哪里？
顺便提一下，SQL和一些常规调试技巧VBA包括在下面缩进：我们得到的最常见（经常发生而不是最喜欢）问题之一是在VBA代码中操作SQL字符串。
这是如此困难的原因是所有的工作都是在重定向的层面上完成的。我的意思是编码器永远不会直接使用SQL本身，而是依靠代码来依赖它来生成他们想要的SQL。它与编码器历来处理指针的问题非常相似。
无论如何，我常常建议编码人员努力解决这个问题（在任何级别。这种情况经验丰富编码器也是。）是使用 MsgBox（）函数，或者 Debug.Print 进入调试器窗口的立即窗格，以显示SQL的值。使用它之前的字符串（这是假设你通常不适合调试。我个人会追踪到该行，然后在允许执行字符串之前显示该值 - 请参阅在VBA中调试）。理解SQL字符串的意图，以及可能存在问题的地方，何时可以完整地看到它，以及它的真实形式，而不是代码即将创建它，真的要容易得多。 。
对不起，如果不清楚的话。这是2001年的错误，我发现这是Access的说法一般语法错误。固定它！我一行一行地跟着每个变量（例如strValue）的变化，一切都按照我的预期进行。我检查了SQL是否有意义并且它对我有用，但我发布这个以确保我没有错过设置过滤器的一些规则。我过去忽略的一个规则的例子是将''29'与29证明难以比较，所以我在应用引号时对数字和日期做了排除规则。
BTW，NeoPa，因为我知道你一直在关注我的进步，我以为你会喜欢这个消息。我重新编写了我在早期线程中讨论过的受版权保护的查找函数，调试它，成功测试它并添加了功能。它在上面作为 StrReplace（）函数发布。
编辑：添加错误编号并重新安排OP以便于阅读。
Software: Access 2000
Operating System: Windows XP Professional SP3
Problem: Error 2001 based on filter being "ModelYear <= 2006".
Solution: Table''s datatype was text when SQL expected number.
I''m in the middle of debugging an issue with some dynamic SQL code and I think I may have missed some important rule. When I try to use it, it pops up with a general syntax error (Runtime Error 2001). My error arises when I have the function Me.frmSubForm.Form.Filter = SQLFilter even though SQLFilter = "ModelYear <= 2006". Here''s the relevant function information:
Main function:
What''s the error message and where does this line occur within the various pieces of code posted?
By the way, some general debugging tips with SQL and VBA are included indented below :One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.
The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It''s rather similar to the problems coders have historically had dealing with pointers.
Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That''s assuming you''re not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It''s really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.
Sorry if this wasn''t clear. It''s error 2001, which I''ve found is Access''s way of saying "General syntax error. FIX IT!" I went line-by-line and followed how each variable (such as strValue) changed along the way, and it all does it as I expected. I checked to see if the SQL made sense and it did to me, but I''m posting this to make sure I didn''t miss some rule with setting a filter. An example of a rule I''ve overlooked in the past is comparing ''29'' with 29 proves difficult, so I made an exclusion rule for numbers and dates when applying quotes.
BTW, NeoPa, since I know you''ve been following my progress I thought you''d enjoy this news. I rewrote the copyrighted find function I talked about in earlier threads, debugged it, successfully tested it, and added functionality. It''s posted above as the StrReplace() function.
EDIT: added error number and rearranged OP to be an easier read.
这篇关于调试帮助。 SQL / VBA融合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
登录 关闭
