Base: I want to use a local variable in an SQL statement

[DaleW]

OS: Windows 10 LO: Version: 6.4.6.2 (x64) File format: .odb.
NB: Variable “Status” and array “UArray” are declared globally.

The attached subroutine is called by clicking on a row in the view “vwTrain” lying within form “frmUnits”. The underlying table is “tblUnits”. The contents of column 4 are stored within variable “SelectedTrain”. This works OK.

The idea is then to query “tblUnits” to return “qryTrain”. The filter is to ensure “qryTrain” contains the records where field “BCode” = “SelectedTrain”. This is done with the SQL statement beginning “SQLText = …”

I have submitted three versions of the SQL statement: one live, the other two commented out. The live version containing the string ‘2KXBT5’ (an example of “Selected Train”) works OK, but is hardly practical. I would like to replace the string with the variable “SelectedTrain”, but this returns the error “Table not found in statement [SELECT * FROM tblUnits]”. The other commented-out version is trying to access a “hidden” text box linked to “vwTrain”. This doesn’t work either.

Any ideas? Thanks in advance.Example Code.odt

[Ratslinger]

Hello,

You do not specify what database you are using. Most macro SQL problems are because of lack of double quotes around field and table names. This depends upon the database used (again, not specified) and case used. With mixed case it is required. The variable is outside of the quoted string and included using ampersands.

See my answers in these posts:

Execute SQL with variable

Operation in a sql in a macro

[DaleW]

Hi,
I got there in the end. The delay was due to a re-organisation of my project. Yes, it was to do with the quotation marks. I was following the examples in the LO Base handbook (v6.4), but the prepared SQL statement section suggested I could remove the double quotation marks. When I put them back in, the statement worked. Surprisingly, the error messages did indicate where the problems were. By the way, I was using the internal HSQLDB database. Happy New Year.