NOT LIKE 2

[Kendel]

Hi All,

How do you select a field that doesn't start with a patern, something like:

Not Like "CTR*"

I've tried that but it didn't work. Thanks

 

[dhookom]

Your expression should work. Why do you think it doesn't work? You could also use:
Left([SomeField],3) <>"CTR"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Kendel]

It doesn't work because it still select those values start with "CTR". The Left function doesn't work either. Something wrong with my query. Thanks for your help.

 

[dhookom]

Something is different with your application. Are the tables linked from outside Access? Is the field a lookup field in your table?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Kendel]

I have 2 linked tables and an excel file. I link these 3 table together inside Access. Am I doing something wrong?

 

[dhookom]

You might be doing something unconventional. I don't know.

Linked Excel files might not always behave as if they were Access tables.

I repeat, "Is the field a lookup field in your table?"


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Kendel]

No it's not. Thanks

 

[dhookom]

Is the field from an Access table or the Excel file? What is your complete SQL view of your query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Kendel]

This field is from access table: Left([SomeField],3) <>"CTR"

SELECT DISTINCT Cust.*, Emp.*
FROM Cust INNER JOIN Emp ON Cust.CustID=Emp.EmpID;

I link the tables using GUI.

 

[dhookom]

There is no "where" clause in your query. I would expect it to return all records from the query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Kendel]

I'm sorry. I copied the wrong query. Here is the correct one:

SELECT DISTINCT Cust.*, Emp.*
FROM Cust INNER JOIN Emp ON Cust.CustID=Emp.EmpID
WHERE Cust.CalYr = 2004 and Cust.Type NOT LIKE 'CTR*';

OR

WHERE Cust.CalYr = 2004 and Left(Cust.Type,3) <> 'CTR';

I still get the record start with CTR. Thanks.

 

[gmmastros]

Is it possible that you have some records with a space character and then CTR, so the filter lets it pass, but when viewing, you are not noticing the space and it appears to begin with CTR?

Try Left([!]Trim([/!]Cust.Type[!])[/!], 3) <> 'CTR'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dhookom]

Good point George.
Also, do some trouble-shooting:
- can you filter anything on any field
- does this work
Left(UCase(Cust.Type),3) <> 'CTR'
- what do you notice if you sort on the type field
- what do you see if you copy the records and paste into notepad

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[straybullet]

Are the records which come up starting with CTR also from the calendar year 2004?

Let them hate - so long as they fear... Lucius Accius

 

[Kendel]

The Trim function didn't either.
Yes, I can filter on other field. It works when I use Cust.Type LIKE 'CTR*'
UCase didn't work either.
I can sort this field just fine.
When I copied the records to notepad, I saw the values just like what I saw in Access.
And yes, records starts with CTR also from Cal 2004.

Thank you all. At this pint, I decided to get it done by different approach. Thank dhookom so much to keep up with me.

 

[PHV]

And this ?
WHERE Cust.CalYr = 2004 And Not (Cust.Type LIKE 'CTR*')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Kendel]

PHP, I'm nolonger working on that problem. Just wanted to give it a try but ...nah...it didn't work. Thanks anyways.

 

[dberansky]

Kendel,

Do both of your tables by any chance have the Type field?


Dmitry