CODEWITHSUNDEEP
sqlsql-server
JBSAMOM
JBSAMOM

Reputation: 331

SQL statement for TOP 3

Trying to write a SELECT Statement that will SELECT the top 3 rows from the table tblProducts WHERE the Vender ID is like the first two characters of the entered data. I decide to use the IN operation but isn't not working. I have also tried LIKE instead on the IN, but I keeping getting error message with this line. Is my logic wrong?

SELECT TOP 3
FROM tblProducts
WHERE VendorID IN UCase(Me.txtProdID.Text)

Upvotes: 0

Views: 75

Answers (2)

sgeddes
sgeddes

Reputation: 62831

I think you're just missing your column definitions after top 3. Try this instead:

SELECT TOP 3 *
FROM tblProducts
WHERE VendorID = UCase(Me.txtProdID.Text)

Also if you want to use IN, I think you need parentheses. I changed it to =.

Edit, after rereading WHERE the Vender ID is like the first two characters of the entered data. You would want to use LIKE with LEFT (or SUBSTRING), not IN for your WHERE criteria:

WHERE VendorID like Left(UCase(Me.txtProdID.Text),2) + '%'

Also, you may not need ucase -- depends on your database collation.

Upvotes: 2

luisluix
luisluix

Reputation: 579

try using parenthesis, also that .text might cause issues, add .toString(), or since its an ID, maybe toInt()

SELECT TOP (3)
FROM tblProducts
WHERE VendorID like UCase(Me.txtProdID.Text.toInt())

Upvotes: 0

Related Questions