DLookUp in Form

[Haytham]

Hi All...
I'm trying to build an expression in the form using DLookUp Function. I have a tabular form for the staff of the company where according to their contract period and contract date, I can calculate their holiday's entitled.
If the employee is on duty for more than 300 days, then he is eligible for the holiday, otherwise NO.
Everything is going on well.
Now when the contract is finish and a new contract is to be make, I don't want to calculate the 300 days and he can take holidays according to his duration.
e.g. he join for 3 month, then he can take a holiday for the 3 month and so on.
I think the answer is in DLookUp. I tried to build an expression but not succedded.
Any help please

 

[jwindon]

I'm not seeing the DLookup here. Are you saying that you want each contract duration to be counted separarely.

Contract 1: 300 days = holiday yes
Contract 2: 280 days = holiday no

When you said everything is going OK, did you mean that you are able to return the records that match your criteria.

At this point I can offer this:
On your form, create an unbound text box. Use it to calculate the number of days (duration of the contract). Make another unbound text box. On your forms OnCurrent Event property put code to check the value of UnboundDuration using If-Then

Private Sub CheckHoliday()
If Me.UnboundDuration >300 Then
Me.SecondUnboundTextbox = "Take Holiday"
Else
Me.SecondUnboundTextbox = "No Holiday"
End If
End Sub

[This message has been edited by jwindon (edited 10-02-2001).]

 

[Haytham]

Hi, yes every thing is going well to some extent.
My point is If the employee is having more than one record, then the 300 days should not be counted for the second record.
If only one record, it should stick to the rule.
I mean, when the employee complete 300 days he can be eligible for holiday for first contract. When a new contract is done to the same employee and let us say he resign, then whatever days he join, the holiday will be counted accordingly.
Through IIF statement I was succedeed in the first step.
Then, I created Unbound field where I was EmployeeNo to return the number of record entry in the first field named EmployeeNo.
So, if in the field EmployeeNo e.g. 13 is more than 1 record I want in the unbound field to give me 1. Otherwise 0.
dbs sample can be send if required to the mail. Very thankful to you

 

[jwindon]

So you are needing to know how many days that employee actually completed in ALL contracts (if he doesn't resign) and base your Holiday yes/no off THAT total?

Is that right?

 

[Haytham]

Right..In a tabular form. The first field is No. I want to return the number of records in the Field: No to an unbound field.
So, if the records is 1 record only for the given item, it should give me 1.
If more than 1, it should give me 0.
I tried DLookUp function as follows in the control source of my unbound field:

=DLookUp("[No]","[ContractDetails]","[EmpNo]")
and stuck up..
Any idea please with many thanks

 

[jwindon]

I would base my form off a query that calls up only that employee. Then use an unbound text box control source Count([EmployeeID]) to return the number of records for that employee.

I suspect that a DCount function is in order here. I am reading more about it now. Perhaps someone else can join in?