Not like *

[Hemish]

Hi,

I have a query which i am a bit stuck. I am using Not Like [FieldName] *. So it does not include anything from that field which is from a table called Postcode.
The PosCode table has 250 records.

The criteria

Not Like [PostCodeCheck] & "*" works with if the postcode table only has 1 record, but if i include more records in the PostCode table then this does not work.

My query is correct as it works if 1 have one record in the Post Code table. Also If i did the same query using LIKE * statement then it works and finds all the records.

Has any one got any ideas or access just limit it self to 1 record for NOT LIKE

Thanks

 

[Brianwarnock]

"this does not work"

What a very useful description of the problem

Did it produce data it shouldn't?

Did it not produce data that it should?

Brian

 

[Privateer]

When you use a wild card, it means you have part of the postal code. Your criteria looks like you are asking for 06902*, which will get you my zip code in Stamford, CT. But if you put 06* you will get all the zip codes in Connecticut. To solve this you might want to change your criteria to Not Like Left([PostCodeCheck],2) & "*", which should evaluate to (Not Like 06*).

If you are trying to find all the zip codes in one table that are not in the other table, you need to create a "Find Unmatched Query" using the wizard.

Other than that, I agree with Brian, what is the problem?

 

[Hemish]

"this does not work"

What a very useful description of the problem

Did it produce data it shouldn't?

Did it not produce data that it should?

Brian

Hi Sorry what i meant to say is it did not produce the data it should.

 

[Brianwarnock]

Any chance that you could expand on this, samples of what data was there plus what was and was not selected for the given parameter.
You may need to post a sample DB or just your query if it is not clear

Brian