[thorny]

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

[Amadeus]

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

[thorny]

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;)

[thorny]

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

[Amadeus]

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/

[thorny]

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));

[Amadeus]

When you say it's not working fully, what do you mean? Can you describe how it is lacking?