End with...

[Still Dreaming]

I have data fields with Name and Surname in format like "Name Surname". How
to write query which will return all fields where surname which is typed. I
tried this, but it doesn't work:

LIKE "*" & "[type surname]"

and also:

LIKE "*" & "[surname start with]*"

 

[John W. Vinson]

I have data fields with Name and Surname in format like "Name Surname". How
to write query which will return all fields where surname which is typed. I
tried this, but it doesn't work:

LIKE "*" & "[type surname]"

This will find all records ending with the literal text string [type surname]
which is certainly not what you want!

and also:

LIKE "*" & "[surname start with]*"

IF - and it's a bad idea, parameter queries are much better pulling from a
Form Control - you want to prompt for a name and find all cases where the
response occurs anywhere within the Surname field (e.g. the user types "DON"
and you want to find ""DONNEGAL", "ELDON", "LEDONNE"), use

LIKE "*" & [type surname] & "*"

To find only names starting with the prompt response,

LIKE [type surname] & "*"

Note - no quotes around the prompt, so it's seen by Access as a prompt rather
than as a literal text string.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Ron2006]

I am not quite clear on what your name field looks like.

If it is a single field with both name and surname in it (ie. John
Doe) and you want to find all of the records that have the last name
(surname) beginning with the letter "D" then you will need to change
you query to:

LIKE "* " & [type surname] & "*"

basically you need to start the matching criteria with a space,
otherwise in this instance you will get everyone that has a "D" in
their name or surname.

Ron

 

[Ron2006]

And also if you have a person with the name of "Mike D Jones" it
will also select him.

That is why a better design ALWAYS at the least separates the surname
from the rest of the name. They can always be put back together to
form a single literal if necessary.

Ron