Hi,

I am quite a newby to Ms Access but have had a real 'stab' at this ..... but I have hit a wall !

I have the following query which forms the basis of a subform.

I use it to select and recall records via Text box (ClientID) on a form (frmMainForm). ClientID is the Primary Key

SELECT tblClientDetails.ClientID, tblClientDetails.Title, tblClientDetails.FirstName, tblClientDetails.LastName, tblClientDetails.HouseNameNumber, tblClientDetails.Street, tblClientDetails.Town, tblClientDetails.County, tblClientDetails.Postcode, tblClientDetails.RegisterDate, tblClientType.Purchaser, tblClientType.Vendor
FROM tblClientDetails INNER JOIN tblClientType ON tblClientDetails.ClientID=tblClientType.ClientID
WHERE (((tblClientDetails.ClientID)=forms!frmMainForm!Cl ientID));

If I also wanted to search by (as well as ClientID);

LastName
Postcode


How would I change the SQL ? I think I need some Is Null statements but cannot figure out how.

I would like to able to narrow down on the required record by using a combination of the above parameters (LastName, Postcode, ClientID) in this query.

For example, LastName "Smith" is popular, but teamed with Postcode should result in just on record being returned ?

Finally, can I ask you experts, is this the best way of tackling this issue ?

Many thanks for any help or pointers.

i think you don't need to use a Where clause in ur subform Record source

Use the Link Child Fields and Link Master Fields properties of the subform object.
Link Child Fields.... ClientID
Link Master Fields. ClientID

Thanks Fernando, can you just clarify a bit ......... (sorry but I am still learning).

Is there q quicker way to write something like this (if I had 4 or 5 parameters the query woudl be huge and complex).

You can use the Filter Property i.e.
Filter.... [Inactive] = 0 ' to show the Active records
Remember you must have the FilterOn property True

If I also wanted to search by (as well as ClientID);

* LastName
* Postcode

How would I change the SQL ? I think I need some Is Null statements but cannot figure out how.

Try Jon K's basic criteria format as described in this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

^

Thanks ......... just what I was after !