SQL WHERE LIKE query -

[#1]

i have in one table tblcustomers a field custords. in this field i have both sales orders and credits posted. Each prefixed with SO for sales order and CR for credits. I have attempt to run a query to list only credits and or only sales orders. script is pretty standard

select * from tblcustomers where custords like 'CR%';

ideally i would ike to define the return as credits in the above example.

I apologise if this has been answered elsewhere.

any pointers will be gratefully received.

[johnw]

Is this what you're asking for? I'm not sure I understand the question.

load *,mid(custords,3) as credits;
select * from tblcustomers where custords like 'CR%';

[#3 · Accepted solution]

Thanks John, sorry if the question was unclear - I hope to clarify below:-

In my Access Database I have a table tbCustomers within the table I have a column for custords - the data in the custords column reflewct both sales and credits sales are prefixed with SO ( Sales Order ) and Credits with CR. The format of each is SO12345 CR12345 each is a unique number.

I have created some impressive dashboard tabs- but a query is to product queries to list only Credits - without showing Sales Orders and Vice a Versa

This is why I thought upon using the Select Where Like Statement - In reality I am looking to undertake 2 selections from the same data source renaming each as select where queries one for CR loaded as Credits and the Other SO as Sales. As such I am looking to perform the select where statement as part of the load in order to define the result as Credits or sales.

regards

Nik

[johnw]

Sounds like this, then:

[Table]:
load *,mid(custords,3) as credits;
select * from tblcustomers where custords like 'CR%';
concatenate ([Table])
load *,mid(custords,3) as salesorders;
select * from tblcustomers where custords like 'SO%';

This might be a more efficient approach, but you'd have to test:

[Table]:
LOAD *
,recno() as ID
,left(custords,2) as type
;
SELECT * FROM tblcustomers
;
LEFT JOIN ([Table])
LOAD
ID
,mid(custords,3) as credits
RESIDENT [Table]
WHERE type = 'CR'
;
LEFT JOIN ([Table])
LOAD
ID
,mid(custords,3) as salesorders
RESIDENT

WHERE type = 'SO'
;

[#5]

Thank you John that has answered the query. I used the second script option

Thank you save me a great headache

Nik