Parameter Query

[tboyce]

I have a staff data db. I am trying to record staff holidays that are
requested. I have a table called "StaffDetail" links to another table
"Holidays" with a "FromDate" field and an EndDate field along with a
totaldays taken field. I would like to enter two dates when staff request a
holiday to check if another member of staff will also be off during these
dates. Being as I have 2 fields i cannot do a between and end date, also if
someone requests for a date which falls outside the start and end date the
query may not pick up the conflicting dates. I need to know what the formula
is that will enable me to enter the dates and show me the other staff members
with conflicting holiday requests. I have read other questions but nothing
like this one. I have tried
=datediff([Enter from date] "StartDate", "d" - date()-14) but get a syntax
error. Can anybody assist me. Many thanx

 

[KARL DEWEY]

Create a table named CountNumber with field CountNUM containing numbers from
0 (zero) through your maximum holiday spread. Use these queries to show who
and what is the conflicted day. Query Requested_Conflict is set to show any
two persons on same day but you can change the >1 to other value such as

2 to show if 3 or more request same day. I used field StaffID to link

StaffDetail to Holidays.

Requested_Holidays --
SELECT DateAdd("d",[CountNUM],[FromDate]) AS Request
FROM Holidays, CountNumber
WHERE (((DateAdd("d",[CountNUM],[FromDate])) Between [FromDate] And
[EndDate]))
ORDER BY DateAdd("d",[CountNUM],[FromDate]);

Requested_Conflict --
SELECT Requested_Holidays.Request, Count(Requested_Holidays.Request) AS
CountOfExpr1
FROM Requested_Holidays
GROUP BY Requested_Holidays.Request
HAVING (((Count(Requested_Holidays.Request))>1));

SELECT Requested_Conflict.Request, StaffDetail.FName, StaffDetail.LName,
Holidays.FromDate, Holidays.EndDate
FROM Requested_Conflict, StaffDetail INNER JOIN Holidays ON
StaffDetail.StaffID = Holidays.StaffID
WHERE (((Requested_Conflict.Request) Between [FromDate] And [EndDate]))
ORDER BY Requested_Conflict.Request;

I have a staff data db. I am trying to record staff holidays that are
requested. I have a table called "StaffDetail" links to another table
"Holidays" with a "FromDate" field and an EndDate field along with a
totaldays taken field. I would like to enter two dates when staff request a
holiday to check if another member of staff will also be off during these
dates. Being as I have 2 fields i cannot do a between and end date, also if
someone requests for a date which falls outside the start and end date the
query may not pick up the conflicting dates. I need to know what the formula
is that will enable me to enter the dates and show me the other staff members
with conflicting holiday requests. I have read other questions but nothing
like this one. I have tried
=datediff([Enter from date] "StartDate", "d" - date()-14) but get a syntax
error. Can anybody assist me. Many thanx

 

[Dale Fye]

Assuming your Holiday form (frm_StaffHolidays) has textboxes (txtFromDate and
txtEndDate), then the query of your Holidays table would look something like:

SELECT StaffID, FromDate, EndDate
FROM StaffHolidays
WHERE StaffHolidays.FromDate <= Forms!frm_StaffHolidays.txtEndDate
AND StaffHolidays.EndDate >= Forms!frm_StaffHolidays.txtFromDate

This may seem counter intuitive but basically, you have 4 possible scenarios
relating to other peoples holidays.
1. Both the From and End dates fall between the new request dates
2. Both the From and End dates fall outside the new request dates
3. The From date is before the new request From date and the End date is
between the new requests From and End date
4. The From date is between the new requests From and End date and the End
date is after the new requests End date.

The only way to identify all of the staff holidays that meet any of the 4
criteria above is to use the query I provided above.

Note you may have to define parameters for the form From and End dates in
the query, if you are going to save it.

If you are going to create it on the fly, then you will probably need to add
the "#" symbol before and after the dates to ensure they are interpreted
properly. This might look like:

strSQL = "SELECT StaffID, FromDate, EndDate " _
& "FROM StaffHolidays " _
& "WHERE StaffHolidays.FromDate <= #" _
& Forms!frm_StaffHolidays.txtEndDate & "# " _
& "AND StaffHolidays.EndDate >= #" _
& Forms!frm_StaffHolidays.txtFromDate & "#"

Once you have the query, you might want to use it as the source of a list on
the form or simply to use in a DCOUNT to identify the number of people that
have conflicting holidays.

 

[tboyce]

Thank you Dale, this seems like the answer i will have to wait until tomorrow
to try it out. You kindly provided me the SQL for on the fly how would i
implement it on my form. I would like the holiday form to have a subform
where the conflicting dates would show instantly on the form. Also can you
tell me if there is a way to assess the notifications as when i click read
the response the page wont load. Thank You

Assuming your Holiday form (frm_StaffHolidays) has textboxes (txtFromDate and
txtEndDate), then the query of your Holidays table would look something like:

SELECT StaffID, FromDate, EndDate
FROM StaffHolidays
WHERE StaffHolidays.FromDate <= Forms!frm_StaffHolidays.txtEndDate
AND StaffHolidays.EndDate >= Forms!frm_StaffHolidays.txtFromDate

This may seem counter intuitive but basically, you have 4 possible scenarios
relating to other peoples holidays.
1. Both the From and End dates fall between the new request dates
2. Both the From and End dates fall outside the new request dates
3. The From date is before the new request From date and the End date is
between the new requests From and End date
4. The From date is between the new requests From and End date and the End
date is after the new requests End date.

The only way to identify all of the staff holidays that meet any of the 4
criteria above is to use the query I provided above.

Note you may have to define parameters for the form From and End dates in
the query, if you are going to save it.

If you are going to create it on the fly, then you will probably need to add
the "#" symbol before and after the dates to ensure they are interpreted
properly. This might look like:

strSQL = "SELECT StaffID, FromDate, EndDate " _
& "FROM StaffHolidays " _
& "WHERE StaffHolidays.FromDate <= #" _
& Forms!frm_StaffHolidays.txtEndDate & "# " _
& "AND StaffHolidays.EndDate >= #" _
& Forms!frm_StaffHolidays.txtFromDate & "#"

Once you have the query, you might want to use it as the source of a list on
the form or simply to use in a DCOUNT to identify the number of people that
have conflicting holidays.

----
HTH
Dale

I have a staff data db. I am trying to record staff holidays that are
requested. I have a table called "StaffDetail" links to another table
"Holidays" with a "FromDate" field and an EndDate field along with a
totaldays taken field. I would like to enter two dates when staff request a
holiday to check if another member of staff will also be off during these
dates. Being as I have 2 fields i cannot do a between and end date, also if
someone requests for a date which falls outside the start and end date the
query may not pick up the conflicting dates. I need to know what the formula
is that will enable me to enter the dates and show me the other staff members
with conflicting holiday requests. I have read other questions but nothing
like this one. I have tried
=datediff([Enter from date] "StartDate", "d" - date()-14) but get a syntax
error. Can anybody assist me. Many thanx

 

[tboyce]

Hello again Dale, I have created a main form StaffMembers and a subform
StaffHolidays with another subform which i have bound to your query sql which
i have called conflicts. When i open my main form it asks me for StartDate
and End Date parameters every time i try to view each staff member, I entered
exactly same dates for two staff members but nothing popped up to say there
was conflict either. Can you help me some more

Assuming your Holiday form (frm_StaffHolidays) has textboxes (txtFromDate and
txtEndDate), then the query of your Holidays table would look something like:

SELECT StaffID, FromDate, EndDate
FROM StaffHolidays
WHERE StaffHolidays.FromDate <= Forms!frm_StaffHolidays.txtEndDate
AND StaffHolidays.EndDate >= Forms!frm_StaffHolidays.txtFromDate

This may seem counter intuitive but basically, you have 4 possible scenarios
relating to other peoples holidays.
1. Both the From and End dates fall between the new request dates
2. Both the From and End dates fall outside the new request dates
3. The From date is before the new request From date and the End date is
between the new requests From and End date
4. The From date is between the new requests From and End date and the End
date is after the new requests End date.

The only way to identify all of the staff holidays that meet any of the 4
criteria above is to use the query I provided above.

Note you may have to define parameters for the form From and End dates in
the query, if you are going to save it.

If you are going to create it on the fly, then you will probably need to add
the "#" symbol before and after the dates to ensure they are interpreted
properly. This might look like:

strSQL = "SELECT StaffID, FromDate, EndDate " _
& "FROM StaffHolidays " _
& "WHERE StaffHolidays.FromDate <= #" _
& Forms!frm_StaffHolidays.txtEndDate & "# " _
& "AND StaffHolidays.EndDate >= #" _
& Forms!frm_StaffHolidays.txtFromDate & "#"

Once you have the query, you might want to use it as the source of a list on
the form or simply to use in a DCOUNT to identify the number of people that
have conflicting holidays.

----
HTH
Dale

I have a staff data db. I am trying to record staff holidays that are
requested. I have a table called "StaffDetail" links to another table
"Holidays" with a "FromDate" field and an EndDate field along with a
totaldays taken field. I would like to enter two dates when staff request a
holiday to check if another member of staff will also be off during these
dates. Being as I have 2 fields i cannot do a between and end date, also if
someone requests for a date which falls outside the start and end date the
query may not pick up the conflicting dates. I need to know what the formula
is that will enable me to enter the dates and show me the other staff members
with conflicting holiday requests. I have read other questions but nothing
like this one. I have tried
=datediff([Enter from date] "StartDate", "d" - date()-14) but get a syntax
error. Can anybody assist me. Many thanx

 

[Dale Fye]

If I understand you correctly, you have:

1. a main form. I assume that this is for Staff members, so it has the
staff members name, StaffID, and may have some other fields.

2. a StaffHolidays subform. I assume that this is bound to a table
(tbl_StaffHolidays) which contains fields for StaffID, StartDate, and End
date. It should have a has a child/master relationship with the main form
that is based on the StaffID field.

3. a Conflicts subform. This should contain values based on a query similar
to what I provided. This subform should have no child/master relationship
defined; but should be based on the values of the fields (StartDate and
EndDate) on the StaffHolidays subform, and you should use the Current event
of the StaffHolidays subform as well as the AfterUpdate events of the
StartDate and End date fields in that form to requery this subform.
Personally, I would probably put some code in the Current event of the
subform (sub_StaffHolidays) to test to determine whether the current record
is a new record or not. If it is, I would set the RecordSource of the
sub_Conflicts to "". Then, in the AfterUpdate events for the Start and End
dates, I would confirm that both of those values are dates, and if they are,
set the recordsource back to the query.

Finally, the query that you use for the final subform (sub_Conflicts) needs
to look something like:

SELECT StaffID, StartDate, EndDate
FROM StaffHolidays
WHERE StaffHolidays.StartDate <=
Forms!frm_Main.sub_StaffHolidays.form.txtEndDate
AND StaffHolidays.EndDate >=
Forms!frm_Main.sub_StaffHolidays.form.txtStartDate

HTH
Dale

Hello again Dale, I have created a main form StaffMembers and a subform
StaffHolidays with another subform which i have bound to your query sql
which
i have called conflicts. When i open my main form it asks me for StartDate
and End Date parameters every time i try to view each staff member, I
entered
exactly same dates for two staff members but nothing popped up to say
there
was conflict either. Can you help me some more

Assuming your Holiday form (frm_StaffHolidays) has textboxes (txtFromDate
and
txtEndDate), then the query of your Holidays table would look something
like:

SELECT StaffID, FromDate, EndDate
FROM StaffHolidays
WHERE StaffHolidays.FromDate <= Forms!frm_StaffHolidays.txtEndDate
AND StaffHolidays.EndDate >= Forms!frm_StaffHolidays.txtFromDate

This may seem counter intuitive but basically, you have 4 possible
scenarios
relating to other peoples holidays.
1. Both the From and End dates fall between the new request dates
2. Both the From and End dates fall outside the new request dates
3. The From date is before the new request From date and the End date is
between the new requests From and End date
4. The From date is between the new requests From and End date and the
End
date is after the new requests End date.

The only way to identify all of the staff holidays that meet any of the 4
criteria above is to use the query I provided above.

Note you may have to define parameters for the form From and End dates in
the query, if you are going to save it.

If you are going to create it on the fly, then you will probably need to
add
the "#" symbol before and after the dates to ensure they are interpreted
properly. This might look like:

strSQL = "SELECT StaffID, FromDate, EndDate " _
& "FROM StaffHolidays " _
& "WHERE StaffHolidays.FromDate <= #" _
& Forms!frm_StaffHolidays.txtEndDate & "# " _
& "AND StaffHolidays.EndDate >= #" _
& Forms!frm_StaffHolidays.txtFromDate & "#"

Once you have the query, you might want to use it as the source of a list
on
the form or simply to use in a DCOUNT to identify the number of people
that
have conflicting holidays.

----
HTH
Dale

I have a staff data db. I am trying to record staff holidays that are
requested. I have a table called "StaffDetail" links to another table
"Holidays" with a "FromDate" field and an EndDate field along with a
totaldays taken field. I would like to enter two dates when staff
request a
holiday to check if another member of staff will also be off during
these
dates. Being as I have 2 fields i cannot do a between and end date,
also if
someone requests for a date which falls outside the start and end date
the
query may not pick up the conflicting dates. I need to know what the
formula
is that will enable me to enter the dates and show me the other staff
members
with conflicting holiday requests. I have read other questions but
nothing
like this one. I have tried
=datediff([Enter from date] "StartDate", "d" - date()-14) but get a
syntax
error. Can anybody assist me. Many thanx

 

[TinMan]

Karl thank you for this you have obviously gone to great trouble but it
seemed more difficult for me to achieve, you know your stuff. Thank you

Create a table named CountNumber with field CountNUM containing numbers from
0 (zero) through your maximum holiday spread. Use these queries to show who
and what is the conflicted day. Query Requested_Conflict is set to show any
two persons on same day but you can change the >1 to other value such as

2 to show if 3 or more request same day. I used field StaffID to link

StaffDetail to Holidays.

Requested_Holidays --
SELECT DateAdd("d",[CountNUM],[FromDate]) AS Request
FROM Holidays, CountNumber
WHERE (((DateAdd("d",[CountNUM],[FromDate])) Between [FromDate] And
[EndDate]))
ORDER BY DateAdd("d",[CountNUM],[FromDate]);

Requested_Conflict --
SELECT Requested_Holidays.Request, Count(Requested_Holidays.Request) AS
CountOfExpr1
FROM Requested_Holidays
GROUP BY Requested_Holidays.Request
HAVING (((Count(Requested_Holidays.Request))>1));

SELECT Requested_Conflict.Request, StaffDetail.FName, StaffDetail.LName,
Holidays.FromDate, Holidays.EndDate
FROM Requested_Conflict, StaffDetail INNER JOIN Holidays ON
StaffDetail.StaffID = Holidays.StaffID
WHERE (((Requested_Conflict.Request) Between [FromDate] And [EndDate]))
ORDER BY Requested_Conflict.Request;

I have a staff data db. I am trying to record staff holidays that are
requested. I have a table called "StaffDetail" links to another table
"Holidays" with a "FromDate" field and an EndDate field along with a
totaldays taken field. I would like to enter two dates when staff request a
holiday to check if another member of staff will also be off during these
dates. Being as I have 2 fields i cannot do a between and end date, also if
someone requests for a date which falls outside the start and end date the
query may not pick up the conflicting dates. I need to know what the formula
is that will enable me to enter the dates and show me the other staff members
with conflicting holiday requests. I have read other questions but nothing
like this one. I have tried
=datediff([Enter from date] "StartDate", "d" - date()-14) but get a syntax
error. Can anybody assist me. Many thanx

 

[TinMan]

God bless you again Dale for coming to my rescue. I have got the jist of your
post and found it easy to follow, except point 3. I have broken up your post
with my queries which i found useful when getting posts from John (another
smasher like yourself). Thank you for baring with me. Tom

If I understand you correctly, you have:

Yes you have got it all bang on, Points 1 & 2 it point 3 where i little
confused. c p3

1. a main form. I assume that this is for Staff members, so it has the
staff members name, StaffID, and may have some other fields.

2. a StaffHolidays subform. I assume that this is bound to a table
(tbl_StaffHolidays) which contains fields for StaffID, StartDate, and End
date. It should have a has a child/master relationship with the main form
that is based on the StaffID field.

3. a Conflicts subform. This should contain values based on a query similar
to what I provided.

Do you mean open a blank query and post this as SQL and save it.

This subform should have no child/master relationship

defined; but should be based on the values of the fields (StartDate and
EndDate) on the StaffHolidays subform, and you should use the Current event

What needs to go in the current event?

of the StaffHolidays subform as well as the AfterUpdate events of the
StartDate and End date fields in that form to requery this subform.

So i need to enter code in both i.e Me.Requery?

Personally, I would probably put some code in the Current event of the
subform (sub_StaffHolidays) to test to determine whether the current record
is a new record or not.

What code would i need for this please??

If it is, I would set the RecordSource of the

sub_Conflicts to "".

I am lost here Dale??

Then, in the AfterUpdate events for the Start and End

dates, I would confirm that both of those values are dates,

Will not setting the properties to DTM not be sufficient here

and if they are, set the recordsource back to the query.
I am Lost here to Dale??

Finally, the query that you use for the final subform (sub_Conflicts) needs
to look something like:

Is this point the same as point 1 or something different. Thanks again Dale
you a star.

SELECT StaffID, StartDate, EndDate
FROM StaffHolidays
WHERE StaffHolidays.StartDate <=
Forms!frm_Main.sub_StaffHolidays.form.txtEndDate
AND StaffHolidays.EndDate >=
Forms!frm_Main.sub_StaffHolidays.form.txtStartDate

HTH
Dale

Hello again Dale, I have created a main form StaffMembers and a subform
StaffHolidays with another subform which i have bound to your query sql
which
i have called conflicts. When i open my main form it asks me for StartDate
and End Date parameters every time i try to view each staff member, I
entered
exactly same dates for two staff members but nothing popped up to say
there
was conflict either. Can you help me some more

Assuming your Holiday form (frm_StaffHolidays) has textboxes (txtFromDate
and
txtEndDate), then the query of your Holidays table would look something
like:

SELECT StaffID, FromDate, EndDate
FROM StaffHolidays
WHERE StaffHolidays.FromDate <= Forms!frm_StaffHolidays.txtEndDate
AND StaffHolidays.EndDate >= Forms!frm_StaffHolidays.txtFromDate

This may seem counter intuitive but basically, you have 4 possible
scenarios
relating to other peoples holidays.
1. Both the From and End dates fall between the new request dates
2. Both the From and End dates fall outside the new request dates
3. The From date is before the new request From date and the End date is
between the new requests From and End date
4. The From date is between the new requests From and End date and the
End
date is after the new requests End date.

The only way to identify all of the staff holidays that meet any of the 4
criteria above is to use the query I provided above.

Note you may have to define parameters for the form From and End dates in
the query, if you are going to save it.

If you are going to create it on the fly, then you will probably need to
add
the "#" symbol before and after the dates to ensure they are interpreted
properly. This might look like:

strSQL = "SELECT StaffID, FromDate, EndDate " _
& "FROM StaffHolidays " _
& "WHERE StaffHolidays.FromDate <= #" _
& Forms!frm_StaffHolidays.txtEndDate & "# " _
& "AND StaffHolidays.EndDate >= #" _
& Forms!frm_StaffHolidays.txtFromDate & "#"

Once you have the query, you might want to use it as the source of a list
on
the form or simply to use in a DCOUNT to identify the number of people
that
have conflicting holidays.

----
HTH
Dale



:

I have a staff data db. I am trying to record staff holidays that are
requested. I have a table called "StaffDetail" links to another table
"Holidays" with a "FromDate" field and an EndDate field along with a
totaldays taken field. I would like to enter two dates when staff
request a
holiday to check if another member of staff will also be off during
these
dates. Being as I have 2 fields i cannot do a between and end date,
also if
someone requests for a date which falls outside the start and end date
the
query may not pick up the conflicting dates. I need to know what the
formula
is that will enable me to enter the dates and show me the other staff
members
with conflicting holiday requests. I have read other questions but
nothing
like this one. I have tried
=datediff([Enter from date] "StartDate", "d" - date()-14) but get a
syntax
error. Can anybody assist me. Many thanx

 

[TinMan]

Hello Dale are you still available to assist me in this matter. I responded
to your post but need a little more guidance. I have broke up your comments
with a question where i am stuck can you please take a look at it again for
me. Many thanks

If I understand you correctly, you have:

1. a main form. I assume that this is for Staff members, so it has the
staff members name, StaffID, and may have some other fields.

2. a StaffHolidays subform. I assume that this is bound to a table
(tbl_StaffHolidays) which contains fields for StaffID, StartDate, and End
date. It should have a has a child/master relationship with the main form
that is based on the StaffID field.

3. a Conflicts subform. This should contain values based on a query similar
to what I provided. This subform should have no child/master relationship
defined; but should be based on the values of the fields (StartDate and
EndDate) on the StaffHolidays subform, and you should use the Current event
of the StaffHolidays subform as well as the AfterUpdate events of the
StartDate and End date fields in that form to requery this subform.
Personally, I would probably put some code in the Current event of the
subform (sub_StaffHolidays) to test to determine whether the current record
is a new record or not. If it is, I would set the RecordSource of the
sub_Conflicts to "". Then, in the AfterUpdate events for the Start and End
dates, I would confirm that both of those values are dates, and if they are,
set the recordsource back to the query.

Finally, the query that you use for the final subform (sub_Conflicts) needs
to look something like:

SELECT StaffID, StartDate, EndDate
FROM StaffHolidays
WHERE StaffHolidays.StartDate <=
Forms!frm_Main.sub_StaffHolidays.form.txtEndDate
AND StaffHolidays.EndDate >=
Forms!frm_Main.sub_StaffHolidays.form.txtStartDate

HTH
Dale

Hello again Dale, I have created a main form StaffMembers and a subform
StaffHolidays with another subform which i have bound to your query sql
which
i have called conflicts. When i open my main form it asks me for StartDate
and End Date parameters every time i try to view each staff member, I
entered
exactly same dates for two staff members but nothing popped up to say
there
was conflict either. Can you help me some more

Assuming your Holiday form (frm_StaffHolidays) has textboxes (txtFromDate
and
txtEndDate), then the query of your Holidays table would look something
like:

SELECT StaffID, FromDate, EndDate
FROM StaffHolidays
WHERE StaffHolidays.FromDate <= Forms!frm_StaffHolidays.txtEndDate
AND StaffHolidays.EndDate >= Forms!frm_StaffHolidays.txtFromDate

This may seem counter intuitive but basically, you have 4 possible
scenarios
relating to other peoples holidays.
1. Both the From and End dates fall between the new request dates
2. Both the From and End dates fall outside the new request dates
3. The From date is before the new request From date and the End date is
between the new requests From and End date
4. The From date is between the new requests From and End date and the
End
date is after the new requests End date.

The only way to identify all of the staff holidays that meet any of the 4
criteria above is to use the query I provided above.

Note you may have to define parameters for the form From and End dates in
the query, if you are going to save it.

If you are going to create it on the fly, then you will probably need to
add
the "#" symbol before and after the dates to ensure they are interpreted
properly. This might look like:

strSQL = "SELECT StaffID, FromDate, EndDate " _
& "FROM StaffHolidays " _
& "WHERE StaffHolidays.FromDate <= #" _
& Forms!frm_StaffHolidays.txtEndDate & "# " _
& "AND StaffHolidays.EndDate >= #" _
& Forms!frm_StaffHolidays.txtFromDate & "#"

Once you have the query, you might want to use it as the source of a list
on
the form or simply to use in a DCOUNT to identify the number of people
that
have conflicting holidays.

----
HTH
Dale



:

I have a staff data db. I am trying to record staff holidays that are
requested. I have a table called "StaffDetail" links to another table
"Holidays" with a "FromDate" field and an EndDate field along with a
totaldays taken field. I would like to enter two dates when staff
request a
holiday to check if another member of staff will also be off during
these
dates. Being as I have 2 fields i cannot do a between and end date,
also if
someone requests for a date which falls outside the start and end date
the
query may not pick up the conflicting dates. I need to know what the
formula
is that will enable me to enter the dates and show me the other staff
members
with conflicting holiday requests. I have read other questions but
nothing
like this one. I have tried
=datediff([Enter from date] "StartDate", "d" - date()-14) but get a
syntax
error. Can anybody assist me. Many thanx