SQL error in field select

Scriptcase 9 General
#1

I spent the day on this and I am sure the answers simple.

I am trying to build code for a Field SQL lookup

I want to find all rows in tblMusicalRuns, where the field {numberSizes} equals the field {numSizes} in the tblRange

I testes the code out in SQLBuilder, hardcoding {numSizes} with value of 5.

SELECT
tblMusicalRuns.runName,
tblRange.name,
tblRange.rangeid,
tblRange.numSizes,
tblMusicalRuns.mrid,
tblMusicalRuns.numberSizes,
tblMusicalRuns.runName
FROM
tblRange INNER JOIN tblMusicalRuns ON tblRange.numSizes = tblMusicalRuns.numberSizes
WHERE
(tblRange.numSizes >= 5)

I place this in my SQL Field Lookup, changing the 5 to a variable.
and it looks like this:

SELECT
   tblMusicalRuns.runName,
   tblRange.name,
   tblRange.rangeid,
   tblRange.numSizes,
   tblMusicalRuns.mrid,
   tblMusicalRuns.numberSizes,
   tblMusicalRuns.runName
FROM
   tblRange INNER JOIN tblMusicalRuns ON tblRange.numSizes = tblMusicalRuns.numberSizes
WHERE 
   tblRange.numSizes = {tblMusicalRuns.numberSizes}

This is what I get before the app even executes (onLoad???)

Error while accessing the database
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘’ at line 1

View SQL
SELECT tblMusicalRuns.runName, tblRange.name, tblRange.rangeid, tblRange.numSizes, tblMusicalRuns.mrid, tblMusicalRuns.numberSizes, tblMusicalRuns.runName FROM tblRange INNER JOIN tblMusicalRuns ON tblRange.numSizes = tblMusicalRuns.numberSizes WHERE tblRange.numSizes = .numbersizes

Notice

#2

The error is in the where statement
{tblMusicalRuns.numberSizes} take off the braces.
If you have a inner join you do not need the where clause.
BR

#3

If the field in your form is called numSizes, then your WHERE statement should be more like

“SELECT … WHERE numberSizes = {numSizes}”

or maybe

'SELECT …WHERE numberSizes = ’ . {numSizes}

Make sure you have the field name exactly right too - the “View SQL” error message you pasted above says you are comparing against a field in the form called numbersizes, which is different.

#4

Thank you both. I got it to work