SQL syntax - Select, Filter, Left

Question

Hello,

I am trying to filter a table in query to only lookup rows with a column that contains 3 text values and return the table as so with just 2 columns. I have already filtered the columns (to 2) and sorted them.

My current Query is where plant_master is the table and plant_no is the column containing the test string I wish to filter, plant_desc should filter based off plant_no.

Current Query

SELECT plant_master.plant_no, plant_master.plant_desc#(lf)FROM pronto.plant_master plant_master#(lf)ORDER BY plant_master.plant_no"

I believe it is a case of using, SELECT, FROM, WHERE AND LEFT but can't get the syntax correct

So my table looks like this:

100	Description 100
100-01	Description 100-01
200	Description 200
200-01	Description 200-01
200-02	Description 200-02
300	Description 300

I want the query to basically produce this:

100	Description 100
200	Description 200
300	Description 300

Answer 1

you could do somehting like:

SELECT plant_master.plant_no, plant_master.plant_desc, Len([plant_master]![plant_no]) AS Expr1

FROM plant_master

WHERE (((Len([plant_master]![plant_no]))=3));

Answer 2

ciao Johnny,

it would suggest to revise the table like that :

and you could achieve your task :

applying this sql predicate :

SELECT plant_master2.plant_no, plant_master2.plant_desc, [plant_desc] & Chr(32) & [plant_no] AS plantname

FROM plant_master2

WHERE InStr([plant_no],"-")=0;

description word in your table is just an example, but I would avoid keeping plant_no  and description on the same field.

HTH.

ciao, Sandro.