6 Replies - 1751 Views - Last Post: 22 May 2006 - 05:55 PM Rate Topic: -----

#1 thorny  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 17-May 06

Database Search

Posted 17 May 2006 - 06:22 PM

I have a problem I hope you can help me out with. I have designed a database form in access and the user can enter a persons details that they know and searches the database and outputs the name and complete details stored I have achieved this so far but the user must put in the exact full name with corect spelling in order for it to be displayed. I am looking for the code to make the program make a list of all the people with any part of the word.
eg, Name entered: Jo
Search include: Jo, John, Johothan, Joanna, etc
could you please help asap

This post has been edited by thorny: 17 May 2006 - 06:32 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Database Search

#2 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Database Search

Posted 17 May 2006 - 06:57 PM

I took the liberty of deleting your other post as it was a duplicate...you only need to post in one section.

From your description, what you are referring to are wildcard characters...using an Access database the ones you need are:
? - finds a single character
* - multiple characters
# - single numbers

So if Jo were entered, you would search for Jo*...this will return anything that begins with Jo, just like *Jo would return anything that ends with Jo.

Here is a table that specified the wildcards for an Access database:
http://office.micros....aspx#Reference
Was This Post Helpful? 0
  • +
  • -

#3 thorny  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 17-May 06

Re: Database Search

Posted 17 May 2006 - 11:01 PM

Amadeus, on 17 May, 2006 - 05:49 PM, said:

I took the liberty of deleting your other post as it was a duplicate...you only need to post in one section.

From your description, what you are referring to are wildcard characters...using an Access database the ones you need are:
? - finds a single character
* - multiple characters
# - single numbers

So if Jo were entered, you would search for Jo*...this will return anything that begins with Jo, just like *Jo would return anything that ends with Jo.

Here is a table that specified the wildcards for an Access database:
http://office.micros....aspx#Reference

thanks but how do i implement that into my database, I have a form that has a text box in whicch the user can put for example jo but the are looking for a john what code can I use where it can be use in the sql code (Select; From; Where;)
Was This Post Helpful? 0
  • +
  • -

#4 thorny  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 17-May 06

Re: Database Search

Posted 17 May 2006 - 11:06 PM

I need some code that no matter what letter or word is inputed by the user anything that relates to the topic comes up. eg user input: Jo, solutions include; john jo joanna jojo etc
Was This Post Helpful? 0
  • +
  • -

#5 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Database Search

Posted 22 May 2006 - 08:51 AM

As I said, you simply have to use the wildcards in your search...if you post the code that currently performs the search, we can modify it to include the wilcard portion.

It will resemble the following:

SELECT values FROM table1 WHERE fieldname LIKE "Jo*"

Of course, your variable name will appear in the query itself.

Here is an article on using wildcards and Access in general:
http://www.fmsinc.com/tpapers/queries/
Was This Post Helpful? 0
  • +
  • -

#6 thorny  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 17-May 06

Re: Database Search

Posted 22 May 2006 - 05:23 PM

Here is my code which includes a wild card but i still cant get it to work fully, see if you can find out why





OwnersearchB
SELECT tblOwnerNames.nOwnerID, tblOwnerNames.sFirstName, tblOwnerNames.sName, tblOwner.sAddressLine1, tblOwner.sAddressLine2, tblOwner.sAddressLine3, tblOwner.sPostCode, tblMail.nChangedAddress, tblMail.[sName(New Address)], tblMail.sNewAddress1, tblMail.sNewPostcode, tblMail.sNewAddress2, tblMail.[sExclude(MailOut)]
FROM (tblMail INNER JOIN tblOwner ON tblMail.nOwnerID=tblOwner.nOwnerID) INNER JOIN tblOwnerNames ON (tblOwner.nOwnerID=tblOwnerNames.nOwnerID) AND (tblMail.nOwnerNameID=tblOwnerNames.nOwnerNameID)
WHERE (((tblOwnerNames.sName) Like (Forms!RealProperty2!txtSurname & *)) Or ((Forms!RealProperty2!txtSurname) Is Null) And ((tblOwnerNames.sFirstName) Like (Forms!RealProperty2!txtFName & *)) Or ((Forms!RealProperty2!txtFName) Is Null) And ((tblOwner.sAddressLine1) Like (Forms!RealProperty2!txtAddress & *)) Or ((tblMail.sNewAddress1) Like (Forms!RealProperty2!txtAddress & *)) Or ((Forms!RealProperty2!txtAddress) Is Null) And ((tblOwner.sAddressLine2) Like (Forms!RealProperty2!txtOwTown & *)) Or ((tblMail.sNewAddress2) Like (Forms!RealProperty2!txtOwTown & *)) Or ((Forms!RealProperty2!txtOwTown) Is Null) And ((tblOwner.sPostCode) Like (Forms!RealProperty2!txtOwPost & *)) Or ((Forms!RealProperty2!txtOwPost) Is Null));

Was This Post Helpful? 0
  • +
  • -

#7 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Database Search

Posted 22 May 2006 - 05:55 PM

When you say it's not working fully, what do you mean? Can you describe how it is lacking?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1