Displaying results of search on dropdown boxes at a form

[Lusitan]

Hello.

I wonder if you can help me with something. I have created a Form where there are 4 drop down boxes that collect data from 3 tables. How can I use that form to search for all the records matching one or more criteria chosen from those drop down boxes and display other fields on a report or a table?

The case is:


Tables

Table_Candidates (number of candidate, name, date of birth, address,…)
Table_Professional_Habilities (professional experience, years on the job…)
Table_College_Observations (College attended, degree, years, field of expertise…)

Form – Drop Down Boxes

Professional experience
College attended
Degree
Years
Age


Result Expected (How do I get this? In a table or a report, it doesn’t make a difference)

Number of candidate
Name
Professional Experience
College Attended
Degree
Field of expertise
Age (how can I calculate this from date of birth?)

I've been advised to use Expression Builder. But the query isn't working because itreturns blank. Should I only select the fields on the Expression Builder (as an example, this is what it appears " [Forms]![New_Frm_Parameters_Selection]![Combobox17] "? Or should I make another expression?



Thank you very much.

 

[KeithWilliams]

Hi,

Here's how I would tackle it:
First create a query from the tables, selecting the columns that you want in your result set, i.e. Number of candidate, name etc. Then create a form or report based on that query.

Then in the form with the drop down boxes, put an OK button, with the script to open the result form / report. The script would be something like:
If Not IsNull(Me!ProfessionalExperience.Value) Then
stWhere = stWhere & " AND ProfessionalExperience='" & Me!ProfessionalExperience.Value & "'"
End If
If Not IsNull(Me!CollegeAttended.Value) Then
stWhere = stWhere & " AND ProfessionalExperience='" & Me!CollegeAttended.Value & "'"
End If
...
'Other where conditions here
...
stWhere = Mid(stWhere, 6) 'Strip off the leading AND
DoCmd.OpenForm "FormResult", , , stWhere

As a solution to the age calculation, you could include a field in the query for the result set:
Expr1: DateDiff("yyyy",DateOfBirth,Date())-IIf(Month(Date())<Month(DateOfBirth),1,IIf(Month(Date())=Month(DateOfBirth) And Day(Date())<Day(DateOfBirth),1,0))which will return the person's age at today's date.

This isn't very elegant and I'm sure someone can suggest a more elegant solution, but in need this will serve the purpose.

Hope that helps,
Keith.

 

[Lusitan]

And I am in need. Thank you. I'll try this and I'll get back at you with results.