Dcount

[venu_resoju]

I have one "Attendance" table which contain

1. EmpID
2. EmpName
3. Attendance Status ("Present", "Absent")
4. AttendanceDay
5. AttendanceMonth
6. AttendanceYear

one "Salary" Form contains:

1. EmpID (Combo Box1)
2. EmpName
3. Month (Combo Box2)
4. Year (Combo Box3)
5. Total Working Days (Text Box)
6. No of Days Worked (Text Box)

I want to display(count) "Total Working Days" and "No of Days Worked" when I select the above 3 Combo boxes.
How can do/write the code? Please tell me any one

 

[Khalid_Afridi]

Hello Venu,

This might helphttp://allenbrowne.com/casu-13.html

 

[venu_resoju]

Thank you Afrid for your quick response

I want only "Present" Days count not time calculation. any ideas...?

 

[Khalid_Afridi]

Okay! you can change the code to get the duration for days here:

Minutes: DateDiff("n", [StartDateTime], [EndDateTime])
to
Days: DateDiff("d", [StartDateTime], [EndDateTime])

Try to understand the DateDiff Functionalso

 

[venu_resoju]

I don;t want the fields "Time in" and "Time Out".

Just I have a field "Attendance Status" of the Day ( like storing "Present" or "Absent" in that field).

I want to count how many "Present" Days of selected Month & Year(Combo box1 & Combo box2) of selected employee(Combo box3)

 

[Khalid_Afridi]

Dcount Function could solve this:

dim lngCnt as long
lngCnt = DCount("[Attendance Status]", "Table1", "EMPID = 1")
Me.TextBox.Value= lngCnt

This is an Air code, change the bit of Table1 and EmpID to your table and Field name.

Another methode is to Open a recordset and count for the "Present" and "Absent" criteria which is more flixable:

Dim strSQL As String
Dim db As DAO.Database
Dim rsSQL As DAO.Recordset

Set db = CurrentDb
strSQL = " Select Count Statement with Where Clause"
Set rsSQL = CurrentDb.OpenRecordset(strSQL)

me.txtBox1.value = rsSQL!CountResultField ' this would be your count field and txtBox1 is the textbox on your form.

this is an air code, sorry for SELECT statement coz I dont have the actual field of your database.

 

[venu_resoju]

I am unable to do this ...I am attaching my DB.Please do something...Thanks in Advance...

 

[venu_resoju]

"text box11" and "text box15" are using for ruff ideas...

 

[venu_resoju]

I have used this code at control source of my "text box", but it is not working. .

I want to count "Present" days of Employee from "Attendance" table by using combo boxes of "cboEmpID", "AtnMonth" and "AtnYear". please help me anyone

=DCount("[AttendannceStatus]","Attendance","[EmpID]='" & [cboEmpID.Text] & "' AND [AttendanceMonth]='" & [AtnMonth.Text] & "' AND [AttendanceYear]='" & [AtnYear.Text] & "'")