Cut off date

[JaneB19]

I was wondering if anyone knew how to create a cut off date in a query?!

What I have is a personnel tracking database, keeping tabs on employees' movements on and offshore!

The problem that I have is:

Sometimes an employee can be offshore over two months, i.e. they could have gone offshore on the 25/08/2002 and won't come back onshore until 06/09/2002. The bonus lists have to show the amount accrued up to the end of the month, in this case that would be (bonus total)*(number of days to the end of the month).

Does anybody know how I could do this?

Thanks in advance

[PC2]

 

[pgk]

Hi,
Try the following piece of code. It gives you the number of days to the month end using the current date. Modify it to your needs.


DateDiff("d", DateAdd("m", 1, Date) - Day(Date), DateAdd("m", 1, Date))

Hope it helps. Let me know what happens.
With regards,
PGK

 

[JaneB19]

Hi PGK

I didn't realise you could use DateDiff in queries?

I have been trying WHERE statements like

WHERE Offshore>=(1/8/2002) AND Onshore<=(31/8/2002)

but this doesn't return any records, when I know it should. Does what I have, above, make sense or am I attacking the problem from the worng angle?

This query is only being used for reports, and is not being shown in the forms or any other part of the database!

Thanks
[PC2]

P.S Access query didn't like your suggestion. It decided that there were too many arguments for it to cope with!

 

[pgk]

Hi,
I just gave you an idea. I didn't think of your using it in a query. What exactly are the fields in your table and what is the info that you want your query to return?

Hope it helps. Let me know what happens.
With regards,
PGK

 

[JaneB19]

Sorry, I realised after I posted the last note that I hadn't mentioned that it was a query I was doing!

The fields that I'm using for this query are:

Name (the name of the employee that has gone offshore)
Offshore (the date that the employee went offshore)
Onshore (the date that the employee came back onshore)
BonusRate1 (the amount being paid to the employee)
NoDays@Bonus1 (the number of days that the employee worked at that bonus rate)
BonusRate2 (the amount being paid to the employee)
NoDays@Bonus2 (the number of day that the employee worked at that bonus rate)

There is a difference between the Bonus Rates but their irrelevant to what I'm wanting to do!

I have a Tracking form that the details of the employees' while working offshore are entered into. What I'm trying to do, is figure out how to create a report for the Bonus Totals for each month. As I said earlier some of the employees are still offshore at the end of the month. If I was to get the users to only enter details 'til the end of the month this would mean they would have to enter that all the employees came back onshore at the end of the month, which is not true and also messes up the Offshore/Onshore History reports. So I would like to have an expression or something like that, that will determine the end of the month, i.e. if the employee is still offshore at the end of the month, the bonus is only calculated to the end of the month and not when the employee came back onshore.

I'm hoping that this makes sense, because I'm finding it very difficult to explain If it doesn't, let me know, and I will try to explain again.

Thanks for your help
Regards
[PC2]

 

[pgk]

Hi,
I created a table just like you have described ( with a few missing fields, of course). I have created a Public Procedure that is used to enter the bonus days into the table( I call it Jane ).

Option Compare Database
Dim db As DAO.Database
Dim rs As DAO.Recordset
Option Compare Database
Dim db As DAO.Database
Dim rs As DAO.Recordset


Public Sub DetermineBonusDays()
Set db = CurrentDb
Set rs = db.OpenRecordset(&quot;Select * from Jane where onshore is Null&quot;, dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF
DoCmd.RunSQL &quot;Update Jane set noOfBonusDays=&quot; & DateDiff(&quot;d&quot;, (DateAdd(&quot;m&quot;, 1, CDate(Int(Now()))) - Day(Int(Now()))), DateAdd(&quot;m&quot;, 1, CDate(Int(Now())))) & &quot; where name='&quot; & rs.Fields(&quot;name&quot & &quot;'&quot;
rs.MoveNext
Loop
rs.Close
Set rs = db.OpenRecordset(&quot;Select * from Jane where onshore is not null&quot;, dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF
If Month(rs.Fields(&quot;onshore&quot) = Month(Int(Now())) Then
DoCmd.RunSQL &quot;Update Jane set noOfBonusDays=&quot; & DateDiff(&quot;d&quot;, DateSerial(Year(Int(Now())), Month(Int(Now())), 1), rs.Fields(&quot;onshore&quot) & &quot; where name='&quot; & rs.Fields(&quot;name&quot & &quot;'&quot;
End If
rs.MoveNext
Loop
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

Ofcourse, I assume that name is a primary key. This will check if the onshore field is null ( I actually set the Default Value property for that property to Null) and then proceed to determine the number of days elapsed from the start of the month till today. It then updates all records with that detail.


It then determines the users with the onshore date filled in. If the month of onshore date and current date are the same, then the number of bonus days will be determined as follows:

noOfBonusDays=Onshore Date - Start of Current month

Eg. 05/09/2002 - 01/09/2002 = 4


You can run this module and can then use a query to determine the details. Post as what happens. Hope it helps. Let me know what happens.
With regards,
PGK