Assuming CLPostCode is a Text type field, then:
Left(CLPostCode,4) <> 'BFPO'
works fine. You can use the single-quote character rather than the
double-quote character in a SQL context (such as FindFirst, DLookup(),
Filter, WhereCondition, ...)
The problem arises where the field data contains an apostrophy. That is not
an issue where you are inserting a literal value, and unlikely to be a
problem with a postcode anyway. However you were concatenating a name the
user entered into your string, the code would fail if the user entered
O'Conner, etc. Although the same issue does arise with the double-quote
character, it is much less common, i.e. it generally only occurs as an
abbreviation for inches or seconds (with the possible exception of memo
fields that contain actual quotations.). It is therefore safer to use the
double-quote character, and as you already know it has to be doubled-up when
embedded.
BTW, it may be more efficient to use the Like operator rather than the
function call, so Access can use the index:
CLPostcode Not Like 'BFPO*'
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Mike MacSween" <mi************************@btinternet.com> wrote in message
news:41*********************@news.aaisp.net.uk...
rstStuAddr.MoveFirst
rstStuAddr.FindFirst "CountryID = 168 AND Left(CLPostCode,4) <> 'BFPO'"
CountryID and CLPostCode are names of fields in rstStuAddr
It works. But doesn't look like it should. Cause it isn't stuffed full of
double quotes and &s.