3 Parameter Query For A Subfrom

[jagstirling]

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

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.

 

[Fernando]

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

 

[jagstirling]

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

 

[Fernando]

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

 

[EMP]

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

^

 

[jagstirling]

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