Using "Left" to select records

  • Thread starter Thread starter el zorro
  • Start date Start date
E

el zorro

I have an Access adp front end connected to SQL Server back end. I have a
form that pops up when the user types a person's last name. It shows all
people in our database with that last name. So far so good.

I want to make it easier on our users though, and not require that they
spell the whole name correctly, or even have to key inthe whole name. I would
like to have them just type the first 3 letters, and get all last names tha
begin with those letters. Form that list, they can easily find the person
they want.

So I tried this in the VBA statement that is executed when the control
button is clicked:

stLinkCriteria = "Lname Like'" & Left(Me![Last name field on form], 3) & "*' "
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, acFormReadOnly

It doesn't work-- it doesn't crash, it just returns zero records. I must
have the syntax wrong. Cah anyone help? Thanks mucho!
 
el zorro said:
stLinkCriteria = "Lname Like'" & Left(Me![Last name field on form], 3) & "*' "
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, acFormReadOnly
Click to expand...

SQL Server uses % instead of * for the wild card character.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
To that, I will add that the use of Left ("...", 3) seems to me as useless
here.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Tony Toews said:
el zorro said:
stLinkCriteria = "Lname Like'" & Left(Me![Last name field on form], 3) &
"*' "
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, acFormReadOnly
Click to expand...

SQL Server uses % instead of * for the wild card character.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Click to expand...
 
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please) said:
To that, I will add that the use of Left ("...", 3) seems to me as useless
here.
Click to expand...

Good point. For example is it McGregor or MacGregor? Punching in
just greg would take care of that problem.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
THanks Tony and Sylvain. Replacing the wildcard character worked:
stLinkCriteria = "Lname Like'" & Left(Me![FormLN], 3) & "%' "

Now, for ex, if the FormLN is entered as "Smidrovska" the database returns
the records for "Smidrovszka, Smirnov, and Smith." Notice that the user left
out the "z" in the spelling of the name on the form, but still got the
correct record. THis is what I want.

I tried omitting the use of "Left," but must have done it wrong:
stLinkCriteria = "Lname Like'" & Me![IntReqLN] & "%' "

When I tried this, I only get Smidrovszka if I spell the name exactly right.
Can you correct the syntax here?
 
The point Tony and Sylvain were trying to make is that it's possible that
the spelling mistake could be in the first 3 characters. What if the name
you were look for was actually Szmidrovszka, and the user typed in
Smidrovszka?

You might look into Soundex. As far as I know, SQL Server supports it out of
the box.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


el zorro said:
THanks Tony and Sylvain. Replacing the wildcard character worked:
stLinkCriteria = "Lname Like'" & Left(Me![FormLN], 3) & "%' "

Now, for ex, if the FormLN is entered as "Smidrovska" the database returns
the records for "Smidrovszka, Smirnov, and Smith." Notice that the user
left
out the "z" in the spelling of the name on the form, but still got the
correct record. THis is what I want.

I tried omitting the use of "Left," but must have done it wrong:
stLinkCriteria = "Lname Like'" & Me![IntReqLN] & "%' "

When I tried this, I only get Smidrovszka if I spell the name exactly
right.
Can you correct the syntax here?

el zorro said:
I have an Access adp front end connected to SQL Server back end. I have
a
form that pops up when the user types a person's last name. It shows all
people in our database with that last name. So far so good.

I want to make it easier on our users though, and not require that they
spell the whole name correctly, or even have to key inthe whole name. I
would
like to have them just type the first 3 letters, and get all last names
tha
begin with those letters. Form that list, they can easily find the person
they want.

So I tried this in the VBA statement that is executed when the control
button is clicked:

stLinkCriteria = "Lname Like'" & Left(Me![Last name field on form], 3) &
"*' "
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, acFormReadOnly

It doesn't work-- it doesn't crash, it just returns zero records. I must
have the syntax wrong. Cah anyone help? Thanks mucho!
Click to expand...
Click to expand...
 
Douglas J. Steele said:
The point Tony and Sylvain were trying to make is that it's possible that
the spelling mistake could be in the first 3 characters. What if the name
you were look for was actually Szmidrovszka, and the user typed in
Smidrovszka?
Click to expand...

Sylvain more than me. I was just agreeing with him as I had missed the
left in the code.

But yes, I would just let the user key in what little or as much as
they could decipher.

I'd also suggest doing the same thing on first name as well. In a
table I have of 10,000 names. "Jo" for first name and "sm" for last
name works out to only about four people.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Out of the box, Soundex would be a poor choice for names
like Szmidrovszka.

Soundex was designed for 'American' names. If you want
Russian, Israeli, or Eastern European names in general, you
would probably be better off just checking the first characters,
or you could use one of the alternatives.

(david)


Douglas J. Steele said:
The point Tony and Sylvain were trying to make is that it's possible that
the spelling mistake could be in the first 3 characters. What if the name
you were look for was actually Szmidrovszka, and the user typed in
Smidrovszka?

You might look into Soundex. As far as I know, SQL Server supports it out of
the box.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


el zorro said:
THanks Tony and Sylvain. Replacing the wildcard character worked:
stLinkCriteria = "Lname Like'" & Left(Me![FormLN], 3) & "%' "

Now, for ex, if the FormLN is entered as "Smidrovska" the database returns
the records for "Smidrovszka, Smirnov, and Smith." Notice that the user
left
out the "z" in the spelling of the name on the form, but still got the
correct record. THis is what I want.

I tried omitting the use of "Left," but must have done it wrong:
stLinkCriteria = "Lname Like'" & Me![IntReqLN] & "%' "

When I tried this, I only get Smidrovszka if I spell the name exactly
right.
Can you correct the syntax here?

el zorro said:
I have an Access adp front end connected to SQL Server back end. I have
a
form that pops up when the user types a person's last name. It shows all
people in our database with that last name. So far so good.

I want to make it easier on our users though, and not require that they
spell the whole name correctly, or even have to key inthe whole name. I
would
like to have them just type the first 3 letters, and get all last names
tha
begin with those letters. Form that list, they can easily find the person
they want.

So I tried this in the VBA statement that is executed when the control
button is clicked:

stLinkCriteria = "Lname Like'" & Left(Me![Last name field on form], 3) &
"*' "
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, acFormReadOnly

It doesn't work-- it doesn't crash, it just returns zero records. I must
have the syntax wrong. Cah anyone help? Thanks mucho!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top