Help in translating a criteria from a query!!

[neemi]

I have a database that I am doing some work on (which was writen by someone else)

One of the queries has some code in the Update which I cannot fully understand... I would be helpfull if someone can help in telling me what it is doing... plz

Firstly I shall give a discription of the fields involved:

[HRWEEK] : Number of hrs a course runs in a week
[BEGD] : Start date of a course
[EEND] : End Date of a course
[dtmTerm1_Start] : Start Date of Term 1
[dtmTerm1_End] : End Date of Term 1
[dtmTerm2_Start] : Start Date of Term 2
[dtmTerm2_End] : End Date of Term 2
[dtmTerm3_Start] : Start Date of Term 3
[dtmTerm3_End] : End Date of Term 3

The SQL is I need help explaining wats happening is:

UPDATE tblR1_Data, tblTerm SET tblR1_Data.AUTUMN = [HRWEEK]*IIf(([BEGD] Between [dtmTerm1_Start] And [dtmTerm1_End]),IIf(([EEND]<=[dtmTerm1_End]),DateDiff(&quot;ww&quot;,[BEGD],[EEND]),DateDiff(&quot;ww&quot;,[BEGD],[dtmTerm1_End])),0);


Another one I need help with is:

UPDATE tblR1_Data, tblTerm SET tblR1_Data.AUTUMN = [HRWEEK]*IIf(([BEGD] Between [dtmTerm1_Start] And [dtmTerm1_End]),IIf((DateDiff(&quot;d&quot;,[BEGD],[EEND]) Between 0 And 6),1,0),0)
WHERE (((tblR1_Data.AUTUMN)=0));

Thanks in advance

Neemi

 

[LittleSmudge]

Does this help :-

UPDATE tblR1_Data, tblTerm SET 

tblR1_Data.AUTUMN = [HRWEEK]* AValue

Where :-
If(([BEGD] Between [dtmTerm1_Start] And [dtmTerm1_End]) Then 
    If [EEND]<=[dtmTerm1_End] Then
        AValue = DateDiff(&quot;ww&quot;,[BEGD],[EEND])
    Else 
        AValue = DateDiff(&quot;ww&quot;,[BEGD],[dtmTerm1_End])
    End If
Else
    AValue = 0
End If;

Laying it out like that can often make it easier to understand. All it takes is replacing the commas with THEN and ELSE statements and replacing the brackets with IF and END IF statements - Then fiddle with the layout.

Now you have the idea you can do the second one - can't you ?


'ope-that-'elps.

G LS

 

[Dodgey]

UPDATE tblR1_Data, tblTerm
SET tblR1_Data.AUTUMN = [HRWEEK]*
IIf(([BEGD] Between [dtmTerm1_Start] And [dtmTerm1_End]),
IIf(([EEND]<=[dtmTerm1_End]),
DateDiff(&quot;ww&quot;,[BEGD],[EEND]),
DateDiff(&quot;ww&quot;,[BEGD],[dtmTerm1_End])
),
0);

If the course's duration is solely within the term 1 dates: ie

Term1 StartDate EndDate
|------------------------------|

Course Start Date EndDate
|------------------------|

then calculate the total number of hours of work are within that course ie: [HRWEEK] * 'Number Of Weeks' (datediff..)

The calculated number shall then be updated within the field &quot;tblR1_Data.AUTUMN&quot;


UPDATE tblR1_Data, tblTerm
SET tblR1_Data.AUTUMN = [HRWEEK]*
IIf(([BEGD] Between [dtmTerm1_Start] And [dtmTerm1_End]),
IIf((DateDiff(&quot;d&quot;,[BEGD],[EEND]) Between 0 And 6),
1,
0
),
0
)
WHERE (((tblR1_Data.AUTUMN)=0));

This query looks to be a clean up and is executed after the above query.
For all courses who's &quot;tblR1_Data.AUTUMN&quot; field is equal to zero, if the course's duration is solely within the term 1 dates AND the course is less than a week AND the then i.e
...
IIf((DateDiff(&quot;d&quot;,[BEGD],[EEND]) Between 0 And 6)
...

then set the &quot;tblR1_Data.AUTUMN&quot; field to the number of hours of work are within that course i.e.

[HRWEEK] * 1

otherwise set it to zero (It's original value!)


If this helps, be a friend and mark this post as helpful.