Dynamic SQL and Access Forms

[RussOSU]

I am looking for a way to take a form with some fields (Name, City, State, Age, Status, Year Hired, Certification) and set up a search in which Access would recognize which fields has an input and search for all records that match this criteria. Where I really run into a problem is that the Name, City, State, Age, Status, and Year Hired are in one table but the Certification is in another table. They are related by a common ID #. I think I have to use dynamic SQL for this however I do not know how to set up other then through the query builder how to setup the sql in Access. I have Access XP but I think it is the same as all the others.

Thanks
Russ

It isn't who or what we know rather our ability to continue to learn.

 

[LizardKingSchwing]

you could do this by first setting up a search string
do this by going through each form item and checking it for text if its empty do nothing if there is text then add the filter to the search i.e

Name: Bob

City: Dublin

State:

Age: 33

Status:

Year Hired: 1992

Certification: MCSE

the sql would be something like

"select Name, City, State, Age, Status, Year Hired, Certification from table1,table2 where table1.id = table2.id and Name = 'Bob' and City = 'Dublin' and Age = 33 and Year_Hired = 1992 and Certification = MCSE"

Not sure on table names etc. but should work

Cheers

LK--<