Queries

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

I have a data base of names, addresses, phone numbers
etc. The names are listed by surmame then first name i.e.
Peter, Smith.

What ever surname I put in criteria of the name field
then run the query no results are shown. Why is this?
Have I done something wrong on not done something?

Many thanks for any advice.

Martin
 
Hi Martin

If your names are in the same field ie: if you have the
field like this ...

Name
Smith,Peter

or

Name
Peter,Smith

You will not get an answer cos Access looks up an exact
match for Peter or Smith

but if you type in the criteria Smith* or Peter* then it
will work. Access will look for all names beginning with
Smith,
Ideally, you should have a separate field for each name
fname and sname.
Hope this is what you are looking for.
 
THanks
-----Original Message-----
Hi Martin

If your names are in the same field ie: if you have the
field like this ...

Name
Smith,Peter

or

Name
Peter,Smith

You will not get an answer cos Access looks up an exact
match for Peter or Smith

but if you type in the criteria Smith* or Peter* then it
will work. Access will look for all names beginning with
Smith,
Ideally, you should have a separate field for each name
fname and sname.
Hope this is what you are looking for.

.
Click to expand...
 
I have a data base of names, addresses, phone numbers
etc. The names are listed by surmame then first name i.e.
Peter, Smith.

What ever surname I put in criteria of the name field
then run the query no results are shown. Why is this?
Have I done something wrong on not done something?

Many thanks for any advice.

Martin
Click to expand...

Well, clearly you're doing something wrong. However you're not telling
us what you're doing so it's a bit hard to say WHAT.

*GUESSING* in the dark here... if you have Mr. Peter listed as

"Peter, Smith" <with his rather uncommon first name>

a criterion of

"Peter"

will NOT work, because the text string "Peter" and the text string
"Peter, Smith" are not equal to one another. You can use the LIKE
operator to recognize * as a "wildcard" matching any text string:

LIKE "Peter*"

will find "Peter, Smith" and "Peterson, Roger Tory" and any other
surname.

For a bit more flexibility, use a criterion of

LIKE [Enter surname:] & ",*"

to put in the comma and the wildcard automatically.

Note that this is one key reason most developers will use separate
FirstName and Surname fields. It's much easier to combine them for
display than to tease them apart.
 
If I seperate the name field into 2 fields, First name &
surname how would I get access to display the names as
one. I am very new to access so I am trying to learn the
basics.

Thank you for your assistance
Martin
-----Original Message-----
I have a data base of names, addresses, phone numbers
etc. The names are listed by surmame then first name i.e.
Peter, Smith.

What ever surname I put in criteria of the name field
then run the query no results are shown. Why is this?
Have I done something wrong on not done something?

Many thanks for any advice.

Martin
Click to expand...

Well, clearly you're doing something wrong. However you're not telling
us what you're doing so it's a bit hard to say WHAT.

*GUESSING* in the dark here... if you have Mr. Peter listed as

"Peter, Smith" <with his rather uncommon first name>

a criterion of

"Peter"

will NOT work, because the text string "Peter" and the text string
"Peter, Smith" are not equal to one another. You can use the LIKE
operator to recognize * as a "wildcard" matching any text string:

LIKE "Peter*"

will find "Peter, Smith" and "Peterson, Roger Tory" and any other
surname.

For a bit more flexibility, use a criterion of

LIKE [Enter surname:] & ",*"

to put in the comma and the wildcard automatically.

Note that this is one key reason most developers will use separate
FirstName and Surname fields. It's much easier to combine them for
display than to tease them apart.


.
Click to expand...
 
In VBA code you would use concatenation of strings. It
looks something like this:

dim FullName, FirstName, LastName as string
FullName = LastName & ", " & FirstName

If the FirstName=Dave and the LastName=Brown the above
code snippet would give you:
Brown, Dave

Or you could go with:
FullName = FirstName & " " & LastName

which gives
Dave Brown

I don't know more about your code so I have not showed how
to get the strings into the FirstName and LastName
Variable. More than likely if you are using TSQL, you
will/should go directly against the datasource like
table.field (or something similar). So then it looks like:
FullName = MyTable.LastName & ", " & MyTable.FirstName
to get Brown, Dave.

Good Luck and good coding
-----Original Message-----
If I seperate the name field into 2 fields, First name &
surname how would I get access to display the names as
one. I am very new to access so I am trying to learn the
basics.

Thank you for your assistance
Martin
-----Original Message-----
I have a data base of names, addresses, phone numbers
etc. The names are listed by surmame then first name i.e.
Peter, Smith.

What ever surname I put in criteria of the name field
then run the query no results are shown. Why is this?
Have I done something wrong on not done something?

Many thanks for any advice.

Martin
Click to expand...

Well, clearly you're doing something wrong. However you're not telling
us what you're doing so it's a bit hard to say WHAT.

*GUESSING* in the dark here... if you have Mr. Peter listed as

"Peter, Smith" <with his rather uncommon first name>

a criterion of

"Peter"

will NOT work, because the text string "Peter" and the text string
"Peter, Smith" are not equal to one another. You can use the LIKE
operator to recognize * as a "wildcard" matching any text string:

LIKE "Peter*"

will find "Peter, Smith" and "Peterson, Roger Tory" and any other
surname.

For a bit more flexibility, use a criterion of

LIKE [Enter surname:] & ",*"

to put in the comma and the wildcard automatically.

Note that this is one key reason most developers will use separate
FirstName and Surname fields. It's much easier to combine them for
display than to tease them apart.


.
Click to expand...
.
Click to expand...
 
If I seperate the name field into 2 fields, First name &
surname how would I get access to display the names as
one. I am very new to access so I am trying to learn the
basics.
Click to expand...

You can create a Query based on your table; in a vacant Field cell put

FullName: [First name] & " " & [Surname]

to display in the format "John Vinson". This query field can be used
on a Report, on a Form, for display in a combo box, whatever you like.
 
Back
Top