By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,184 Members | 1,530 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,184 IT Pros & Developers. It's quick & easy.

what's wrong with this findfirst?

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.