Search Function in Form directed to Query 1

[kentwoodjean]

I have a table with a query that represents an index to our company newsletter. Entries are made each month that contain Issue Date, Issue No., Article Title, Source, and also a column where a hyperlink is inserted and takes you to the actual newsletter in Word for the article you want. What I want to know is this: I created a form with 2 buttons, one for Article and one for Source. These will be search buttons that will open up a query based on the criteria entered. My question is on formating the criteria. In both the Article Title field and Source field, there can be either multiple names or multiple words in the article title. On my query, if I use the criteria [Enter Name], it will only pull those articles based on an exact match to the field. Because there could be multiple names but the search entry is one name, I need to format my criteria so it will pull that way. (I.e. *2004* for a date field covering several years and formated as 02/01/02). I tried [*Enter Name*] thinking just maybe it might work and of coursse it did not. Any ideas?

 

[THWatson]

kentwoodjean
If I understand your post correctly, what you want something such as the following as criteria in the Article name column...

Like [Enter Article Name or leave blank for All] & "*"

Tom

 

[THWatson]

kentwoodjean
A P.S. to my original reply...

Like [Enter Last Name or leave blank for All] & "*"
will return all articles that start with the first letter you enter.

I happened to think that perhaps you want to search for articles that include certain things but you aren't sure what the first letter is. In that case...
Like "*" & [Enter Last Name or leave blank for All] & "*"
both starts and ends with a wild card

As an example, if you were searching on a last name field, when the criteria prompts for an entry if you enter two letters th records returned would include
Bothwell
Mathews
Smith
Smothers

Tom

 

[kentwoodjean]

It all works beautifully! I knew that it probably something rather simple but I just didn't know. This will be another for the reference file.

I do have one question though. In my Source field, names are not broken out by a column of "First Name" or "Last Name". The column is titled "Source" and might have one name listed (Jane Doe) or several (Jane Doe, Bob Smith, Mickey Mouse). Using the format as you suggested, If I put in "Jane" I get nothing. If I put in Jane Doe, I get every record that has that full name listed in the Source column, whether it be alone or with others. Anyway to format the criteria so that it will bring up anything containing the first or last name if only one rather than both are entered?

 

[THWatson]

I think you need to tell me a little more about how your stuff is set up. Originally, I thought you were putting the criteria in the "Article name" column but now think you are putting the criteria in the "Source" column. The Source, I presume, is the author.

Let me know exactly what you are wanting to do and we'll work from there.

I was also wondering about another approach. That would be to put either a combo or list box on your form. The combo box could have a list of all articles in the database. You could select an article from the list, and the query would refer to the combo box. That way you wouldn't have to go fishing for an article title using the "Like * &..." structure in the query, when you didn't know the exact name of the article.

If you are working with source (author) name, the combo or list box approach would work equally well for that.

You reference the form from the query by using
Forms!YourFormName!YourComboBoxName.Column(x)

I am also wondering about how your table is set up. Have you thought that perhaps the source names need to be broken down further, say into FirstName and LastName? Sounds like a little bit of a data normalization issue. Maybe not.

Tom

 

[kentwoodjean]

Tom,

I did answer my own question by reading a bit farther in your original response, in that I needed a front end and back end wild card. I am really thrilled with the result. I a bit reluctant to go with the list box since that are currently about 3300 articles listed and still growing. In addition, it is quite normal to have more than one author.
This information has been accumulated since 2001 when it was established and has been keep on an excel spread sheet. I recently have imported it into an Access database I built which houses other reference material.

Right now its exactly what we want. Thanks so much for your help.

 

[THWatson]

Yes, 3300 articles would be a bit much for a list box, and would probably require quite a bit of searching to find the one you want.

I realize that it's common for an article to have more than one author. It would be possible to normalize the data by changing the table structure, so that no two author names appeared together in the same field but were still linked to the same article. However, given the size of your database, this would take some time to do. And if you are quite satisfied with what you have now, and can pull out what you need, then go for it.

All the best.

Tom

 

[THWatson]

I came across the following idea on Allen Browne's tips for Access users. It's an interesting way to choose what goes into a combo box at run time, and you can have just a limited number of items show up. I thought I would pass it on in case you might be interested for your application.

I haven't tried it, but thought it looked interesting.

http://members.iinet.net.au/~allenbrowne/ser-32.html

Tom

 

[godisgr8]

This will work fine with ur problem
Nm Like '*" & Trim(Enter the name field) & "*'

 

[THWatson]

Kent
Regarding that link I sent you yesterday, I had trouble translating that into something that I could get to work.

However, I did fool around with something that combines the query structure we discussed in prior posts and a combo box on a form. You probably have everything running to your satisfaction but if you are interested in experimenting with a combo box idea here's what to do...

1. Create a form based on your table. Drop a couple of fields in it - Article Title, Source.

2. Put on a text box which you will use to enter search criteria for the combo box. Let's call the text box txtSearch

3. Put on a combo box which will look up values in your table. Let's call the combo box cboSearch

4. Open the SQL behind the combo box (the row source). In the Source column, put the following criteria...
Like "*" & Forms!YourFormName!txtSearch & "*"

5. In the forms Current event, put Me.cboSearch = Null

6. In the AfterUpdate event for the txtSearch text box, put Me.cboSearch.Requery (so that the combo box gets requeried after each new entry in txtSearch)

What happens is only the values that match your search criteria in txtSearch show up in cboSearch.

If you wanted to do so, you could change the combo box to a list box, so that it was permanently open.

There may not be much, or any, advantage of this way of doing it as opposed to what you already have running. It's just a different approach...and something I found interesting because it limited the values that appear in the combo box.

Regards.
Tom

 

[kentwoodjean]

Thanks Tom, you have been a great help. Sorry so late in the response, but my computer took a dump on me Thursday night and I am just now getting it back to running.