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,