Solved Can't figure out this sum iif coding.... Please help! (1 Viewer)

[dawsonrhodes]

Hello,

I am creating an employee database, and am working on tracking attendance. Below is a working code to count all occurrences no matter what type of category (sick, emergency, etc.)

=CDbl(Nz(Sum([Number of Days]),0))

I am struggling with making a code that references the "Number of Days" field, but targets records that have only "Sick". I have tried many different ways but cannot figure this out.

This data is used for the below stats page which is referenced as a subform which will display the data for the selected person. I have attached a screenshot below
Thanks,

Dawson

 

[theDBguy]

Hi. Have you tried using DSum()?

 

[dawsonrhodes]

Hi. Have you tried using DSum()?

Hello, I am not sure how to use this function, and how to make that target only the entries that have "Sick" as the data...

Is there anyway you could give me an example to try by chance?

Thanks!

 

[theDBguy]

Hello, I am not sure how to use this function, and how to make that target only the entries that have "Sick" as the data...

Is there anyway you could give me an example to try by chance?

Thanks!

Hi. Check here.

Application.DSum method (Access)

Office VBA reference topic

docs.microsoft.com

 

[dawsonrhodes]

Hi. Check here.

Application.DSum method (Access)

Office VBA reference topic

docs.microsoft.com

=CDbl(Nz(DSum("[Number of Days]","Employee Attendance Extended","[Sick] = 'string' "))) doesn't work

and

=DSum("[Number of Days]","Employee Attendance Extended","[Sick] = 'string' ") doesn't work

Maybe I am misunderstanding? {#of Days is the field, Employee Attendance Extended is the query, and [Sick] is the the data)

 

[MajP]

at a minimum your query name also needs [] since it has spaces. Do not put spaces in ANY names.
But what are you trying here ""[Sick] = 'string' "? I am guessing you do not have a field named sick where you store the word "string" in it. Are you trying to check it is not empty or True?
Sick = True ?
or
Sick Is Not Null?
Or is
[SomeOtherField] = 'Sick'

 

[dawsonrhodes]

at a minimum your query name also needs [] since it has spaces. Do not put spaces in ANY names.
But what are you trying here ""[Sick] = 'string' "? I am guessing you do not have a field named sick where you store the word "string" in it. Are you trying to check it is not empty or True?
Sick = True ?
or
Sick Is Not Null?
Or is
[SomeOtherField] = 'Sick'

Hello,

I just realized I made an error

=CDbl(Nz(DSum("[Number of Days]","[Employee Attendance Extended]","[TypeofLeave/Exception]" = 'Sick' ")))
.............................................^ is a field........................^ is the query.......................................^ is the field..........................^ is the data I want to count

Number of days is just calculating the amount of days between two dates.

Type of Leave/Exception contains sick, emergency, and so on.

I am trying to make it so that the control source has a code that will only count if the type of leave is "sick"

sorry, hope this makes sense

 

[zeroaccess]

A simple query with a total field on Type, with Criteria Sick (or the ID for sick) would do the trick.

 

[dawsonrhodes]

A simple query with a total field on Type, with Criteria Sick (or the ID for sick) would do the trick.

It would but that field is a dropdown with about 10 options, I just want to calculate each lookup value in the combo box, this also needs to be done in the form as a control source, or it will not work and update as needed.

 

[Cronk]

If you are wanting the sum of days depending on the value of the Type of Leave selected in the combo on your form, that would be

=CDbl(Nz(DSum("[Number of Days]","[Employee Attendance Extended]","[TypeofLeave/Exception] = '" & me.YourComboName & "'")))

 

[dawsonrhodes]

If you are wanting the sum of days depending on the value of the Type of Leave selected in the combo on your form, that would be

=CDbl(Nz(DSum("[Number of Days]","[Employee Attendance Extended]","[TypeofLeave/Exception] = '" & me.YourComboName & "'")))

what does & me.YourComboName & represent? the combo name is sick, so not sure how to format that

 

[Cronk]

The intention as I did not previously know the name of your control, that you would replace YourComboName with sick

I'll assume you are wanting to display the total in a text box, so you can make it simpler by making the controlsource of the text box set to

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

Incidentally, one of the things you learn passing through the novice stage using Access, is to omit spaces and special character in table/field names so you don't have to remember to use square brackets. Another to make it easier to come back to previous work is to preface the names of your controls with their type eg cboSick or txtTotalEmployees

 

[dawsonrhodes]

The intention as I did not previously know the name of your control, that you would replace YourComboName with sick

I'll assume you are wanting to display the total in a text box, so you can make it simpler by making the controlsource of the text box set to

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

Incidentally, one of the things you learn passing through the novice stage using Access, is to omit spaces and special character in table/field names so you don't have to remember to use square brackets. Another to make it easier to come back to previous work is to preface the names of your controls with their type eg cboSick or txtTotalEmployees

This still didn't work, I am trying to use the code as the control source when I open the "wizard" which will be the main interface for my Supervisors, I have made a module that works that counts all days that one employee has been absent using "=CDbl(Nz(Sum([Number of Days]),0))", this works exactly as intended and will vary when I select a different employee. Maybe I can send you a stripped copy and you can take a look? I have no idea why this is the case.

 

[zeroaccess]

It's generally recommended to use the SQL engine instead of domain functions for things like this. Just about everything on that form could be part of the form's record source query, so that's the route I'd take.

 

[dawsonrhodes]

It's generally recommended to use the SQL engine instead of domain functions for things like this. Just about everything on that form could be part of the form's record source query, so that's the route I'd take.

I have no idea where to begin with that. I am now trying to write the formula right into the query, as my "number of days" function is Number of Days: DateDiff('d',[Employee Attendance.Start Date],[Employee Attendance.End Date],+1)

which works great, trying to make it only count if type of leave = sick though.

 

[jdraw]

What exactly defines type of leave as "sick"? We need to see some of your table design to ensure field/column names and your query efforts. It would also help if you could describe in plain English what you are trying to do.
As has been advised earlier, having spaces in names in Access will come back to bite you.

 

[dawsonrhodes]

@Cronk @theDBguy @jdraw

https://drive.google.com/file/d/1FtK9gtCIApNDBuAif1xsyoHDOS3HdkOK/view?usp=sharing is the database due to it being too large for the site.

Above is the access database, have stripped it and removed 204 employees. I am going to take the advice and change names to no spaces and have identifiers (cbo,txt, etc.) once I figure this out and show my proof of concept to my team and boss.

As I'm sure you all know using macros will be asked to be enabled, if you do it will hide all options, in that case, hit "Tools" and enter password "hide". This is so that my staff won't be overwhelmed or enter data where it is not meant to be.

This database was built with the "Student" template but modified quite a bit to fit my needs.

Attendance is a huge issue at my work, and tracking is a disaster. The database is basically outfitted to use exclusively by my staff in the "Employee Menu (Form Name Employee Details). Opening this will default to a blank "Add New Employee" screen. Select one of the names in the dropdown at the header or search by name, number, etc. Anyways!, when I select myself (Dawson, RHODES) I will use the "Attendance Detailed" tab to track all instances and use the "Attendance Viewer" tab to see a general overview for each employee. In that "Attendance Viewer" tab, there are two subforms, they are copies of each other but one is used strictly for the datasheet view, and one for the header which is the "Statistics".

The subforms are linked to the "Employee Attendance Extended" query. In this there is a date formula to calculate the number of days between the absence start date and end date. That data is used in the "Statistics - Days on Leave" box's control source. This works great because when I open the "Employee Menu" this number varies based on which employee is being selected!

So here is exactly what I want to do...

Create a working formula to calculate the number of days an employee is absent based on what type of leave they are on... Sick, Emergency, etc., which likely needs to be built right into the query as suggested, but I have been unsuccessful.

(( I now realize it has to be built into the query or else it will calculate all entries and will not vary between employee to employee. I have successfully coded this using "Criteria" but that then makes it only show entries in the query that match leave type of "Sick", which I do not want. ))

If you are planning on downloading and seeing the database be advised that because all the users have been stripped, there are only 2 attendance records, and you may need to add several to both AWA and RHODES in order to generate some records. This can be done directly in the table or in the "Menu" in the second tab!

I hope this helps and makes sense and I hope you guys can assist me as I am still learning but and amazed how much I now understand about basics!

 

[The_Doc_Man]

I am trying to make it so that the control source has a code that will only count if the type of leave is "sick"

It would but that field is a dropdown with about 10 options,

These two statements have little or nothing to do with each other.

If a .RecordSource is involved, it is a query (unless it is a table name). Make it a query with a WHERE clause to only select what you wanted to count. Otherwise, your description of what you want is somehow flawed.

 

[dawsonrhodes]

These two statements have little or nothing to do with each other.

If a .RecordSource is involved, it is a query (unless it is a table name). Make it a query with a WHERE clause to only select what you wanted to count. Otherwise, your description of what you want is somehow flawed.

Hello,

I am unsure how to write that into a code as I have tried that as well.

 

[The_Doc_Man]

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.