Forum Home
Press F1
Thread ID: 60707	2005-08-10 08:52:00	Finding a Specific String in Access 2000 Table	Dannz (1668)	Press F1

Post ID	Timestamp	Content	User
380077	2005-08-10 08:52:00	Hi, I have a database with 2 tables in it. In a form i have the main table (Which stores album details) and then the secondary table (which stores the tracks/songs on each album) as a sub-form, they are linked by an ID field. I want to be able to search for a specific song and show the album that contains that track. Im guessing i would have to do this by either using a filter or just searching the table and displaying the album name.	Dannz (1668)
380078	2005-08-10 10:32:00	Hi You can do this by creating a new query, add the albums and songs tables to the query. In your query design grid, add the album details you want to see and add the song name. In the criteria section under the song name enter the following criteria : Like "*" & [Enter part of song name] & "*" Save the query and test run it. Every time you open the query, you will be prompted for a part of the song name and the query will return the possible songs along with the album details. You won't need to enter the entire song name. You could link this query to a new form, such that any time you open the form, you are asked for part of the song name and once again the possible matches are displayed. Alternatively, you could attach the query to a button on your existing form so that it works like a search function. HTH, Andrew P.S. The SQL for the query would look something like this (depending on your table and field names) : SELECT tblAlbums.Album_Artist, tblAlbums.Album_Name, tblSongs.Song_Name FROM tblAlbums INNER JOIN tblSongs ON tblAlbums.Album_ID = tblSongs.Album_Link WHERE (((tblSongs.Song_Name) Like "*" & [Enter part of song name] & "*")) WITH OWNERACCESS OPTION;	andrew93 (249)
380079	2005-08-22 09:20:00	Sorry for the late reply but as well as searching for a certain song name is there a way to look at the Album_ID Feild and find the matching album_id in a different table.	Dannz (1668)
380080	2005-08-22 11:00:00	I don't understand. Have you tried creating the query per my post? That does a search for the song name (or part thereof) and includes the album details. Is that not what you want? There are functions like DLookup but I don't think we should go there given I think you can do this with a query and plus I don't fully understand the problem. Andrew	andrew93 (249)
380081	2005-08-23 05:32:00	I have the song names as a subform and i have them linked with the Album_ID Field, how do i get the song to show with the corresponding album Thanks	Dannz (1668)
380082	2005-08-23 06:59:00	When you say 'subform' are you referring to an actual form that you have embedded inside another form? Or are you referring to the linked view you get when viewing the table? If you already have a subform, try putting the album name into the main form - that's how main forms and subforms are intended to work together - the album name (and artist etc) would appear at the top of the form and the songs would appear in the subform. There is more information available on forms and subforms here. (office.microsoft.com) Let me know if I'm still not understanding this correctly. If I am completely off track then you can use the DLookup function to return the album name based on the album id - there is more information on the DLookup function here. (msdn.microsoft.com) HTH, Andrew :)	andrew93 (249)
1