ACCESS 2003 - Using Combobox for Query Criteria

[CurtisFBuck]

I have a a form with a combobox that is supplied by the table "tblCourses ". It is provided with a list of course names

The report that is opened after one is selected, looks at all employees who are currently trained in that course.

In the criteria section, I have (under Course Name)

 [Forms]![frmTrainingLookUp]![NameofCourse]

However, when I run the report, no data shows up what so ever.

As an additional bit of troubleshooting , if I remove the criteria, it returns data, seemingly at random (i.e. it randomly selects a course to return) as well as all employee training information (i.e. not only for the selected course)

[Rabbit]

We'd have to see the code.

[CurtisFBuck]

Hi Rabbit,

What code is it you'd have to see?

Here's the code to call open the report:

Private Sub Command22_Click()
On Error GoTo Err_Command22_Click

    DoCmd.OpenReport "rptEmployeesCurrent", acViewReport

Exit_Command22_Click:
    Exit Sub

Err_Command22_Click:
    MsgBox Err.Description
    Resume Exit_Command22_Click
    
End Sub

Anything else you need to see?

[Rabbit]

The SQL code as well

[CurtisFBuck]

SELECT tblPeople.[PeopleSoft Number], tblPeople.[First Name], tblPeople.[Last Name], tblPeople.Department, tblPeople.Picture, tblPeople.Shift, tblCourses.ID, tblCourses.CourseName, tblCourses.[Renewal Period], tblCourses.Category, tblTrained.[Training ID], tblTrained.[Date Trained], IIf([Renewal Period]=0,DateSerial(Year([Date Trained])+100,Month([Date Trained]),Day([Date Trained])),IIf([Renewal Period]<1,DateSerial(Year([Date Trained]),Month([Date Trained])+[Renewal Period]*12,Day([Date Trained])),DateSerial(Year([Date Trained])+[Renewal Period],Month([Date Trained]),Day([Date Trained])))) AS [Renewal Due]
FROM tblCourses INNER JOIN (tblPeople INNER JOIN tblTrained ON tblPeople.[PeopleSoft Number] = tblTrained.[Peoplesoft Number]) ON tblCourses.ID = tblTrained.[Training ID]
WHERE (((tblCourses.CourseName)=[Forms]![frmTrainingLookup]![NameofCourse]) AND ((IIf([Renewal Period]=0,DateSerial(Year([Date Trained])+100,Month([Date Trained]),Day([Date Trained])),IIf([Renewal Period]<1,DateSerial(Year([Date Trained]),Month([Date Trained])+[Renewal Period]*12,Day([Date Trained])),DateSerial(Year([Date Trained])+[Renewal Period],Month([Date Trained]),Day([Date Trained])))))>Date()))
ORDER BY tblPeople.[PeopleSoft Number] DESC;

[Rabbit]

Aside from the course name, you have a complicated nested iif condition, what is that supposed to do?

[CurtisFBuck]

The nested iif does the following:

Calculates the date that the course needs to be renewed based upon the courses "renewal period" (which is a number 0-2, depending on how often the employee must take a course)

In general:

If the number is 0, add 100 years as the course never has to be retaken
If the number is between 0 and 1, multiply it by 12 and add it to the month.
If the number is 1 or 2, add it to the year.

In practice, this works well.

The report will populate as long as I don't have criteria under the Course Name.

[CurtisFBuck]

While it may not be the best way to do it, I found a solution...

I passed the value from the combo box to an invisible textbox on the form, and set the criteria to use the value from the textbox intsead.

[Rabbit]

Glad you found a solution.