Searching multiple memo fields in multiple tables in Access

I have a search option on my website, which should perform a search on 4 fields, as follows:

tblNews
headline
content

tblDatabank
filename
description

It only needs to return matches which are an exact match of their search criteria. For instance, searching for "I am here" would return a record which contained "I am here", but not just "I" or "I am" etc.

I need to return all these records as part of one recordset preferably, as I want to be able to order them etc., though I imagine you may suggest I use an array somehow to merge two recordets etc., then reorder them?

I hope someone can help...PLEASE!! )

In order to return one recordset containing records from two different tables, there should usually be some logical relationship between the two tables which in this case isn't esplicit.

Then you need to write an SQL statement (usually JOIN) which has a WHERE part corresponding to something like:
WHERE headline = 'I am here' AND content = 'no I am not' AND ...
or maybe you mean:
WHERE headline = 'I am here' OR content = 'I am here' OR filename = 'I am here' ...

If you manage to relate the two tables so you can have one SQL statement, you can also add an ORDER BY filename DESC to order them without an array...

I gathered that this would be the easier way forward, but am a little confused i.e. there is no relationship between the tables.

The idea, is that those records from the databank table will then require the user to register before they can view it, and the matching records from the news table will be viewable without registration. The data in the tables refers to completely different topics.

Can you explain what you mean by this logical relationship please?

I am the one who is confused, I am afraid. If the data contained is completely separate, how do you propose putting them in the same RecordSet?
Managing permissions is something completely different, done on the level of the ASP Script which accesses the database, while composing the data into a useful format is what you will be using the SQL/ADO/RecordSets to do through creative querying .
The whole point of a RecordSet is that it is a set of somehow related data, at least theoretically (what I am understanding from your description would be like the phone numbers and addresses listed together in an PIM don't necessarily correspond to the same person)
or maybe you are trying to say something that one query (open to everyone) lets you see the list of people and phone numbers, while registered guests can see a nickname and e-mail address list (which could be a completely different set of people which has no relation to the first)

Just an example to try and understand, of course.

Normally there is a relationship (a common identifying field) in two tables that will be queried together, so that all the data on the same line is tied together by that (usually a unique identifying element like an ID number, an Order Number, or a Social Security Number, which only a limited number of records will have).

Sorry, ignore my point about registering to view given fields, I can do this easily and it wasn't strictly necessary to tell you. Sorry if I added to the confusion as such!

I need to span the results (a preview of so many words..) over a number of pages, e.g. 4 results per page. This is why I thought it easier to create just one recordset rather than two somehow. From there users will be able to click the result in question to view it in its entirety.

Hence, if using two recordsets I thought perhaps I could populate an array from both recordsets and thus manipulate the data (i.e. order it or count the records etc.) returned from the query that way instead. For instance objRS.recordcount might equal 5, and objRS2.recordcount might equal 6. So that's 3 pages of results I'd need.

Hope this makes sense.

I think you're going to need two recordsets. First one for Headline and Content and second one for FileName and Description. Carry the count of Recordset1 over to Recordset2 for a total count.

As far as displaying them to the user, you can make it look like it's one set of records.

I've managed to get round it by changing the names of the fields in the two tables to both be

tagline
content

then using the following query:

strSQL = "select id as strID, tableID as strTableID, tagline as strTagline, content AS strContact FROM news where tagline LIKE '%" & searchString & "%' OR content LIKE '% " & searchString & "%' UNION ALL select id as strID, tableID as strTableID, tagline AS strTagline, content AS strContent from databank where tagline LIKE '%" & searchString & "%' OR content LIKE '%" & searchString & "%' ORDER BY strID"

thanks for your help everyone!