Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Query Help! Search First and Last Names?

    Hi there, I need a bit of help with a query...

    I have a table - "Authors" with the fields "Author_First_Name" and "Author_last_name". I have a query with the same fields as well.

    On my form I want to place a text box to SEARCH for authors via either their full name, first name, or last name and show the results in a listbox.

    I know how to do it with a single text box searching single fields,but not two fields.

    Any Ideas?

    Cheers!!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    When you say "either", do you mean they choose the type of search or you want to search the whole shebang every time? On the query end it's a simple matter of concatenating your search field:

    WHERE [first] + [last] LIKE "*yourString*"
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    Not sure the SQL is totally correct, but how about this:

    SELECT * FROM AUTHORS WHERE Author_Last_Name Contains [txtboxentry] OR Author_First_Name contains [txtboxentry]

    This wouldn't work though if they entered first and last name. I think there are functions where you can look for certain characters in the text box, such as a space or comma. Then if it contains one of these, you can split the text and search each field separately.

    The easiest way would be to have two text boxes, one for first name and one for last name.

  4. #4
    Join Date
    Aug 2004
    Posts
    364
    I need it so they can search by the authors First_name only, or Last_name only, or search both together.

    For example if I entered "Jones" in the text box I get all the Jones's in the databse. Or If I entered "Billy Jones" I just get the results for Billy Jones.

    Hope this helps cuz im stuck!!

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by LisaChow
    The easiest way would be to have two text boxes, one for first name and one for last name.
    I'm going to go ahead and say the easiest way is concatenating first and last name together, then searching based on that.

    there's always more then one way to do stuff, you've got options.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Aug 2004
    Posts
    364
    Ive got the code you put in your reply in the "criteria" part of the query, is this right? Or should it go in the "field" section? What would be the correct expression to write and where do I put it?

    Sorry im not very good at working these things out!!

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    This is basic stuff.

    You REALLY need to take the time to learn it properly. As tempting as it is to simply have people do your work for you, I'm telling you it's going to bite you in the ass later. PLEASE take the time to go through a few basic tutorials. If you don't know how to use the supplied where clause, you need to start at square 1.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Aug 2004
    Posts
    364
    Great help thanks! Ill start learning right now

  9. #9
    Join Date
    Sep 2004
    Posts
    36

    From listbox example

    Hey

    Created a small example for you to play with...

    Look under the listbox properties to see the query.

    Any questions please ask.
    Attached Files Attached Files

  10. #10
    Join Date
    Aug 2004
    Posts
    364
    Thanks Jedi thats great help Much appreciated, its much easier to see an example working than when people try to describe it to you!

    Cheers

    Tom

  11. #11
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by Teddy
    This is basic stuff.

    You REALLY need to take the time to learn it properly. As tempting as it is to simply have people do your work for you, I'm telling you it's going to bite you in the ass later. PLEASE take the time to go through a few basic tutorials. If you don't know how to use the supplied where clause, you need to start at square 1.
    Thanks again Teddy,

    It seems you're catching on too that so many here don't really have a good footing on the Basics of database design and are going at it all wrong. And it WILL bite them in the ass someday...like when the boss needs something corrected RIGHT NOW...and you have no concept of what the solution is. Not sure you can tell the boss to "hey, let me ask someone else and get back to you in a day or so..." NOT. Set the foundation first by Reading and Learning just the mere basics...it won't hurt any and will enrich your experiences later on. Everyone, have a HAPPY NEW YEAR!!!!!!!!!!!!!!!!
    Let's do it again in 2005
    BUD

  12. #12
    Join Date
    Aug 2004
    Posts
    364
    Hey no-one is asking for your opinion on how to learn anything. Myself (and clearly many hundreds of others) just want a bit of help with small problems (which someone else - "thejedi" gave me very kindly and helped enormously). For someone who already knows how to do databases it is very easy for you to say "go to square one and learn the basics..", but Im sure you guys had to learn somewhere too, and had to ask for help along the way and there IS NO HARM IN ASKING FOR HELP AND ADVICE. How else do we move forwards??

    If someone has a small problem with a database they should be able to come here to ask for help - thats what this forum is for.It id not a forum to be told by some smart-arse that I should go back to square one.

    I thought this forum is for all levels of ability and knowledge? Questions should be answered in an un-biased and neutral way.

    If you want to help people to learn databases may I suggest you become a teacher and move your postings to that sort of forum and keep your opinions to yourself and just stick to the question asked. Also, if you do have opinions about "how" I should learn databases, you tell them directly to me, not post them here.

    I am very greatful for all the responses to my question, apart from the "go back to basics" remarks because if that is your answer, then lets shut the forum down totally.

  13. #13
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Hey there,

    Didn't mean to cause any ill-will here. On my behalf I think this also is a good forum and have learned a lot from the people here. That's why I myself do my best to help others. You may not know it, but in getting someone to go to the root of the matter IS help indeed. I know you don't know this, but I am with a few forums and have actually helped a great many of people willingly and happily. But what I have found, out as many others have, is often times you give the person the answer on a silver platter and they just don't understand it still, and then run into trouble when they run into that problem again. Also, there have been many who have come for HELP with what was thought to be a small problem, yet when given the answers repeatedly, they would come back for more help because they didn't understand the BASICS. You don't start out building a 30-story skyrise by trying your best to get the 8 floor up and standing before you get the foundation done first. That's all that's being said. Some are trying to do just that without realizing that they should perhaps go to square one FIRST...and usually they will get the answer or at least better understanding.
    Also, you don't know that I have literally re-designed a few databases for posters...WILLINGLY, EAGERLY and without a grimace, when they had NO CLUE as to setting up Table Relationships. THAT is something you learn (or should) early on BEFORE you go about trying to learn complex code and then confused why your data is not showing up like you think it should. I enjoy helping others, as you're right, that's what this is all about.. Izy, Teddy and others are really great people and have helped countless people, so we do understand. You know the saying..."give a man a fish, feed him......" well, you know. Anyway, no harm meant here. It's a new year and starting with a smile. The creator allowed me to be here and I am very happy for it.

    Happy New Year to ALL the posters on this forum,
    BUD
    ....btw....I was also told early on to ....GO READ A BOOK... and so I did.

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The fact that you responded hostily to the suggestion of seeking a basic footing before tackling more advanced concepts is the type of scenario I was warning of.

    It's a simple teach a man to fish situation.

    If someone came to me and asked for help on their calculus homework and I come to find out they can't add, what am I supposed to do? I can tell them what to write in the blank, or I can attempt to goad them into REALLY understanding the solution so they can arrive at dynamic solutions for themselves in the future.

    I would still urge you to be receptive to the idea of seeking fundamental skills. You will gain SO much more from the solutions given here, even those to other people's questions when you have a solid footing. I try to be unbiased in both giving and receiving criticism. I like many others was told that I need to get a solid hold on the basics before hitting the code, I did as such and I was throttled by how much more substance I was able to take from each answer received.

    I was attempting to advance the proposition as gently as possible. This is a learning/teaching forum. Your assertion that it is anything but make it appear you have a distorted idea of what we do here. We attempt to help people understand solutions. We do not simply provide them. Providing solutions on a silver platter is called "consulting". "Consulting" is expensive. If you would like my solutions served to you with no effort on your part as a "consultant", then feel free to pm me with a rate inquiry.

    I would suggest if you are looking for consultants, then perhaps you are the one in the wrong place?

    **for the record, the right place would be here.
    Last edited by Teddy; 01-03-05 at 11:16.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •