SQL Statement Hates Me (1 Viewer)

I haven't been using Access for quite a while and I am needing to do some modifications to another database and I thought it would be 'easier' in access. It would be ... if it would behave.

I know I am missing something its been too long


Keeps asking me for a ) where the trailing & is after strComp...I am going loopy on this.

Tried a variety of "'" or """ or combinations. What have I forgotten?

Because strComp is a function in MS-Access.
https://www.techonthenet.com/access/functions/string/strcomp.php
Call it something different, (ex. strComp1).

First, a really tiny nit-pick... you don't need to specify tbltTasks.iTask, you can just use iTask, because there is only one FROM table so there can be no ambiguity.

Second, you might need to set a breakpoint on the line of code that follows that statement, then in the code window, open the immediate window (under View). Type this command:

Debug.Print strSQL

Whatever it tells you is what the SQL parser was trying to parse. You can also do that for strComp. You want to verify that strComp isn't null at the time. You ALSO want to verify that strComp doesn't contain an ampersand.

Now, a couple of questions. What is strComp's data type and what is iTasks's data type?

Given that you are using "str" for a prefix for the Comp field, the use of an "i" prefix for a field in a table makes me wonder if you have a mixed mode going on here.

Third, JHB and I were answering at the same time. Good catch on the reserved name, JHB.

Didn't you get a compile error when you defined strComp?

Strangely enough, there is no compile error in the following

It's only when some operation is done on strComp
eg
that the vba editor will not accept it.

So I guess it turns out it's not SQL that hates you, it's VBA.
Mark

That worked. Thanks. As I said been a long time, been playing around learning C# but got really busy at work and stuffed something in C# so thought Access would be easy way to fix, and it is now.

Thanks

With regard to defining strComp and not getting a compile error:

The language apparently allows overloading, which means you can define things in multiple overlapping scopes and they will not cause a compilation error. However, the semantics analyzer barfs on it. So it is clearly a semantics problem.