Filtering a Combo Box based on a field on a form

[Guest]

I have a form with a sub form on it. On the parent form I have a combo box
(cboEmpName) which looks up an employee by name, and then returns the related
training they've attended in the subform. What I want to do is have another
combo box which will limit (i.e. filter) the values in the cboEmpName combo
Box based on a date field (this date, which is called “RemovedDate†in my
database, represents the date the employee left the company, if the employee
is still with the company, the field is blank).

What I’ve done is create another combo box (cboLkBkDt) with the values: “30
daysâ€, “60 daysâ€, “6 Monthsâ€, and “1 Yearâ€. Then I created an unbound text
box (txtDate) and wrote a function in the data source which calculates a date
30 days, 60 days, 6 months, or one year in the past depending on what the
user selects in the cboLkBkDt combo box, using the current date as the base
date (I used the DateDiff function to do this). This appears to be working
fine. For example, if I select “30 days†in the cboLkBkDt combo box, the
txtDate field returns a date equal to today minus 30 days.

Here’s where I begin to have issues: In the SQL view of the cboEmpName combo
box I’ve pulled in the Employee Name field and the date field (RemovedDate)
and set the criteria for the later field equal to Null or >cboLkBkDt (using
the proper syntax, i.e. Forms!frmName.[cboLkBkDt]). However, when I run the
SQL for the cboEmpName combo box I only get the records where the RemovedDate
field is null – I’m not getting those records where the date is > than the
date which was calculated and populated in the txtDate field. Anyone have
any idea what I might be doing wrong – or if there is a better way to go
about what doing I’m trying to accomplish.

Thanks,
Manuel

 

[kingston via AccessMonster.com]

You may have to enclose the date variable in # when you create your SQL
string:

stringSQL = "SELECT ... FROM ... WHERE ... > #" & Forms!frmName.[cboLkBkDt] &
"#...;"

Also (unrelated to the actual problem), you change cboLkBkDt to a simple
textbox that allows only numbers. Then you could do a calculation like this:
Date()-cboLkBkDt. This would allow greater user flexibility. HTH

I have a form with a sub form on it. On the parent form I have a combo box
(cboEmpName) which looks up an employee by name, and then returns the related
training they've attended in the subform. What I want to do is have another
combo box which will limit (i.e. filter) the values in the cboEmpName combo
Box based on a date field (this date, which is called “RemovedDate†in my
database, represents the date the employee left the company, if the employee
is still with the company, the field is blank).

What I’ve done is create another combo box (cboLkBkDt) with the values: “30
daysâ€, “60 daysâ€, “6 Monthsâ€, and “1 Yearâ€. Then I created an unbound text
box (txtDate) and wrote a function in the data source which calculates a date
30 days, 60 days, 6 months, or one year in the past depending on what the
user selects in the cboLkBkDt combo box, using the current date as the base
date (I used the DateDiff function to do this). This appears to be working
fine. For example, if I select “30 days†in the cboLkBkDt combo box, the
txtDate field returns a date equal to today minus 30 days.

Here’s where I begin to have issues: In the SQL view of the cboEmpName combo
box I’ve pulled in the Employee Name field and the date field (RemovedDate)
and set the criteria for the later field equal to Null or >cboLkBkDt (using
the proper syntax, i.e. Forms!frmName.[cboLkBkDt]). However, when I run the
SQL for the cboEmpName combo box I only get the records where the RemovedDate
field is null – I’m not getting those records where the date is > than the
date which was calculated and populated in the txtDate field. Anyone have
any idea what I might be doing wrong – or if there is a better way to go
about what doing I’m trying to accomplish.

Thanks,
Manuel