Iterating through recordsets in code

[Jake Frankham]

Hi again

I have called a procedure from within a query in order to return a date

In the VBA, I wish to iterate through each instance of a value in one field
ie empNo

122 ... ... ...
122 ... ... ...
436 ... ... ...
436 ... ... ...
436 ... ... ...
788 ... ... ...

I wish to iterate through each empNo in some kind of loop to determine which
record I wish to display in the query, then move on to the next set of
empNo's

PLEASE can someone tell me how this is done? - I know you can use things
like DO UNTIL RST.EOF but how do I do DO FOR EACH EMPNO?

Thanks Very Much

Jake

 

[Jake Frankham]

FYI

I am calling my function from within a query which holds numerous CProgID's
and it is this field which is passed to function as a parameter.

strSQL = "SELECT * FROM [sqry_midpointFee] WHERE [ClientProgID] = """ &
CProgID & """"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)


rst.MoveFirst
Set ClientProgID = rst("ClientProgID")
Set startdate = rst("startdate")
Set leavedate = rst("leavedate")

For Each ClientProgID In rst
.........................................
.........................................

Does this look right???

Cheers again

Jake

 

[Jake Frankham]

Hi Howard

Thanks for your help

What I am actually trying to do is find out which time the client was on his
7th week, ie

ClientProgID startdate leavedate
123 10/01/03 22/01/03
123 12/03/03 26/03/03
123 11/06/03 29/08/03
324 08/08/03 14/12/03
........ ............. .............. etc etc

So: each client 'does' a random number of weeks each time they join.

What I need to do is this: Use VB code to iterate through each client record
to determine which time they hit their 7th week.

For client 123 above, (s)he only did 2 weeks, then a further 2 weeks, and so
they hit their 7th week in the 3rd time. I want to be able to return THIS
record.

(There could be as many as 8 periods of being logged as attending, but I
will only ever want to return the record where they hit their 7th week)

I am clear about the logic behind the code which I need to do, it's just I'm
no good with recordsets and navigating through them !

I need to navigate through EACH ClientProgID to return the ONE record where
they hit their 7th week (determining the 7th week is not a problem for me) -
what is a problem for me is trying to return a record for each client.

I am calling my function from a query, which passes to the function a set of
UNIQUE ClientProgID's.

The function I have written (below) is (trying to) open up the set of all
ClientProgIDs and iterate through them. I am trying to return the 7th week
date.

MY CODE:

Public Function MidpointWeek(CProgID As String) As Date

Dim break As Integer
Dim currentWeeks As Integer
Dim prevTotalWeeks As Integer
Dim totalWeeks As Integer
Dim weeksToAdd As Integer
Dim midpointDate As Date

Dim ClientProgID As Field
Dim startdate As Field
Dim leavedate As Field

Dim strSQL As String
strSQL = "SELECT * FROM [sqry_midpointFee_PSL_FEED] WHERE [ClientProgID]
= """ & CProgID & """"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveFirst
Set ClientProgID = rst("ClientProgID")
Set startdate = rst("startdate")
Set leavedate = rst("leavedate")

For Each ClientProgID In rst.Fields
currentWeeks = 0
prevTotalWeeks = 0
totalWeeks = 0
break = 0
Do
If leavedate.Value Is Null Then
midpointDate = DateAdd("ww", Duration, startdate.Value)
break = 1
Else
currentWeeks = DateDiff("ww", startdate.Value,
leavedate.Value)
totalWeeks = totalWeeks + currentWeeks

If totalWeeks < Duration Then
prevTotalWeeks = prevTotalWeeks + currentWeeks
rst.MoveNext
Else
weeksToAdd = Duration - prevTotalWeeks
midpointDate = DateAdd("ww", weeksToAdd,
startdate.Value)
break = 1
End If
End If
Loop Until break = 1
rst.MoveNext
Next ClientProgID

MidpointWeek = midpointDate

Thanks Howard

Jake

What data are you looking for and what exactly are you trying to do?

It looks like you're trying to get the Start and Leave data for each

Client (?) to use them . . . somewhere else, I guess. Where is the data
stored? In your sqry_MidpointFee, is the StartDate and LeaveDate the same
for every record a Client has in the query?

Howard


----- Jake Frankham wrote: -----

FYI

I am calling my function from within a query which holds numerous CProgID's
and it is this field which is passed to function as a parameter.

strSQL = "SELECT * FROM [sqry_midpointFee] WHERE [ClientProgID] = """ &
CProgID & """"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)


rst.MoveFirst
Set ClientProgID = rst("ClientProgID")
Set startdate = rst("startdate")
Set leavedate = rst("leavedate")

For Each ClientProgID In rst
.........................................
.........................................

Does this look right???

Cheers again

Jake

Hi again

one

field
ie empNo
122 ... ... ...
436 ... ... ...
436 ... ... ...
436 ... ... ...
788 ... ... ...

determine

which
record I wish to display in the query, then move on to the next set of
empNo's things
like DO UNTIL RST.EOF but how do I do DO FOR EACH EMPNO?

 

[Michel Walsh]

Hi,



If your table is called SevenWeek, then the following SQL statement
produces what you want, no loop... no recordset.

==============================================
SELECT a.ClientProgID, a.StartDate, a.LeaveDate

FROM SevenWeek As a INNER JOIN SevenWeek AS b
ON a.ClientProgID=b.ClientProgID
AND b.StartDate <=a.StartDate

GROUP BY a.ClientProgID, a.StartDate, a.LeaveDate

HAVING 7

BETWEEN

SUM(DateDiff("ww", b.StartDate, b.LeaveDate))
- DateDiff("ww", a.StartDate, a.LeaveDate)

AND

SUM(DateDiff("ww", b.StartDate, b.LeaveDate))
==============================================



Note that 7 can be a parameter.



Hoping it may help,
Vanderghast, Access MVP

Hi Howard

Thanks for your help

What I am actually trying to do is find out which time the client was on his
7th week, ie

ClientProgID startdate leavedate
123 10/01/03 22/01/03
123 12/03/03 26/03/03
123 11/06/03 29/08/03
324 08/08/03 14/12/03
....... ............. .............. etc etc

So: each client 'does' a random number of weeks each time they join.

What I need to do is this: Use VB code to iterate through each client record
to determine which time they hit their 7th week.

For client 123 above, (s)he only did 2 weeks, then a further 2 weeks, and so
they hit their 7th week in the 3rd time. I want to be able to return THIS
record.

(There could be as many as 8 periods of being logged as attending, but I
will only ever want to return the record where they hit their 7th week)

I am clear about the logic behind the code which I need to do, it's just I'm
no good with recordsets and navigating through them !

I need to navigate through EACH ClientProgID to return the ONE record where
they hit their 7th week (determining the 7th week is not a problem for me) -
what is a problem for me is trying to return a record for each client.

I am calling my function from a query, which passes to the function a set of
UNIQUE ClientProgID's.

The function I have written (below) is (trying to) open up the set of all
ClientProgIDs and iterate through them. I am trying to return the 7th week
date.

MY CODE:

Public Function MidpointWeek(CProgID As String) As Date

Dim break As Integer
Dim currentWeeks As Integer
Dim prevTotalWeeks As Integer
Dim totalWeeks As Integer
Dim weeksToAdd As Integer
Dim midpointDate As Date

Dim ClientProgID As Field
Dim startdate As Field
Dim leavedate As Field

Dim strSQL As String
strSQL = "SELECT * FROM [sqry_midpointFee_PSL_FEED] WHERE [ClientProgID]
= """ & CProgID & """"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveFirst
Set ClientProgID = rst("ClientProgID")
Set startdate = rst("startdate")
Set leavedate = rst("leavedate")

For Each ClientProgID In rst.Fields
currentWeeks = 0
prevTotalWeeks = 0
totalWeeks = 0
break = 0
Do
If leavedate.Value Is Null Then
midpointDate = DateAdd("ww", Duration, startdate.Value)
break = 1
Else
currentWeeks = DateDiff("ww", startdate.Value,
leavedate.Value)
totalWeeks = totalWeeks + currentWeeks

If totalWeeks < Duration Then
prevTotalWeeks = prevTotalWeeks + currentWeeks
rst.MoveNext
Else
weeksToAdd = Duration - prevTotalWeeks
midpointDate = DateAdd("ww", weeksToAdd,
startdate.Value)
break = 1
End If
End If
Loop Until break = 1
rst.MoveNext
Next ClientProgID

MidpointWeek = midpointDate

Thanks Howard

Jake

What data are you looking for and what exactly are you trying to do?

It looks like you're trying to get the Start and Leave data for each

Client (?) to use them . . . somewhere else, I guess. Where is the data
stored? In your sqry_MidpointFee, is the StartDate and LeaveDate the same
for every record a Client has in the query?

Howard


----- Jake Frankham wrote: -----

FYI

I am calling my function from within a query which holds numerous CProgID's
and it is this field which is passed to function as a parameter.

strSQL = "SELECT * FROM [sqry_midpointFee] WHERE [ClientProgID] =

"""

&
CProgID & """"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)


rst.MoveFirst
Set ClientProgID = rst("ClientProgID")
Set startdate = rst("startdate")
Set leavedate = rst("leavedate")

For Each ClientProgID In rst
.........................................
.........................................

Does this look right???

Cheers again

Jake

a

date

in

one determine

set

of

use

things

 

[Jake Frankham]

Thank you VERY much Vanderghast !!

So, I just have a query with the attached SQL, no VBA whatsoever??

Jake

Hi,



If your table is called SevenWeek, then the following SQL statement
produces what you want, no loop... no recordset.

==============================================
SELECT a.ClientProgID, a.StartDate, a.LeaveDate

FROM SevenWeek As a INNER JOIN SevenWeek AS b
ON a.ClientProgID=b.ClientProgID
AND b.StartDate <=a.StartDate

GROUP BY a.ClientProgID, a.StartDate, a.LeaveDate

HAVING 7

BETWEEN

SUM(DateDiff("ww", b.StartDate, b.LeaveDate))
- DateDiff("ww", a.StartDate, a.LeaveDate)

AND

SUM(DateDiff("ww", b.StartDate, b.LeaveDate))
==============================================



Note that 7 can be a parameter.



Hoping it may help,
Vanderghast, Access MVP

Hi Howard

Thanks for your help

What I am actually trying to do is find out which time the client was on his
7th week, ie

ClientProgID startdate leavedate
123 10/01/03 22/01/03
123 12/03/03 26/03/03
123 11/06/03 29/08/03
324 08/08/03 14/12/03
....... ............. .............. etc etc

So: each client 'does' a random number of weeks each time they join.

What I need to do is this: Use VB code to iterate through each client record
to determine which time they hit their 7th week.

For client 123 above, (s)he only did 2 weeks, then a further 2 weeks,

and

so
they hit their 7th week in the 3rd time. I want to be able to return THIS
record.

(There could be as many as 8 periods of being logged as attending, but I
will only ever want to return the record where they hit their 7th week)

I am clear about the logic behind the code which I need to do, it's just I'm
no good with recordsets and navigating through them !

I need to navigate through EACH ClientProgID to return the ONE record where
they hit their 7th week (determining the 7th week is not a problem for me) -
what is a problem for me is trying to return a record for each client.

I am calling my function from a query, which passes to the function a

set

of
UNIQUE ClientProgID's.

The function I have written (below) is (trying to) open up the set of all
ClientProgIDs and iterate through them. I am trying to return the 7th week
date.

MY CODE:

Public Function MidpointWeek(CProgID As String) As Date

Dim break As Integer
Dim currentWeeks As Integer
Dim prevTotalWeeks As Integer
Dim totalWeeks As Integer
Dim weeksToAdd As Integer
Dim midpointDate As Date

Dim ClientProgID As Field
Dim startdate As Field
Dim leavedate As Field

Dim strSQL As String
strSQL = "SELECT * FROM [sqry_midpointFee_PSL_FEED] WHERE [ClientProgID]
= """ & CProgID & """"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveFirst
Set ClientProgID = rst("ClientProgID")
Set startdate = rst("startdate")
Set leavedate = rst("leavedate")

For Each ClientProgID In rst.Fields
currentWeeks = 0
prevTotalWeeks = 0
totalWeeks = 0
break = 0
Do
If leavedate.Value Is Null Then
midpointDate = DateAdd("ww", Duration, startdate.Value)
break = 1
Else
currentWeeks = DateDiff("ww", startdate.Value,
leavedate.Value)
totalWeeks = totalWeeks + currentWeeks

If totalWeeks < Duration Then
prevTotalWeeks = prevTotalWeeks + currentWeeks
rst.MoveNext
Else
weeksToAdd = Duration - prevTotalWeeks
midpointDate = DateAdd("ww", weeksToAdd,
startdate.Value)
break = 1
End If
End If
Loop Until break = 1
rst.MoveNext
Next ClientProgID

MidpointWeek = midpointDate

Thanks Howard

Jake

What data are you looking for and what exactly are you trying to do?

It looks like you're trying to get the Start and Leave data for each

Client (?) to use them . . . somewhere else, I guess. Where is the data
stored? In your sqry_MidpointFee, is the StartDate and LeaveDate the same
for every record a Client has in the query?

Howard


----- Jake Frankham wrote: -----

FYI

I am calling my function from within a query which holds numerous CProgID's
and it is this field which is passed to function as a parameter.

strSQL = "SELECT * FROM [sqry_midpointFee] WHERE [ClientProgID] =

"""

&
CProgID & """"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)


rst.MoveFirst
Set ClientProgID = rst("ClientProgID")
Set startdate = rst("startdate")
Set leavedate = rst("leavedate")

For Each ClientProgID In rst
.........................................
.........................................

Does this look right???

Cheers again

Jake
Hi again
I have called a procedure from within a query in order to

return

a in set use

 

[Jake Frankham]

Sorry, your name is Michel !!

Hi,



If your table is called SevenWeek, then the following SQL statement
produces what you want, no loop... no recordset.

==============================================
SELECT a.ClientProgID, a.StartDate, a.LeaveDate

FROM SevenWeek As a INNER JOIN SevenWeek AS b
ON a.ClientProgID=b.ClientProgID
AND b.StartDate <=a.StartDate

GROUP BY a.ClientProgID, a.StartDate, a.LeaveDate

HAVING 7

BETWEEN

SUM(DateDiff("ww", b.StartDate, b.LeaveDate))
- DateDiff("ww", a.StartDate, a.LeaveDate)

AND

SUM(DateDiff("ww", b.StartDate, b.LeaveDate))
==============================================



Note that 7 can be a parameter.



Hoping it may help,
Vanderghast, Access MVP

Hi Howard

Thanks for your help

What I am actually trying to do is find out which time the client was on his
7th week, ie

ClientProgID startdate leavedate
123 10/01/03 22/01/03
123 12/03/03 26/03/03
123 11/06/03 29/08/03
324 08/08/03 14/12/03
....... ............. .............. etc etc

So: each client 'does' a random number of weeks each time they join.

What I need to do is this: Use VB code to iterate through each client record
to determine which time they hit their 7th week.

For client 123 above, (s)he only did 2 weeks, then a further 2 weeks,

and

so
they hit their 7th week in the 3rd time. I want to be able to return THIS
record.

(There could be as many as 8 periods of being logged as attending, but I
will only ever want to return the record where they hit their 7th week)

I am clear about the logic behind the code which I need to do, it's just I'm
no good with recordsets and navigating through them !

I need to navigate through EACH ClientProgID to return the ONE record where
they hit their 7th week (determining the 7th week is not a problem for me) -
what is a problem for me is trying to return a record for each client.

I am calling my function from a query, which passes to the function a

set

of
UNIQUE ClientProgID's.

The function I have written (below) is (trying to) open up the set of all
ClientProgIDs and iterate through them. I am trying to return the 7th week
date.

MY CODE:

Public Function MidpointWeek(CProgID As String) As Date

Dim break As Integer
Dim currentWeeks As Integer
Dim prevTotalWeeks As Integer
Dim totalWeeks As Integer
Dim weeksToAdd As Integer
Dim midpointDate As Date

Dim ClientProgID As Field
Dim startdate As Field
Dim leavedate As Field

Dim strSQL As String
strSQL = "SELECT * FROM [sqry_midpointFee_PSL_FEED] WHERE [ClientProgID]
= """ & CProgID & """"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveFirst
Set ClientProgID = rst("ClientProgID")
Set startdate = rst("startdate")
Set leavedate = rst("leavedate")

For Each ClientProgID In rst.Fields
currentWeeks = 0
prevTotalWeeks = 0
totalWeeks = 0
break = 0
Do
If leavedate.Value Is Null Then
midpointDate = DateAdd("ww", Duration, startdate.Value)
break = 1
Else
currentWeeks = DateDiff("ww", startdate.Value,
leavedate.Value)
totalWeeks = totalWeeks + currentWeeks

If totalWeeks < Duration Then
prevTotalWeeks = prevTotalWeeks + currentWeeks
rst.MoveNext
Else
weeksToAdd = Duration - prevTotalWeeks
midpointDate = DateAdd("ww", weeksToAdd,
startdate.Value)
break = 1
End If
End If
Loop Until break = 1
rst.MoveNext
Next ClientProgID

MidpointWeek = midpointDate

Thanks Howard

Jake

What data are you looking for and what exactly are you trying to do?

It looks like you're trying to get the Start and Leave data for each

Client (?) to use them . . . somewhere else, I guess. Where is the data
stored? In your sqry_MidpointFee, is the StartDate and LeaveDate the same
for every record a Client has in the query?

Howard


----- Jake Frankham wrote: -----

FYI

I am calling my function from within a query which holds numerous CProgID's
and it is this field which is passed to function as a parameter.

strSQL = "SELECT * FROM [sqry_midpointFee] WHERE [ClientProgID] =

"""

&
CProgID & """"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)


rst.MoveFirst
Set ClientProgID = rst("ClientProgID")
Set startdate = rst("startdate")
Set leavedate = rst("leavedate")

For Each ClientProgID In rst
.........................................
.........................................

Does this look right???

Cheers again

Jake
Hi again
I have called a procedure from within a query in order to

return

a in set use

 

[Michel Walsh]

Hi,

Indeed... You can use that query as RecordSource for a form, without a
single line of VBA code required.


Vanderghast, Access MVP

 

[Jake Frankham]

Michel

I don't know how you did that, but it works PERFECTLY

Thank you (and Howard) SO much, I have spent ALL damn day trying to do that
!!

May I burden you with 2 minor refinements though? (omg I hear you shout)

* Does the DateDiff function calculate weeks as WHOLE weeks? I need to
calculate the weeks as being any whole OR part week, ie if a client started
on a Monday and left the following Monday - that is 2 weeks, because (s)he
participated in week 2.
(How would I adapt DateDiff to cater for this?)

* CRUCIALLY, I need to calculate the 7-week date. The query you supplied
me gives ClientProgID, startdate and leavedate - but how do I calculate the
7-week date?

* Infrequently, for SOME clients, I would need to calculate the 13-week
date (as opposed to 7-week date) - how could I pass this value to the query?

I REALLY appreciate your time, many thanks

Jake

Sorry, your name is Michel !!

Hi,



If your table is called SevenWeek, then the following SQL statement
produces what you want, no loop... no recordset.

==============================================
SELECT a.ClientProgID, a.StartDate, a.LeaveDate

FROM SevenWeek As a INNER JOIN SevenWeek AS b
ON a.ClientProgID=b.ClientProgID
AND b.StartDate <=a.StartDate

GROUP BY a.ClientProgID, a.StartDate, a.LeaveDate

HAVING 7

BETWEEN

SUM(DateDiff("ww", b.StartDate, b.LeaveDate))
- DateDiff("ww", a.StartDate, a.LeaveDate)

AND

SUM(DateDiff("ww", b.StartDate, b.LeaveDate))
==============================================



Note that 7 can be a parameter.



Hoping it may help,
Vanderghast, Access MVP

Hi Howard

Thanks for your help

What I am actually trying to do is find out which time the client was

on
his

7th week, ie

ClientProgID startdate leavedate
123 10/01/03 22/01/03
123 12/03/03 26/03/03
123 11/06/03 29/08/03
324 08/08/03 14/12/03
....... ............. ..............

etc
etc

So: each client 'does' a random number of weeks each time they join.

What I need to do is this: Use VB code to iterate through each client record
to determine which time they hit their 7th week.

For client 123 above, (s)he only did 2 weeks, then a further 2 weeks,

and

so
they hit their 7th week in the 3rd time. I want to be able to return THIS
record.

(There could be as many as 8 periods of being logged as attending, but I
will only ever want to return the record where they hit their 7th week)

I am clear about the logic behind the code which I need to do, it's

just
I'm

no good with recordsets and navigating through them !

I need to navigate through EACH ClientProgID to return the ONE record where
they hit their 7th week (determining the 7th week is not a problem for me) -
what is a problem for me is trying to return a record for each client.

I am calling my function from a query, which passes to the function a

set

of
UNIQUE ClientProgID's.

The function I have written (below) is (trying to) open up the set of all
ClientProgIDs and iterate through them. I am trying to return the 7th week
date.

MY CODE:

Public Function MidpointWeek(CProgID As String) As Date

Dim break As Integer
Dim currentWeeks As Integer
Dim prevTotalWeeks As Integer
Dim totalWeeks As Integer
Dim weeksToAdd As Integer
Dim midpointDate As Date

Dim ClientProgID As Field
Dim startdate As Field
Dim leavedate As Field

Dim strSQL As String
strSQL = "SELECT * FROM [sqry_midpointFee_PSL_FEED] WHERE [ClientProgID]
= """ & CProgID & """"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveFirst
Set ClientProgID = rst("ClientProgID")
Set startdate = rst("startdate")
Set leavedate = rst("leavedate")

For Each ClientProgID In rst.Fields
currentWeeks = 0
prevTotalWeeks = 0
totalWeeks = 0
break = 0
Do
If leavedate.Value Is Null Then
midpointDate = DateAdd("ww", Duration, startdate.Value)
break = 1
Else
currentWeeks = DateDiff("ww", startdate.Value,
leavedate.Value)
totalWeeks = totalWeeks + currentWeeks

If totalWeeks < Duration Then
prevTotalWeeks = prevTotalWeeks + currentWeeks
rst.MoveNext
Else
weeksToAdd = Duration - prevTotalWeeks
midpointDate = DateAdd("ww", weeksToAdd,
startdate.Value)
break = 1
End If
End If
Loop Until break = 1
rst.MoveNext
Next ClientProgID

MidpointWeek = midpointDate

Thanks Howard

Jake
What data are you looking for and what exactly are you trying to do?

It looks like you're trying to get the Start and Leave data for each
Client (?) to use them . . . somewhere else, I guess. Where is the data
stored? In your sqry_MidpointFee, is the StartDate and LeaveDate the same
for every record a Client has in the query?

Howard


----- Jake Frankham wrote: -----

FYI

I am calling my function from within a query which holds numerous
CProgID's
and it is this field which is passed to function as a parameter.

strSQL = "SELECT * FROM [sqry_midpointFee] WHERE [ClientProgID]

=
"""

&
CProgID & """"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)


rst.MoveFirst
Set ClientProgID = rst("ClientProgID")
Set startdate = rst("startdate")
Set leavedate = rst("leavedate")

For Each ClientProgID In rst
.........................................
.........................................

Does this look right???

Cheers again

Jake
Hi again
I have called a procedure from within a query in order to

return

a
date
In the VBA, I wish to iterate through each instance of a

value
in

one
field
ie empNo
122 ... ... ...
122 ... ... ...
436 ... ... ...
436 ... ... ...
436 ... ... ...
788 ... ... ...
I wish to iterate through each empNo in some kind of loop to
determine
which
record I wish to display in the query, then move on to the

next
set

of
empNo's
PLEASE can someone tell me how this is done? - I know you

can
use

things
like DO UNTIL RST.EOF but how do I do DO FOR EACH EMPNO?
Thanks Very Much
Jake

 

[Jake Frankham]

Michel

I don't know how you did that, but it works PERFECTLY

Thank you (and Howard) SO much, I have spent ALL damn day trying to do that
!!

May I burden you with 2 minor refinements though? (omg I hear you shout)

* Does the DateDiff function calculate weeks as WHOLE weeks? I need to
calculate the weeks as being any whole OR part week, ie if a client started
on a Monday and left the following Monday - that is 2 weeks, because (s)he
participated in week 2.
(How would I adapt DateDiff to cater for this?)

* CRUCIALLY, I need to calculate the 7-week date. The query you supplied
me gives ClientProgID, startdate and leavedate - but how do I calculate the
7-week date?

* Infrequently, for SOME clients, I would need to calculate the 13-week
date (as opposed to 7-week date) - how could I pass this value to the query?

I REALLY appreciate your time, many thanks

Jake

 

[Michel Walsh]

Hi,


- DateDiff returns the number of time we "change of", accordingly to
the first argument. The typical example is that, on year, it returns 1 year
if the two dates are like 2000 Dec 30 and 2001 Jan 2 (that is 2 days, in
fact) and it also return 1 year it the two dates are 2000 Jan 2 and 2001
Dec 30 (which is around 720 days). In both cases, 2 days or 730 days, it
does not matter, there is just ONE "change of year, so 1 is returned. Same
logic is applicable for "weeks" rather than year. Note that you can specify
the day of the week that defines the week-start (and indirectly so, the
week-change point).


SUM(DateDiff("ww", b.StartDate, b.LeaveDate))
- DateDiff("ww", a.StartDate, a.LeaveDate)

gives the number of week (change of week, more precisely) before starting
the actual period. Seven minus that result gives us the number of week,
after a.StartDate, we need to start in the seventh week.

DateAdd( "ww", 7- ( SUM( ... ) - ... ), a.StartDate )


should then supply a date in the seventh week.



If you need 13 rather than 7, probably with a table like:

TableName
ClientProgID, RequiredWeek ' fields
1010, 7
1011, 13
1012, 7
1015, 7 ' data



we can then change the FROM clause to get something like:


FROM (SevenWeek As a INNER JOIN SevenWeek AS b
ON a.ClientProgID=b.ClientProgID
AND b.StartDate <=a.StartDate)
INNER JOIN TableName
ON a.ClientProgID = TableName.ClientProgID


and the HAVING to:



HAVING TableName.RequiredWeek
BETWEEN ...



since TableName.RequiredWeek would then be 7 or 13.

You also have to append TableName.RequiredWeek to the list of fields in
the GROUP BY.




Hoping it may help,
Vanderghast, Access MVP

 

[Jake Frankham]

Hi Michel

Sorry for being a nuisance, but I am a bit of a novice !!

To clarify, where do I incorporate the following statement into my query?

DateAdd( "ww", 7- ( SUM( ... ) - ... ), a.StartDate )

Thank you yet again

Jake

 

[Michel Walsh]

Hi,


You append it to the SELECT list.

SELECT ..., ..., ..., DateAdd( .. ) As SeventhWeekStarting
FROM ...



Vanderghast, Access MVP

 

[Jake Frankham]

G R E A T

But I promise this is the VERY last thing I will pester you about......

Some of the calculated 7(or13) week dates are incorrect.

I know why, but don't know how to fix it - it goes back to what you were
saying about how dates are calculated as 'change of' date.

Access appears to be working out MondayDate to NextMondayDate as 1 week, but
I need it to be 2 weeks.

However it works out MondayDate to NextTuesdayDate as 2 weeks (which is
correct for me)

How do I cater for the occassions when someone will leave on a Monday, as I
want this week included in the calculation.

If I amend the calculation (ie add 1 or something) then this will make the
Monday-Next Tuesday example 3 weeks, which is clearly wrong.

I very much appreciate the support you have offered me over the last 2 days,
I think 'life-saver' is probably the best term to use !

Jake

 

[Michel Walsh]

Hi,



DateDiff( "ww", StartingDate, EndingDate + 1, , vbMonday)



since adding 1 ( one day, = 24 hours) include the whole day. The fourth
argument specifies that Monday is the start of week ( in a query, replace
vbMonday by its value, 2) in this context.




Hoping it may help,
Vanderghast, Access MVP

 

[Jake Frankham]

Aaargh!!! (I know I promised, but this is really irritatingme)

My query is returning 2 rows for a certain ClientProgID (should only be the
one where the 7 week date falls in)

I had to adapt the code slightly to cater for the occassion when a client
only does 1 or 2 days, so i deducted one day from start date ie:

DateDiff("ww", b.startdate - 1, b.leavedate + 1, 2) // where
'2' is Monday

Now this works perfectly, ALL EXCEPT for one ClientProgID which has the
following data:

ClientProgID startdate leavedate
111222 03/11/03 14/11/03 //ie 2 weeks
111222 01/12/03 26/12/03 //ie 4 weeks
111222 05/01/04 09/01/04 //ie 1 week
//this is the record I expect to be returned
111222 19/01/04 23/01/04 //ie 1 week
//but I also get this one !!!!

This ONLY happens for THIS client (as far as I can see)

I appreciate you probably don't have time to go through this, but your
continued help is certainly welcome.
Thanks yet again, my SQL follows:

SELECT a.ClientProgID, a.startdate, a.leavedate,

DateAdd("ww",c.weeks-(SUM(DateDiff("ww",b.startdate,b.leavedate))-DateDiff("
ww",a.startdate,a.leavedate)),a.startdate) AS midpointDate
FROM (sqry_midpointFee_PSL_1 AS a INNER JOIN
sqry_midpointFee_PSL_1 AS b ON (b.startdate<=a.startdate)
AND (a.ClientProgID=b.ClientProgID)) INNER JOIN
sqry_midpointFee_PSL_2 AS c ON a.ClientProgID=c.ClientProgID
GROUP BY a.ClientProgID, a.startdate, a.leavedate, c.weeks
HAVING c.weeks
BETWEEN SUM(DateDiff("ww", b.startdate - 1, b.leavedate + 1, 2)) -
DateDiff("ww", a.startdate - 1, a.leavedate + 1, 2)
AND SUM(DateDiff("ww", b.StartDate - 1, b.leavedate
+ 1, 2));

//where sqry_midpointFee_PSL_1 supplies ClientProgID's and start and leave
dates
//and sqry_midpointFee_PSL_2 supplies the relevant midpoint week, ie 7 or 13

Thanks again

Jake

 

[Michel Walsh]

Hi,


This occurs because there is no week change from a start and a leave, so
DateDiff("ww" , , ) returns 0, intending to tell us that this is no work (no
week-work).

It seems that a more robust solution would be to artificially change the
LeaveDate (in the query) so it always fall on a Monday

SUM( DateDiff("ww", x.StartDate , x.LeaveDate + Choose(
WeekDay(x.LeaveDate), 1, 0, 6, 5, 4, 3, 2 ) , 2 ) )


which is nothing more than adding 0 to 6 days to the leaving date, since,
indeed

x.LeaveDate + Choose( WeekDay(x.LeaveDate), 1, 0, 6, 5, 4, 3, 2 )


is always a Monday. DateDiff("ww", ... , ..., 2 ) would then return, at
least, 1. I let you decide if there must be a 0 or a 7 :

x.LeaveDate + Choose( WeekDay(x.LeaveDate), 1, 7, 6, 5, 4, 3, 2 )

in cases the leaving date is already a Monday.



That seems more robust than the +1 and -1 I proposed previously. ( ? )



Hoping it may help,
Vanderghast, Access MVP

 

[Jake Frankham]

sorry, sorry, sorry, sorry,

Could you let me know how I apply this in the existing code, as I am not
quite following your last email (sanity is not prevailing)

So I get rid of the -1 / +1 bits, but which Choose functions do I apply to
where in my query?

If I attach my existing query could you advise me of which bits I need to
change? I have tried changing various sections but I am just getting into a
mess.
Also, with the code I have below I still get 2 entries for that one
ClientProgID - probably because of the example you gace of the 1 week thing
where a week is never reached - how do I apply :

x.LeaveDate + Choose( WeekDay(x.LeaveDate), 1, 7, 6, 5, 4, 3, 2 )

into the query to test whether the leave date is already a Monday?

Indeed should this all be done in a DIFFERENT query? Should the Choose(...)
function go in the SELECT statement too?

I am sorry I am so confused, I'm not very good at this, Continued thanks,
jake, code follows:
____________________________________________________________________________
________
SELECT a.ClientProgID, a.startdate, a.leavedate,
DateAdd("ww",c.weeks-(SUM(DateDiff("ww",b.startdate,b.leavedate +
Choose(WeekDay(b.leavedate),1,0,6,5,4,3,2),2))) -
DateDiff("ww",a.startdate,a.leavedate),a.startdate) AS midpointDate
FROM (sqry_midpointFee_PSL_1 AS a INNER JOIN sqry_midpointFee_PSL_1 AS b ON
(b.startdate<=a.startdate) AND (a.ClientProgID=b.ClientProgID)) INNER JOIN
sqry_midpointFee_PSL_2 AS c ON a.ClientProgID=c.ClientProgID
GROUP BY a.ClientProgID, a.startdate, a.leavedate, c.weeks
HAVING c.weeks

BETWEEN SUM(DateDiff("ww", b.startdate, b.leavedate +
Choose(WeekDay(b.leavedate), 1, 0, 6, 5, 4, 3, 2), 2))
- DateDiff("ww", a.startdate, a.leavedate +
Choose(WeekDay(a.leavedate), 1, 0, 6, 5, 4, 3, 2), 2)
AND SUM(DateDiff("ww", b.StartDate, b.leavedate +
Choose(WeekDay(b.leavedate), 1, 0, 6, 5, 4, 3, 2), 2));

 

[Jake Frankham]

Hi

I have now supplied my query with the 'corrected' leave dates (thus no need
to include the Choose(...) functions within the main query)

But I still get 2 records (indicated with *) for a particular client whose
start/correctedleave dates are:

03/11/03 - 17/11/03
*01/12/03 - 29/12/03
*05/01/04 - 12/01/04
19/01/04 - 26/01/04 //note the leavedates are now all MONDAYS

Regards

jake

 

[Jake Frankham]

Cancel that correction

I am now in more of a mess than ever before, please revert to my email of
today, timed at 17.47

Jake

 

[Michel Walsh]

Hi,


Seems right, except, maybe, as you said, if the leaving date is already a
Monday, thus, just change the 0 for a 7, everywhere, in the sequence 1, 0,
6, 5, 4, 3, 2 to make it 1, 7, 6, 5, 4, 3, 2 ( 4 times ). Note that
we know the leaving date is a Monday since it is when WeekDay returns 2.



Hoping it may help,
Vanderghast, Access MVP