Operation in a sql in a macro

[hermanv]

In the SQL tool this works OK
insert into “Ledenarchief” (“archlidid”,“Naam”) select “lidid” + 1000 ,“Naam” from “Leden” where “Datum ontslag” is not null or “datum overlijden” is not null
When I want to do this in a macro (first time I do this), like

SQL = "INSERT INTO ""Ledenarchief"" " & _
"(""archlidid"" " & _
",""OKRAlidnr"" " & _
",""Naam"" " & _
") " & _
"SELECT" & _
" ""lidid"" "+1000" " & _
",""OKRAlidnr"" " & _
",""Naam"" " & _
"FROM ""Leden"" " & _
"WHERE ""Leden"".""Datum ontslag"" "is not null" "or" ""Leden"".""datum overlijden"" "is not null""

I get error BASIC syntax error. Unexpected symbol: ???. and the “+1000” is hightlighted.
So I must have made a mistake somewhere, but I ddon’t get it. Someone can help??

[torreone]

By “” “lidid” “” +1000 “” do you mean the name of a lidid1000 field or a calculated field?

In the first case I believe that “” lidid “” + cstr (1000) should be used, making sure that cstr (1000) does not start with a space that should therefore be removed with the right function (str, n).

But you would still write “” lidid1000 “” first

In the second case it seems to me that a calculated field must be written in the form (expression) as CalcFieldName.
If you mean anything else you should explain it.

The error you get indicates that +1000 is interpreted either as a field name or as an unknown operator

[hermanv]

The meaning is to add the value 1000 to the value of the lidid field (which is numeric, archlidid as well). Can you explain how to do this. I don’t find a proper explanation by googling, maybe by not using the proper terminology.

[torreone]

try to replace " ““lidid”” “+1000” " with

" ("“lidid”" + 1000) as ““fldNameYouWant”” "

[hermanv]

That does away with this error, tx, but up to the next one: same error for “WHERE ““Leden””.”“Datum ontslag”" “is not null”, and the “null” gets highlighted.

[torreone]

try
" WHERE ““Leden””."“Datum ontslag”" is not null or ““Leden””."“datum overlijden”" is not null "
at the end of your sql string command query

[hermanv]

That did it, tx a lot.

[Ratslinger]

Hello,

First @torreone has presented the correct solution to your problem and the comments should be converted to an actual answer.

But what is not covered is ‘why’ and how you could not find an answer with a search. The problem is with converting your statement into an actual string in basic. Normally this is simply surrounding the characters with quotes.

Your original statement working in Tools->SQL:

insert into "Ledenarchief" ("archlidid","Naam") select "lidid" + 1000 ,"Naam" from "Leden" where "Datum ontslag" is not null or "datum overlijden" is not null

Now create a string by surrounding with quotes:

SQL = "insert into "Ledenarchief" ("archlidid","Naam") select "lidid" + 1000 ,"Naam" from "Leden" where "Datum ontslag" is not null or "datum overlijden" is not null"

This creates a problem as once the second quote is found the string ends and the interpreter is looking for more basic syntax. Since Table and Fields names here need to be surrounded with quotes themselves, to be used within a string they need to be double quoted. For your string no other quoting is necessary and the result is:

SQL = "insert into ""Ledenarchief"" (""archlidid"",""Naam"") select ""lidid"" + 1000 ,""Naam"" from ""Leden"" where ""Datum ontslag"" is not null or ""datum overlijden"" is not null"

The need for quoting Table and Field names can be overcome depending upon the database used (you do not specify).

HSQLDB can use uppercase without quotes (no spaces). This can make SQL statements a lot easier to enter.

[hermanv]

That explains a lot to me, and BTW, it’s Firebird as I got somewhere the note that HSQLDB is (or will become) a dead end. Tx for your help.

[Ratslinger]

@hermanv,

Since you question was answered by torreone, you should mark it as the correct answer. This also helps others know the question was answered.

[torreone]

I pick up Ratslinger’s invitation and report the answer from my comments.

I didn’t have the time to try the solution in a macro so I preferred to wait for a confirmation from whoever opened the discussion

The problem is that, since the substrate sql is already enclosed in quotes, the sections +1000, not null, etc were not to be enclosed further in single quotes. In doing so they were misinterpreted.

As confirmed by Ratslinger the solution was

SQL = “insert into” “Ledenarchief” “(” “archlidid” “,” “Naam” “) select” “lidid” “+ 1000,” “Naam” “from” “Leden” “where” “Datum ontslag” " is not null or “” datum overlijden “” is not null "