Search by first/last name or just last name?

I have a simple page that searches an access database by first and last name. How would I set it up to search by the first and last name OR just the last name.

I tried this:

where ([player.first_name] LIKE '%' + ? + '%') and ([player.last_name] LIKE '%' + ? + '%') or  ([player.last_name] LIKE '%' + ? + '%')

but it didn't work.


Thank you. 

4/1/2008 3:09:27 PM 93655 articles. 6 followers. Follow

9 Replies

Similar Articles

[PageSpeed] 0

You just about got it, you just need a couple more parens, like this:

where (([player.first_name] LIKE '%' + ? + '%') and ([player.last_name] LIKE '%' + ? + '%')) or  ([player.last_name] LIKE '%' + ? + '%')

Please remember to click "Mark as Answer" on the posts that helped solve your issue.
4/1/2008 3:39:09 PM


It seems like that is only working if the first name you put in doesn't match a first name in the database but the last name matches. I get no results for first name blank and last name entered.

Say I put troy johnson. Troy doesn't match, but johnson does, so it pulls up all of the johnsons. If I put don't put anything in the first name textbox and last name johnson, I get no results.

4/1/2008 3:51:56 PM

You might find it easier to build your sql in code then trying to make your logic work in straight sql like the following pseudo code:

If firstname is blank then
sql += "where lastname like '%lastname%'
sql += "where firstname like '%firstname%' and lastname like '%lastname%'

4/1/2008 3:59:29 PM


The more I think about this, if all you want is to return matches when the user enters a first AND last name, and return matches when the user enters only a last name, I think you can have your WHERE clause like this: 

    ([player.first_name] LIKE '%' + ? + '%') and ([player.last_name] LIKE '%' + ? + '%')

 Assuming your parameters are something like @fname and @lname taken from user entry, if the user enters something in both, this query will look for wildcard matches to both fname and lname. If the user only enters a value in lname, this query will return all fnames (because a wilcard match to '%%' is everything) and only matching lnames.

Let me know if I'm missing something in what you're asking.


Please remember to click "Mark as Answer" on the posts that helped solve your issue.
4/1/2008 4:03:07 PM

Using this code in a query window with a similar table on my end, If @fname is set to equal an empty string '' and @lname is set to 'johnson', I get all the johnsons. If @lname is set to 'johnson' and @fname is set to 't', I get all the johnsons with a 't' in their firstname. I think that is what you are asking, let me know if not. 

@fname varchar(10),
@lname varchar(10)

select @fname = 'troy', @lname = 'johnson'

select *
from player
	last_name like '%' + @lname + '%'
	first_name like '%' + @fname + '%'

Please remember to click "Mark as Answer" on the posts that helped solve your issue.
4/1/2008 4:09:51 PM

 I understand what you're saying but it doesn't work. If I don't enter anything in the first name box I get no results. Maybe because it is bound to the textboxes?

Here is my select statement:

SelectCommand="SELECT Player.ID, Player.First_Name, Player.Last_Name, Player.Date_of_Birth, Player.Address, Player.City, Player.State, Player.Zip_Code, Player.PlayerTypeID FROM (Player INNER JOIN PlayerType ON Player.PlayerTypeID = PlayerType.ID) where

 ([player.first_name] LIKE '%' + ? + '%') and ([player.last_name] LIKE '%' + ? + '%') "
       UpdateCommand="UPDATE [Player] SET player.First_Name = ?, player.Last_Name = ?, player.Date_of_Birth = ?, player.Address = ?, player.City= ?, player.State = ?, player.Zip_Code = ?, player.PlayerTypeID = ? WHERE = ?"
        DeleteCommand="DELETE FROM Player where ID = ?" >
            <asp:ControlParameter ControlID="textbox1" Name="First_Name" PropertyName="Text" />
            <asp:ControlParameter ControlID="TextBox2" Name="Last_Name" PropertyName="Text" />

4/1/2008 4:26:06 PM

 You could just use filter parameters on your datasource then you could do First_Name like {0} in your markup.

4/1/2008 4:30:20 PM

Make sure ConvertEmptyStringToNull is False on your ControlParameters.


Please remember to click "Mark as Answer" on the posts that helped solve your issue.
4/1/2008 4:31:48 PM

 W00t, that did it. Thanks BHenry.

4/1/2008 4:33:58 PM

Similar Artilces:

Extracting first name, last name, middle name from a string
I have a variable in a stored procedure that puts the first name, last name, and middle name in the format LastName, FirstName MiddleName. (I cannot just used the columns firstname, lastname, etc. as they are not available in the stored proc.) I am trying to split this up so that it reads LastName, [new line] ~r~n FirstName + MiddleName. Any suggestions on extracting this into that format? Any help greatly appreciated. I am working with Infomaker 6.0 and Windows 2000 Thanks, Sean Actually, it's just the format LastName, FirstName so I would only need to search for the comma an...

[contacts] Option to display names in [Last name] [First name] order
I've come up with the image of the "Display by last name". Could you confirm UI? Bugzilla 916789 Thanks, Teiichiro ...

Question about usernames, last name first or first name first
I have been looking through all the settings on groupwise and i am yet to find how to change the names from last name first to first name first. My account happens to say Phil Halbasch where others says Halbasch, Phil. If anyone knows of that secret setting to modify this it would be helpful. Thanks, Phil I figured it out, sorry for wasting ur time :) FYI Click Address book Click View tab and then name format "Phil Halbasch" <> wrote in message news:FJrDb.4825$ > I have been looking th...

Separating a Full name into a First name & Last name using substring
I am trying to take a textbox that allows a full name to be entered, and then split out the name into a First Name and Last Name. I assume that I use a substring, but how? Hi, You can use the split function to divide a string based a character, ie: Dim fullname() as string = textbox1.Text.Split(" ") Dim firstname as string = fullname(0) Dim lastname as string = fullname(1) Any doubt, post your comment.Segundo Serrano P.Ing. Sistemas That worked perfectly for a textbox.  How do I split out a string that com...

Address book is displaying Last Name, First Name and messing up name completion
How do I change my address book so that the name completion looks at First Name, Last Name??? It sounds simple, but I have two computers with the Last Name, First name and it is a pain with the name completion enabled. Thanks Address Book, View, Name Format Cheers Dave -- Dave Parkes [NSCS] Occasionally resident at Two things I don't like about the firstname, lastname/lastname, firstname thing: 1) With shared address books, an individual user cannot choose which way it goes. 2) Name completion search won't find it...

First name, last name changing
does any one know why groupwise changes in the address book the email with the addresses like this one "" to "", I have this problem and when my users want to send an email, the system send them a email account error, do any one how can I solve this problem? What version/patch level? Are you using Internet Addressing or addressing rules? Is this happening to everybody? Have you emptied FC address books? -- Barry Merchant NSC Volunteer SysOp *** no email unless requested please!! *** ...

Reverse Last Name First Name
Is there a function that will reverse the order of a Persons name? We get data from different xml files with the name all together. Sometimes they are Last Name, First Name MI. Sometimes Lastname space first name space MI. Sometimes just Last Name space First Name. I am using Delphi 2010. TIA Jan B wrote: > Is there a function that will reverse the order of a Persons name? > We get data from different xml files with the name all together. > Sometimes they are Last Name, First Name MI. Sometimes Lastname > space first name space MI. Sometimes just Last Name space F...

Name completion-Extra space between first name and last name GW 6.5
Does anybody know of a way to correct a Address Book entry that has an extra space between the first name and the last name. In console One there is no indication that there is an extra space. When you type the first name you have to enter two spaces before the correct last name comes up. I have some whiners that thinks this problem is oh so dreadful. Should I just archive the mail from the problem Mailbox, delete the account, recreate it and then restore from the archive? spacey in Seattle Dick Lunceford, > In console One It really probably is in there - most li...

First.Last@ addressing dropping the last name
We've had a few emails come back as undeliverable lately which have been sent to style addresses. The response will come back saying does not exist etc. It looks as though GW is dropping the .last name portion of the email address. The original email was sent to but the response does now show the last name Our GWIA routes to an Astaro Security Gateway for AV then out to the internet. We are using GW7.01. Here is transcript of response.... This message was created automatically by the SMTP relay on mail.unive...

Simple First name last name query
Hi everyone I have a small (or i thought ) problem we get the first name and last name in one column separate by a comma " , ". We needed to separate the two. we used this SQL, but this does not work in the case we dont get the comma select substring('anu,nat',1,charindex(',','anu,nat')-1) Works select substring('anunat',1,charindex(',','anunat')-1) Does not work Can anyone help us on this Charanjiv On Fri, 02 Apr 1999 11:07:54 +0530, Charanjiv <> wrote: >Hi everyone >...

Can you sort the mailbox by first name or last name?
I had someone ask me this, and I didn't see a way to do it. The "mailbox" folder in Gwise shows FROM and you can "sort" by that But the user wanted to sort by last name, but I didn't see a way to add "last name" as a column and sort on it (at least for Mailbox). I see "Name" (which may as well be the "from" field). Anyone got a workaround or know if this can be done? You can't sort, filter is probably the best workaround Cheers Dave -- Dave Parkes [NSCS] Occasionally resident at http://support-forums.novel...

Last name/First Name field in Groupwise Client
Groupwise Client 7.0.1 (13/06/2006) I have added the fields 'Last Name' and 'First Name' to the fields displayed in my Groupwise Client. Both fields display blank, even though their corresponding fields in the Address Book are populated. Where does the client pick up these fields from, and what can I do to have these fields display in the GW client for sorting purposes? Thanks Rob Rob, > Both fields display blank, even though their corresponding fields in > the Address Book are populated. > > Where does the client pick up these fields from...

Groupwise user name in pace of first and last name
We meet a strange behaviour with groupwise client ( windows ) in caching mode. since we've chosen "caching mode", Return notification ( popup ) for tracking information display "groupwise username" in place of "first/last Name" Anyone experienced this case ? Thanks -- chabot49 ------------------------------------------------------------------------ chabot49, It appears that in the past few days you have not received a response to your posting. That concerns us, and has triggered this automated reply. Has your problem been ...

Getting first name and last name from Windows logged in user
 I'm sure there must be an easy way of getting the actual first name and last name from an Windows domain\username via the Windows Identity or Principal object, but I can't seem to figure it out. Does anyone have any examples of this? try this: HttpContext.Current.User.Identity.Name  Thanks - tried returns nothing or domain\loginName :( Please check that on the site properties window (in IIS Admin console), in the security tab, click on the modify or edit button in the authentication and anonymous access group and make sure only the "Windows Inte...

Web resources about - Search by first/last name or just last name? -

SEO, Search Engine Optimisation - Website Optimisation
Hit Search Limited - digital marketing agency specialising in search engine optimisation, affiliates, ROI and pay per click. Online marketing ...

SEO & Search Engine News : SEJ
Latest Search Engine News from the Search Engine Journal.

Search Engine Marketing (SEM), Paid Search Advertising (PPC) & Search Engine Optimization (SEO) - Search ...
The authoritative guide to search engine marketing (SEM), search engine optimization (SEO), paid search advertising (PPC) and social media marketing. ...

Search Newz :: Search Engine News
... To Fight Over Paid Links Google prepared a note about a proposal by French lawmakers and backed by French news publsihers, which want search ...

State of Search - Where Search and Social meet up
The fastest growing Search and Social blog covering strategy developments in search and social media

Google Search Appliance - Wikipedia, the free encyclopedia
its search capabilities include searching web content, other file types (e.g. html, pdf, office documents), databases ( Oracle , MySQL , Microsoft ...

Roll up, roll up: Australia’s first teppanyaki cold plate ice cream is here. - AdelaideNow Search Search ...
FORGET ice cream scoops — the coolest way to eat our favourite summer treat is scrolled to order.

Search for missing Noosa swimmer Ricky Gardner scaled back
Man fled into ocean at Noosa while being chased by police. Search crews are yet to locate a swimmer who ran into the ocean at Noosa Main Beach ...

Baby stabbing death: Accused portrayed as doting grandfather - The Courier-Mail Search Search
UPDATE: Police have charged a grandfather for the murder of his two-month-old granddaughter in their Parkinson home on Wednesday.

Police divers find body inside cliff plunge car at Queenscliff - DailyTelegraph Search Search
POLICE divers have located the body of a man in a submerged car which he’s believed to have driven off a cliff and into the ocean on the northern ...

Resources last updated: 1/11/2016 5:26:47 PM