Really need some help here :(<3

[Hannah287]

Heya guys, i am doing some work on a MS access report i need to achieve this:



A one off loyalty bonus payment of £500 is paid out after 500 days in employment by the firm.
Previously, the date this was meant to be paid was written on the reverse of the card and a post-it
stuck on the front as a reminder as the payment date drew near! After the employee had been paid
the bonus a large tick was written over the date on the back of the card and the post-it disposed of.
Modify the database so that the Employee Record Card form will now show:
• The date the Loyalty Bonus is due
• A box that acts as a Loyalty Bonus reminder
• Before the due date it should state “The Bonus is not due to be paid”
• On and after the due date it should state “The Bonus is now due to be paid”
• Once the Bonus has been paid all evidence of a reminder message should
disappear.
• A method of recording that the

If anyone could help it would be awesome, i have no clue were to start!

[jzwp11]

The date the bonus is due should be a calculated value (hiredate+500 days); I would recommend using the dateadd() function. You will have to record in a field in a table the date when the bonus was actually paid. With that, you should be able to use nested IIF() functions in your report to display the messages you want. The IIF() function might go something like this (not tested):

IIF(isNull(bonuspaiddate), IIF(dateadd("d",500,hiredate)>date(),"Bonus is not due to be paid","Bonus is now due to be paid"),Null)

[Hannah287]

=IIf(IsNull([Loyalty_bonus]),IIf(DateAdd("d",500,[Employment_Date])>Date(),"Bonus is not due to be paid","Bonus is now due to be paid"),Null)

This is what is currently looks like, but when i switch back to report view the box that should have the message is empty, i may be doing it in the wrong place. I dont think control source is the correct area to put it, or am i wrong ?

[jzwp11]

The control source is the correct place for the expression. Does the record source for the report have the Loyalty_bonus and employment_date fields? What is the datatype of the Loyalty_bonus field?

[Hannah287]

Loyalty_bonus isnt in a table, its is just an bit of code in its own box "=DateAdd("d",500,[Employment_Date])" This box is on the report.

[Hannah287]

Here is a picture http://imgur.com/sLRoI

[jzwp11]

You MUST store the actual date when the bonus was paid in order to use the expression I provided. Without that date, you will never know if the bonus was ever paid and thus you would not be able to satisfy your requirement: "Once the Bonus has been paid all evidence of a reminder message should disappear."

[Hannah287]

So i should make a record in one of the tables and have all the dates put in there ?

[Hannah287]

Is there way of doing this without having to put a new record in ?

[jzwp11]

I assume that the bonus is only paid once (please correct me if I am wrong). If that is the case, you could add a field to the employee table to hold the date on which the bonus was paid to the employee. Are you recording payments to your employees in other tables in the database? Is so, then you would need a different approach. I do not know the details of your application, so you will have to provide more details

[Hannah287]

yes it is a one off payment. The only other payments are bonuses but these are not in the tables so i guess making a new field wouldn't be that much hassle, but my problem is this control source calculates the date to be paid, if i replace this with =[Employee]![Loyalty Bonus Date] how is it going to figure out the dates ?

[jzwp11]

You would have a textbox control that shows the actual paid date (or null if not yet paid) for the employee.

You would have another textbox control to show the date when the bonus is to be paid (bonus due date). This would be an unbound control (i.e. not bound to a field in the table since calculated values should not be stored) with the following control source
=dateadd("d",500,Employment_Date)

You would then have your textbox with the message.

You can hide these controls (bonus due date & message textboxes) if the bonus paid date control has a valid date.

[Hannah287]

Can you go into a bit more depth on that bit ? i had a bit of trouble understanding it. I have a unbound box at the moment : Loyalty Bonus Due Date (just a label) and the text box with it has =dateadd("d",500,Employment_Date) as its control source. When i go to report view this now shows the date it is to be paid, i have another label: Loyalty Bonus Reminder this has another unbound box were i want the 2 reminders to appear. What do i need to do to get them in that box?

[Hannah287]

Sorry if im running you in circles :/

[jzwp11]

You would use the expression I provided earlier for the control source of the reminder textbox. You will have to substitute your actual field name for the bonus paid date

=IIf(IsNull(bonuspaiddatefield)IIf(DateAdd("d",500 ,[Employment_Date])>Date(),"Bonus is not due to be paid","Bonus is now due to be paid"),Null)