Filtering Query based on field values

[jpgoossen via AccessMonster.com]

Hi,

I am having a hard time fool-proofing a form HOURS_ENTER.
The form is used to register hours worked by temporary employees in a table
called HOURS. First I select the employee through a combobox Select_Emp from
the table EMPLOYEES and store the EMP_ID into a field in the table. As a
visual check, I read the values for name, contract dates (BEGINDATE and
ENDDATE, for which the latter can be open, read empty) and such from the
table EMPLOYEES. Those values are, of course, not stored in HOURS, just the
ID.

Next I use a combobox Select_Week to select the week that they did their work
in. For this I have setup a large table WEEKS with all weeknumber (and dates
that comprise those weeknumbers) for the next 10 years. Of course, There is a
unique WEEKID field, next to the fields WEEKNUMBER (starts anew every year)
and WEEK_FROM and WEEK_TIL. Those last two fields contain the start and end
dates of the week in question.

Now, what I want to do is, only show the weeks/weeknumbers in my Combobox
Select_Week that fall within the period that the Contract with the employee
was in. So, can I constrain the values shown in my combobox to the date
range/weeknumbers that fall within the dates starting with BEGINDATE and up
until either the end of my table WEEKS or the week after the date in ENDDATE?

I have tried setting up a new Query for the ComboBox Select_Week with the
following expression:

SELECT WEEKS.IDWEEK, WEEKS.WEEKNUMBER, WEEKS.WEEK_FROM, WEEKS.WEEK_TIL
FROM WEEKS
WHERE (((WEEKS.WEEK_FROM)<Forms.HOURS_ENTER.Enddate) And ((WEEKS.WEEK_TIL)

Forms.HOURS_ENTER.Begindate)) Or (((WEEKS.WEEK_TIL)>[Forms].[HOURS_ENTER].

[Begindate]))
ORDER BY WEEKS.IDWEEK;

I tried to check for empty values in the Enddate field, but for now, having
the correct Begindate would be of help already.

The question is the following. Whatever I try, the ComboBox Select_Week stays
empty, not even showing the Column Headers from the Query. What am I doing
wrong here? I also tried creating a SQL statement in AfterUpdate for the
Select_Emp Combobox, but this failed with an error message concerning invalid
WHERE statements.

I am at a loss here and would appreciate any insight or help.

TIA,

Patrick

 

[SteveS]

Hi Patrick,

Lets see if I am understanding this right.....

You have a tables:
EMPLOYEES - with fields:
[EMP_ID] (long - PK?)
[BEGINDATE] (date - contract start date)
[ENDDATE] (date - contract end date - can be Null/Empty)
other fields....

WEEKS - with fields:
[WEEKID] (long - PK?)
[WEEKNUMBER] (Integer)
[WEEK_FROM] (date - start date of work week)
[WEEK_TIL] (date - end date of work week)
other fields.... (??)


ON form "HOURS_ENTER" there are controls:

Combo boxes:
Select_Emp (used to select Emp name and Start-End contract dates)
Select_Week (used to select work week start and end dates)


(here I'm guessing on the controls and names....so I'm using my names, but
trying to follow your naming as close as possible)

Text boxes:
tbCon_Begin (unbound - auto filled from Select_Emp cb)
tbCon_End (unbound - auto filled from Select_Emp cb)
other fields....


The cb "Select_Emp" has this code in the afterupdate event:

Private Sub Select_Emp_AfterUpdate()

' the column that is [WEEK_FROM]
Me.tbCon_Begin = Me.Select_Emp.Column(2)

' the column that is [WEEK_TIL]
Me.tbCon_End = Me.Select_Emp.Column(3)

'requerys combo box
Me.Select_Week.Requery

'requerys form
'Me.Requery
End Sub

You will probably need to change the column numbers for the begin and end
dates. Remember, they are zero based...




If this is close, then try this for the row source of cb "Select_Week":

(You *ARE* using a copy... Right??? or have a back up??)

SELECT weeks.IDWEEK, weeks.WEEKNUMBER, " " &
Format([week_from],"mm/dd/yyyy") & " - " & Format([week_til],"mm/dd/yyyy") AS
Weekly, weeks.WEEK_FROM, weeks.WEEK_TIL
FROM weeks
WHERE (((weeks.WEEK_FROM)>=[forms]![HOURS_ENTER].[tbCon_Begin]) AND
((weeks.WEEK_TIL)<=IIf(([weeks].[WEEK_TIL])=IsNull([forms]![HOURS_ENTER].[tbCon_End])
Or
[forms]![HOURS_ENTER].[tbCon_End]="",DateAdd("yyyy",1,Date()),[forms]![HOURS_ENTER].[tbCon_End])))
ORDER BY weeks.IDWEEK;



This part of the SQL:

" " & Format([week_from],"mm/dd/yyyy") & " - " &
Format([week_til],"mm/dd/yyyy") AS Weekly

concantates the two dates into a string so you can see both dates in the
combo box.
These are the settings for the cb:
bound column = 1
column count = 5
column widths = 0";0";1";0";0"


The hard part is the NULLs in the contract end date. In the text box, the
NULL appears as an empty string since the text box is unbound (AFAICT). What
I did was to check the text box - if it is NULL or an empty string, use
today's date and add 1 year ELSE use "tbCon_End" date.

You might have to adjust the WHERE parameters. If an employee starts (and
works) on 5/1/2007, but the first week that appears in the cb "Select_Week"
is 5/3/2007, you will need to subtract 1 (??) week to get the proper week to
appear. Change the first where clause to something like:

=DateAdd("ww",-1,[forms]![HOURS_ENTER].[tbCon_Begin])



** usual warnings apply**

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi,

I am having a hard time fool-proofing a form HOURS_ENTER.
The form is used to register hours worked by temporary employees in a table
called HOURS. First I select the employee through a combobox Select_Emp from
the table EMPLOYEES and store the EMP_ID into a field in the table. As a
visual check, I read the values for name, contract dates (BEGINDATE and
ENDDATE, for which the latter can be open, read empty) and such from the
table EMPLOYEES. Those values are, of course, not stored in HOURS, just the
ID.

Next I use a combobox Select_Week to select the week that they did their work
in. For this I have setup a large table WEEKS with all weeknumber (and dates
that comprise those weeknumbers) for the next 10 years. Of course, There is a
unique WEEKID field, next to the fields WEEKNUMBER (starts anew every year)
and WEEK_FROM and WEEK_TIL. Those last two fields contain the start and end
dates of the week in question.

Now, what I want to do is, only show the weeks/weeknumbers in my Combobox
Select_Week that fall within the period that the Contract with the employee
was in. So, can I constrain the values shown in my combobox to the date
range/weeknumbers that fall within the dates starting with BEGINDATE and up
until either the end of my table WEEKS or the week after the date in ENDDATE?

I have tried setting up a new Query for the ComboBox Select_Week with the
following expression:

SELECT WEEKS.IDWEEK, WEEKS.WEEKNUMBER, WEEKS.WEEK_FROM, WEEKS.WEEK_TIL
FROM WEEKS
WHERE (((WEEKS.WEEK_FROM)<Forms.HOURS_ENTER.Enddate) And ((WEEKS.WEEK_TIL)

Forms.HOURS_ENTER.Begindate)) Or (((WEEKS.WEEK_TIL)>[Forms].[HOURS_ENTER].

[Begindate]))
ORDER BY WEEKS.IDWEEK;

I tried to check for empty values in the Enddate field, but for now, having
the correct Begindate would be of help already.

The question is the following. Whatever I try, the ComboBox Select_Week stays
empty, not even showing the Column Headers from the Query. What am I doing
wrong here? I also tried creating a SQL statement in AfterUpdate for the
Select_Emp Combobox, but this failed with an error message concerning invalid
WHERE statements.

I am at a loss here and would appreciate any insight or help.

TIA,

Patrick

 

[jpgoossen via AccessMonster.com]

Steve,

Lets see if I am understanding this right.....

Exactly! Glad I was at least momentarily clear enough to explain what I am
trying to do.

Text boxes:
tbCon_Begin (unbound - auto filled from Select_Emp cb)
tbCon_End (unbound - auto filled from Select_Emp cb)
other fields....

The cb "Select_Emp" has this code in the afterupdate event:

Private Sub Select_Emp_AfterUpdate()

' the column that is [WEEK_FROM]
Me.tbCon_Begin = Me.Select_Emp.Column(2)

' the column that is [WEEK_TIL]
Me.tbCon_End = Me.Select_Emp.Column(3)

All is as I have set it up, except for the following.
I must admit that these two pieces of code were NOT in my original code. Are
those essential? Why?

'requerys combo box
Me.Select_Week.Requery

'requerys form
'Me.Requery

You were already very close, which saves a lot of extra explaining or grounds
for misunderstanding.

If this is close, then try this for the row source of cb "Select_Week":

(You *ARE* using a copy... Right??? or have a back up??)

Do I look stupid to you? <gggg> No, of course I make daily backups and after
every major code change, so I can always backtrack or compare. But thanks for
reminding me (and possible lurkers!).

This part of the SQL:

" " & Format([week_from],"mm/dd/yyyy") & " - " &
Format([week_til],"mm/dd/yyyy") AS Weekly

concatenates the two dates into a string so you can see both dates in the
combo box.
These are the settings for the cb:
bound column = 1
column count = 5
column widths = 0";0";1";0";0"

Here I actually used the columns of the Query, as following

bound column = 1
column count = 5
column widths = 0cm;1cm;3cm;3cm;0cm

In my original train of thought, it would just be a simple cbo, with the
columns Weeknumber, Week_from, Week_til showing. That way I could incorporate
the column headings for easier viewing pleasure of the data entry person.
Some will want to work with weeknumbers, since they have those readily
available, others would prefer to see the weeks in actual dates. If needed, I
could always concatenate those three columns in the way you describe above.
Is there any specific pro or con to any of these two methods?

The hard part is the NULLs in the contract end date. In the text box, the
NULL appears as an empty string since the text box is unbound (AFAICT). What
I did was to check the text box - if it is NULL or an empty string, use
today's date and add 1 year ELSE use "tbCon_End" date.

Yes, both text boxes (begin and end of contract dates) are unbound fields on
the form. I might want to just do a DATE() and add a week, since we will not
be filling in hour sheets for future weeks. Then again, since it is local to
this form, either would do, I would guess.

You might have to adjust the WHERE parameters. If an employee starts (and
works) on 5/1/2007, but the first week that appears in the cb "Select_Week"
is 5/3/2007, you will need to subtract 1 (??) week to get the proper week to
appear. Change the first where clause to something like:

=DateAdd("ww",-1,[forms]![HOURS_ENTER].[tbCon_Begin])

My idea was to check that the date in tbCon_End < Week_Til and the date in
tbCon_Begin > Week_From. I thought that that would be the easiest way to
ensure the right boundaries to the dates.

HTH

I am sure it will. I will work this in as soon as I have time this weekend.
Thanks for all your help and will let you know what happens or if I run into
something else. Already lost a lot of time on this project due to the stupid
bug http://support.microsoft.com/kb/824189/en-us

Thanks a lot,

Patrick

 

[jpgoossen via AccessMonster.com]

Steve,

Tried implementing this, but it gives me an error that a value is required in
the Weeks query. Which there is, but since that field is next in line to be
opened as a cbo, it is still empty. Seems that this is not the solution yet.
I have now taken out the Me.Requery.

Cheers,

Patrick

 

[jpgoossen via AccessMonster.com]

Steve or anyone else,

I am starting to think that this requires a query based on a join, somehow. I
just cannot find a way to incorporate an expression in the join, where
records are not just included when certain fields right and left are the same,
but with values of greater than or smaller than. Am I barking up the wrong
tree? IS this impossible?

TIA,

Patrick

 

[Rick Brandt]

Steve or anyone else,

I am starting to think that this requires a query based on a join,
somehow. I just cannot find a way to incorporate an expression in the
join, where records are not just included when certain fields right
and left are the same, but with values of greater than or smaller
than. Am I barking up the wrong tree? IS this impossible?

TIA,

Patrick

It is possible, but not in the graphical designer. First create a standard
equi-join and then switch to SQL view. In that view you can change the "="
to another operator and/or add expressions to the joins where you currently
have only field names. Note that the latter will be pretty inefficient
because the query engine will no longer be able use an index to do the join.

 

[jpgoossen via AccessMonster.com]

Rick,

Thanks for your reply.

It is possible, but not in the graphical designer. First create a standard
equi-join and then switch to SQL view. In that view you can change the "="
to another operator and/or add expressions to the joins where you currently
have only field names. Note that the latter will be pretty inefficient
because the query engine will no longer be able use an index to do the join.

In what sense inefficient? Will it cause lots of extra load? The people here
work on smart terminals, with most of the work done in a client server setup
under Win Server 2003. Can you recommend a different approach?

In my mind, it sounds so simple. Just ensure that the second cbo only shows
records that fall within a certain range, based on valued from the first cbo.
But somehow, I am not thinking logical enough or my lack in experience in
Access is biting me in the back.

Cheers,

Patrick

 

[SteveS]

The "Select_Week" combo box depends on the Employee combo box "Select_Emp".
When you select a different employee, "Me.Select_Week.Requery" updates the
"Select_Week" combo box (this is called cascading combo boxes).


"Me.Requery" requeries the form. I had it there (just in case), but
commented out.

Tried implementing this, but it gives me an error that a value is required in
the Weeks query. Which there is, but since that field is next in line to be

Don't know why this error....didn't get it in my test mdb.


HTH

 

[SteveS]

Hi Patrick,

Steve or anyone else,

I am starting to think that this requires a query based on a join, somehow. I
just cannot find a way to incorporate an expression in the join, where
records are not just included when certain fields right and left are the same,
but with values of greater than or smaller than. Am I barking up the wrong
tree? IS this impossible?

Maybe I don't understand after all......

Is this the process:


You select an employee using the "Select_Emp" combo box. In the afterupdate
event, two unbound text boxes are populated with the employee's contract
begin and end dates.

The two ub text boxes are parameters for the "Select_Week" combo box row
source.

Using the employee contract dates, you want to limit the dates (work weeks/
week numbers) available to be selected in the "Select_Week" combo box.


If this is right, would you post a couple of examples of employee contract
begin/end dates and the weeks that should be available in the "Select_Week"
combo box.

A simple select query should be able to generate the week numbers/dates.



HTH

 

[jpgoossen via AccessMonster.com]

Steve,

Thanks for your reply.

You select an employee using the "Select_Emp" combo box. In the afterupdate
event, two unbound text boxes are populated with the employee's contract
begin and end dates.

The two ub text boxes are parameters for the "Select_Week" combo box row
source.

Using the employee contract dates, you want to limit the dates (work weeks/
week numbers) available to be selected in the "Select_Week" combo box.

If this is right, would you post a couple of examples of employee contract
begin/end dates and the weeks that should be available in the "Select_Week"
combo box.

That seems to be exactly what I want to do.
Now let me see if I can cut and paste some sample data for you

Contractdata
WN_NAME WP_CONTRACTBEGIN WP_CONTRACTEND
Johnson 31-05-2007
French 12-04-2007 12-12-2007
Saunders 10-04-2006 12-05-2008
Zsunoma 24-05-2007
Van Buren 31-05-2007
Zesty 01-01-2007 31-12-2008

Weeknumbers data
IDWEEK WEEKNUMBER WEEK_FROM WEEK_TIL
1 1 01-01-2007 07-01-2007
2 2 08-01-2007 14-01-2007
3 3 15-01-2007 21-01-2007
4 4 22-01-2007 28-01-2007
5 5 29-01-2007 04-02-2007
6 6 05-02-2007 11-02-2007
7 7 12-02-2007 18-02-2007
8 8 19-02-2007 25-02-2007
9 9 26-02-2007 04-03-2007
10 10 05-03-2007 11-03-2007
11 11 12-03-2007 18-03-2007
12 12 19-03-2007 25-03-2007
13 13 26-03-2007 01-04-2007
14 14 02-04-2007 08-04-2007
15 15 09-04-2007 15-04-2007
16 16 16-04-2007 22-04-2007
17 17 23-04-2007 29-04-2007
18 18 30-04-2007 06-05-2007
19 19 07-05-2007 13-05-2007
20 20 14-05-2007 20-05-2007
21 21 21-05-2007 27-05-2007
22 22 28-05-2007 03-06-2007
23 23 04-06-2007 10-06-2007
24 24 11-06-2007 17-06-2007
25 25 18-06-2007 24-06-2007
26 26 25-06-2007 01-07-2007
27 27 02-07-2007 08-07-2007
28 28 09-07-2007 15-07-2007
29 29 16-07-2007 22-07-2007
Well, you get the picture...

Only the first field in each record of each table is actually saved in the
resulting table, of course, since the rest are just lookup values.

Hope this makes it clearer.

Thanks,

Patrick

 

[jpgoossen via AccessMonster.com]

Steve,

Yes, it was just the Me.Requery that gave me an error.

But as you can see a few messages back in this thread, I still can't seem to
solve the issue. Any insights?

Cheers,

Patrick

 

[SteveS]

Patrick,

Usin your data, here is what I did....


I created the two tables (of course) and a form "Form4".

On the form I added a combo box "cboEmployees" with a row source of

SELECT Contractdata.ID, Contractdata.WN_NAME, Contractdata.WP_CONTRACTBEGIN,
Contractdata.WP_CONTRACTEND
FROM Contractdata;


I added two unbound text boxes "ContractStart" and "ContractEnd".

In the afterupdate event of combo box "cboEmployees", I entered this code:

'---- beg code -----------
Private Sub cboEmployees_AfterUpdate()
'contract start date
Me.ContractStart = Me.cboEmployees.Column(2)

If Nz(Me.cboEmployees.Column(3), "") = "" Then
'calculate an end date
Me.ContractEnd = DateAdd("yyyy", 1, Me.cboEmployees.Column(2))

'or you do as you suggested - use Date() + 1 week
' Me.ContractEnd = DateAdd("d", 7, Date)
Else
' use the contract end date
Me.ContractEnd = Me.cboEmployees.Column(3)
End If


'requery the combo box to
'update the cboweek row source
Me.cboWeek.Requery
End Sub
'---- end code -----------


Next I added a combo box "cboWeek"; the row source is

SELECT Weeknumbers.IDWEEK, [weeknumber] & " : " & [week_from] & " - " &
[week_til] AS Expr1, Weeknumbers.WEEKNUMBER, Weeknumbers.WEEK_FROM,
Weeknumbers.WEEK_TIL
FROM Weeknumbers
WHERE (((Weeknumbers.WEEK_FROM)>[forms]![form4].[contractstart]) AND
((Weeknumbers.WEEK_TIL)<[forms]![form4].[contractend]));


Don't forget <g> to change "Form4" to the name of your form.


I use

[weeknumber] & " : " & [week_from] & " - " & [week_til] AS Expr1

and Column widths of 0cm;1cm;0cm;0cm;0cm

in "cboweek" to be able to see the week number, start of week and end of
week after a selection is made in the combo box. Otherwise, all you will see
is the first non zero width column. If you want column headers, put text
boxes above the combo box.


When you pick a contract employee, the two ub text boxes are populated and
the cboweek is requeried using the two ub text boxes as parameters for the
row source.


If you still have problems, email me and I will send you my example mdb.


HTH

 

[jpgoossen via AccessMonster.com]

Steve,

Thank you very much for all your insight. Must admit though that I did not do
things exactly as you proposed, but a bit different. I decided after
thoroughly studying your examples, that I could basically accomplish what I
needed if I downgraded my wishes one notch and steered some responsibility
back to the people who are to keep the employee database up to date.

I decided that th eend dat would not be an issue. If there is an actual end
date to someones contract, I would not expect any new works sheets to be
entered. If there are, well, that is for the powers that are responsible for
that to solve.

This is what I did. I just created a Query that I made the row source for the
cboWeek_Select. It sets the start date for possible weeknumbers according to
the start date of someones contract. So, especially later in the years, no
need to scroll months and months to the actual start date. It does give the
possibility to enter work sheets for date before today (otherwise I would
have settled for Now()), in case someone is away for a while and retro
actively hands in the sheets. And I decided to keep the range to one week in
the future, since no one will know when he actually works in future dates, no
matter what planning there may be.

SELECT Weeknumbers.IDWEEK, Weeknumbers.weeknumber, Weeknumbers.week_from,
Weeknumbers.week_til
FROM WEEKNUMBERS
WHERE (((Weeknumbers.WEEK_FROM)>[forms]![Hours_Enter].[contractstart]) AND
(((Weeknumbers.WEEK_TIL)<=DateAdd("d",7,Date())))

I am in doubt whether to add the end date for the contracts into this query
or not. You have shown me how to do it now, but I might, in retrospect, not
need it after all.

Anyway, thanks a lot for not only giving me great solutions to my problem,
but even better, for helping me think for myself how to handle this problem.

Great to see that the spirit of sharing knowledge and especially not looking
down on those who are just getting their feet wet in certain subjects, is
still alive and well.

Haven't solved all the problems that my Database is giving me yet, but the
level of professionalism that I have achieved thanks to the totally unselfish
support of others amazes even me. And the people I am working on this project
for as well, I might add. What started off as a "well, maybe I should create
a simple database for this task" kind of thing, has evolved into a pretty
mature project, with good usability, user friendliness and excellent
performance.

Sure, a pro will do a better job, more thought through, more elaborate error
handling and certainly a lot faster and cleaner than I have done up until now,
but we are all very thankfull for the support.

Hope to solve the last issues soon but for now,

Thanks!

Patrick