Date,Date, Gosh I need 1!

[Jack]

I have a main form that display employee info. Inside of the main form I have
a subform that displays the jobs completed based on [empID]. It displays ALL
of the jobs in descending order of [jobdate]

What I would like to do is add a combo box to subform that automatically
computes dates based on todays date. For example when user clicks the drop
down it will display:
"2 weeks ago" & date 2 weeks ago
"1 month ago" & 1 month ago
"3 months ago" etc..

I would like to be able to filter the jobs completed based on the combo. I
am having difficulty creating the combo and getting that info into
lookup/query.

Any help would be greatly appreciated.
Thanks,
Jack

 

[John Smith]

Create a table of the offsets, units and descriptions:

Offset Unit Description
14 d 2 weeks ago
1 m 1 month ago
3 m 3 months ago

You can the write a query using it and the current date:

SELECT DateAdd(Unit, -Offset, Date()), Description
FROM Offsets

to use as the RowSource of your Combo. You can set the width of column one to
zero so that the user sees only the description and use the value of the combo
in your filter.

HTH
John

 

[jackfoy]

John,

That was perfect. I added a couple of tweaks that I thought I may
share. First I added an additional col. to offset table. The order col.
will allow me to sort according to number in table. I didnt want to
sort off of ID because records may not be entered in correct order.
This works beatifully. Second I set query to get the offset directly
from form. I added the filterID to query and set the criteria to:
[Forms]![frmemployee]![eval_filter]. I then added criteria to the
eval_date field -

=DateAdd([tbloffsets]![Unit],-[tbloffsets]![Offset],Date()). This works perfect. I also set on change property within combo to requery form based on empid. Otherwise when you went to new emploee and tried to use filter - it would either not display anything or would requiry and display the FIRST emp.

Everything works perfect. I would though like to ask how you would
handle adding a "ALL" option to display all jobs - instead of
filtering. What I did was add a field in offset table called all I set
the unit as yyyy and offset by 35 thus minus 35 years. I dont know if
there is a more proper method but this seems to work for me. I really
appreciate your help.

P.S I use a lot of queries that use the criteria from forms. Is there
some type of if statement that I can add to criteria that says if
[form]![field] is null then criteria is blank (show all records) else
criteria = [form]![field]
Thanks again for your help!
Jack

Create a table of the offsets, units and descriptions:

Offset Unit Description
14 d 2 weeks ago
1 m 1 month ago
3 m 3 months ago

You can the write a query using it and the current date:

SELECT DateAdd(Unit, -Offset, Date()), Description
FROM Offsets

to use as the RowSource of your Combo. You can set the width of column one to
zero so that the user sees only the description and use the value of the combo
in your filter.

HTH
John

I have a main form that display employee info. Inside of the main form I have
a subform that displays the jobs completed based on [empID]. It displays ALL
of the jobs in descending order of [jobdate]
What I would like to do is add a combo box to subform that automatically
computes dates based on todays date. For example when user clicks the drop
down it will display:
"2 weeks ago" & date 2 weeks ago
"1 month ago" & 1 month ago
"3 months ago" etc..
I would like to be able to filter the jobs completed based on the combo. I
am having difficulty creating the combo and getting that info into
lookup/query.
Any help would be greatly appreciated.
Thanks,
Jack

 

[John Smith]

Glad that it is working for you.

I usually handle the all option with a union query:

SELECT DateAdd(Unit, -Offset, Date()), Description, order_column
FROM Offsets
UNION
SELECT ' ', 'All Records', 0
FROM Offsets
ORDER BY 3

You can then test in the After Update:

Dim SQL As String
If Nz(YourCombo, " " ) = " " Then
Me.FilterOn = False
Else
SQL = "completion_date > #" & Format$(YourCombo, 'd/mmm/yyyy') & "#"
Me.Filter = SQL
Me.FilterOn = True
End If

Exactly how you arrange the dummy column will depend on which columns you are
displaying to the user, the example assumes that the first is hidden and
therefore the second will be in the combo.

HTH
John

That was perfect. I added a couple of tweaks that I thought I may
share. First I added an additional col. to offset table. The order col.
will allow me to sort according to number in table. I didnt want to
sort off of ID because records may not be entered in correct order.
This works beatifully. Second I set query to get the offset directly
from form. I added the filterID to query and set the criteria to:
[Forms]![frmemployee]![eval_filter]. I then added criteria to the
eval_date field -

=DateAdd([tbloffsets]![Unit],-[tbloffsets]![Offset],Date()). This works perfect. I also set on change property within combo to requery form based on empid. Otherwise when you went to new emploee and tried to use filter - it would either not display anything or would requiry and display the FIRST emp.

Everything works perfect. I would though like to ask how you would
handle adding a "ALL" option to display all jobs - instead of
filtering. What I did was add a field in offset table called all I set
the unit as yyyy and offset by 35 thus minus 35 years. I dont know if
there is a more proper method but this seems to work for me. I really
appreciate your help.
P.S I use a lot of queries that use the criteria from forms. Is there
some type of if statement that I can add to criteria that says if
[form]![field] is null then criteria is blank (show all records) else
criteria = [form]![field]
Thanks again for your help!
Jack

Create a table of the offsets, units and descriptions:
Offset Unit Description
14 d 2 weeks ago
1 m 1 month ago
3 m 3 months ago
You can the write a query using it and the current date:
SELECT DateAdd(Unit, -Offset, Date()), Description
FROM Offsets
to use as the RowSource of your Combo. You can set the width of column one to
zero so that the user sees only the description and use the value of the combo
in your filter.

I have a main form that display employee info. Inside of the main form I have
a subform that displays the jobs completed based on [empID]. It displays ALL
of the jobs in descending order of [jobdate]
What I would like to do is add a combo box to subform that automatically
computes dates based on todays date. For example when user clicks the drop
down it will display:
"2 weeks ago" & date 2 weeks ago
"1 month ago" & 1 month ago
"3 months ago" etc..
I would like to be able to filter the jobs completed based on the combo. I
am having difficulty creating the combo and getting that info into
lookup/query.
Any help would be greatly appreciated.