filtering problem

[fishhead]

i have a query that has the two fields [ref] and [customer] in it.

Because of a design problem that is not reversible i have a 5 digit number in the [ref] field for all customers except 1



for this problem customer i have a 5 digit number for their individual [ref] and another "summary" [ref] number for this customer with the following format "12345-1". All i have done is added the "-1" to a 5 digit [ref].

so what i'm trying to do here is have the query return all the 5 digit [ref] for all the customers except for the records that have the [customer] "ABC CO" in which case only show the [ref] numbers for this particular company that ARE NOT 5 digits and show all the other 5 digit [ref]'s.

I was thinking if there was a way to show all records except "ABC CO" unless [ref] for "ABC CO" > 5 digits. That would work except i don't known the proper way to write this expression?

any suggestions would be greatly appreciated

thanks,

[June7]

Ref is the unique customer ID?

Maybe concatenate customer name and ref. Replace example data with actual data for this customer.

WHERE customerName & ref <> "ABC12345"

[Gicu]

Len([ref])>5 would give you the records where the ref field has more than 5 characters.

Cheers,

[fishhead]

Thanks Vlad,
that works for the records for the one specific company "ABC CO" but i need the records with the 5 digit [ref] for all the other companies that are not "ABC CO"

so the query would return something like the following:

REF CUSTOMER
12345 APPLE INC
65454 MICROSOFT
45645-1 ABC CO
54878 FACEBOOK
98787-1 ABC CO

See what i'm trying to do here?

thanks

[fishhead]

I figured something out that seems to work find, thanks everyone!

[June7]

Glad you got it working but did you see post 2? Want to share your solution?

[fishhead]

Glad you got it working but did you see post 2? Want to share your solution?

sure,

since the good & and bad data was in the same table i was building my query on i created two separate queries one to remove the bad data (5 digit ref's for the problem customer) and another leaving the correct 5 digit companies. Then i combined both queries on another query to combine the data from both into one neat query i could work with. Simple solution really i just hadn't thought of if previously.

thanks again for the suggestions!

[June7]

And the single query with my suggested filter did not work?