473,326 Members | 2,173 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

what's wrong with this findfirst?

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
1 1524
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Paul | last post by:
The 2 statements below work perfectly when using them individually. But when I try to concatenate them, they don't. rst.FindFirst " = " & OldQuoteNumber rst.FindFirst " Is Null" Can someone...
1
by: jphelan | last post by:
I ran a MS Access Analyzer program that suggested changing, "rst.FindFirst strcriteria" to using the, "Seek" instead. I tried making the change. I get a Compile error that says, "Argument is not...
3
dfound
by: dfound | last post by:
#include<windows.h> #include<dos.h> #include<dir.h> #include<fstream.h> #include<stdio.h> #include<process.h> #include"resource.h" //resource header file
0
by: bitstreamer | last post by:
Greetings everybody, I'm trying to UNDERSTAND this code. I've already done lots and lots of debugging sessions and I can confirm that the FIND_NEXT routine is never executed. Every file entry...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.