Community
Showing results for 
Search instead for 
Do you mean 
Reply

Help with Advanced Query

Copper Super Contributor
Posts: 122
Country: United Kingdom

Help with Advanced Query

I'm quite adept at using advanced queries, however I've just tried a query to look up any postcode field which contains a space followed by the letter O, to pick up any incorrectly entered postcodes as the 2nd part of a UK postcode can't start with a letter, and the O is often confused with a 0 (zero) When putting in the criteria contains " O" it ignores the space and returns any record with an O in it - is there anyway to force it to include/look for a space? Thanks in advance Andy
Andy Kent
Angel Solutions (UK and NZ)
www.angelsolutions.co.uk
Platinum Elite Contributor
Posts: 6,653
Country: USA

Re: Help with Advanced Query

Well I have a solution but it'd not pretty but it does work. In the first place you don't need the advanced lookup. Simply right click on the post code field and select Lookup Post code. Use the Starts With operator and use %O for the search value. (The percent sign is the wild card for anything before an O.) You can then use the list view to identify any Post Codes where an O is the first character of the second gropu of characters. I tried several different methods and this is the best of the lot.

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Copper Super Contributor
Posts: 122
Country: United Kingdom

Re: Help with Advanced Query

Hi Roy Thanks for the tip - only problem with this is that this forms part of other checks on the postcode all in 1 hit - ie I need to use an advanced query as this isn't the only check on a valid postcode Any ideas why the SPACE isn't picked up in the advanced query? Thanks Andy
Andy Kent
Angel Solutions (UK and NZ)
www.angelsolutions.co.uk