Solved Can't figure out this sum iif coding.... Please help!

[dawsonrhodes]

The problem is, I think, the contents of that drop-down. Rather than digging through your DB, I'll ask a more direct question.

In the record where you have an absence, what actually gets stored in the field that tells you the type of absence? Is it a code number? Is it a code letter? Is it a word like "sick" ? The WHERE clause should include the code or value and the name of the field.

E.G. if you actually use the word SICK or the lower-case version thereof, ....

WHERE [Type of Leave/Exception] = 'sick' AND other possible conditions

Obviously, if you use an exception code rather than the word 'sick' then you have to compare to something else.

Hello! Thanks for your reply and code!

The dropdown is a word, which is Sick, Emergency and so on.

So I am just unsure where this code belongs, should it be added as a field in the query design, like Number of Sick Days: WHERE [Type of Leave/Exception] = 'sick' AND other possible conditions?

I am trying to calculate the number of days between the absence start date and end date, but only for the records that are "sick", all others I am looking to return the calculation as "0".

As I mentioned one of the fields in the "Attendance Extended" Query is Number of Days: DateDiff('d',[Employee Attendance.Start Date],[Employee Attendance.End Date],+1) Which works with no issues.

Hope this clarifies what I am trying to accomplish...

Thanks in advance!

 

[The_Doc_Man]

OK, looking at the form in your first post of this thread, you show sick days in its own box. You have been given advice on this so I don't understand why you are still uncertain.

You MIGHT be looking for something like below. (BUT I must qualify my statement that there are many ways to do this.)

=DSum("[Number of Days]", "[Employee Attendance Extended]", "TypeofLeave/Exception]='sick'")

That would go in the .ControlSource property of the text box you use for sick days.

There is another issue though... that form showed us records but it didn't show employee names, IDs, or other specifiers. So unless that query somehow includes an employee selection filter, what you would get is a total across all employees. Was that what you are seeking?

 

[dawsonrhodes]

There is another issue though... that form showed us records but it didn't show employee names, IDs, or other specifiers. So unless that query somehow includes an employee selection filter, what you would get is a total across all employees. Was that what you are seeking?

Yes! This formula does work, and I did add it to the control source but yes we ran into the issue where it was totaling all entries, not just the ones belonging to the employee. This where I need some help, having the number change based on which record (employee) we have open in the employee editor (the heart of the interface)

So although that code works, it totals it all up, when I am looking for a way to have it show only the number of days "sick" for the employee selected.

Thanks again!

 

[dawsonrhodes]

Number of Days Sick: IIf([TypeofLeave/Exception]="Sick",(DateDiff('d',[Employee Attendance.Start Date],[Employee Attendance.End Date],1)+1))

--- A lovely lady on another forum saved me! This worked!

 

[theDBguy]

Number of Days Sick: IIf([TypeofLeave/Exception]="Sick",(DateDiff('d',[Employee Attendance.Start Date],[Employee Attendance.End Date],1)+1))

--- A lovely lady on another forum saved me! This worked!

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[zeroaccess]

Taking a look at your database now - some recommendations.

1. Take your types of leave and make a table out of them. Call it tblLeaveType, with 2 fields: TypeID and LeaveType. Set TypeID to be the Primary Key, Autonumber. TypeID can be Short Text. Enter your leave types in this table. Note sick is TypeID 1.

2. I would then modify your query as follows:

This isn't a complete example but it shows how to do what I described earlier. The result of this query will Sum the occurrences of type 1, or "Sick". I would probably limit it to a date range, though.

Your TypeofLeave field would be changed to not use your predefined list, and instead, your combo box on the form would source the lookup values from tblLeaveType and record the value in Employee Attendance. The wizard that appears when you place a combo box easily does this for you.

The idea is that instead of using lists to select your drop down values from, you would create a relationship to a dedicated table that holds those values. So when you record an attendance occurrence, you would select "Sick", and it would record a "1" in the table, not the text "Sick". It reduces data storage requirements, network bandwidth requirements, and normalizes your database – which enables easy and fast queries.

Hope I've given you some ideas to work with