DCount

I need help with the dcount formula for access.
My table is Schedule and i have 31 fields
01/01
01/02 and so on, which stand for days.

Each field (01/01) is lookup data from ShiftID,which is another field in table Department and the pull down value of 01/01 is S, V and H.
And I am tring to count each time an employee (record) took as sick day over the 31 day period.
How do I do that? Thanks a million. Simona

Simona,

1) How are you entering the data into your table Schedule? Are you using a form that has the 31 combobox fields on it? The reason for the question, is the DCount will count records, but not fields. From your post it sounds like you have one record per employee that has 31 fields for each day of the month and the value of either S,V or H or Null is stored in each field. Can you elaborate with a little more detail? If you need to count the number of fields that contain the value of "S" (sick day I assume) for each employee, you can do that with some simple code if you are using a form, or you can use a query and get the same results.

2) Where and how do you want this count to be displayed?

lwells

I am entering data through a form, that contains
Name
01/01
02/01 etc up to 31 fields
Total S
Total V
Total H

What I need is to know exactly how may sick day each empoyee had when I do a performace apprasal.

All the information I would like it desplaied on the same form.

Thanks a million,

Simona

Hi Simona,

Here is what I would do. Make another text box on your form to display the count of sick days. Give this what ever name that is useful to you. Next I would place a command button on your form and give it what ever name you like. Then in the On Click event of this command button use the following code:

Dim ctl As Control
Dim i As Integer
For Each ctl In Me.Form
If (ctl.ControlType = acComboBox) Then
If ctl.Value = "S" Then
With ctl
i = i + 1
End With
End If
End If
Next
NameofTextBox.Value = i


Of course use the name of your text box for the one I show in bold italics above. This will loop through the fields and count the one's that have an S in the combobox. Then it will display that number in your textbox on your form.

You can click the command button at anytime and for any of your employee's records and get the total count of sick days for that employee.

Cheers,
lwells

Hi lwells,

Thank you for so much for your imput. I have tried the code and it gives me the value of 0.

I am not sure of what I am doing wrong?

Simona

Hi Simona,

It probably has to do with what is the bound column of your combobox. Can you give me what value is stored in the bound column of the combobox. For example the bound column maybe a number that was hidden rather than the text value of S. Look in the properties of the combobox and determine which column is the bound column and then look to see what those values are. Then we can change the code slightly to make the value of ctl to be what is in the bound column and then it should work properly. The code I gave you assumed that the bound column was the S,V and H.

lwells

You are a genious it worked!!! Thanks a million.
Simona


I am also tring to create a form that when I pull down the combo box it will give me the employee address and telephone too.

Here are my tables
tblEmployees (where all the data is stored ) - Last Name, First Name
Address
Tel Home
Tel Cell
and I would like to view this data in a form when I pull down they employees name, I would like this data to update automaticaly. Do you know how to do that.

Thanks a million, you have saved me hrs of calculations.

Simona

Hi Simona,

Sure this will be easy. First create your form and use the tblEmployees as your record source. Add all the fields you want onto your form and arrange them the way you want.

Then add a combobox and use the wizard to help you build it. When the wizard opens select the option "Find a record on my form using the value I select in my combobox".

In the next prompt select the first and last name then continue with the rest of the prompts and give the combobox a suitable name when prompted maybe like cboName or whatever you want.

Now when you open your form and use the combobox, the records will match the name you selected in the combobox.

lwells

That worked grated but what I am tring to accomplish is this

Name
Telephone

and in there is another form scheduling
So there are two froms in one, but the one from the top is the only one when I pull down the meniu it updates.

Simona

Hi Simona,

Just add the telephone number to you combobox. Go to properties and in the rowsource open the query and add the telephone number to the GUID. Then change the number of columns from 2 to 3.

As far as your subform, make sure it is linked to your main form To link the subform to your main form use the properties for the subform and select Link Child or Master Fields. This will open a wizard to select which matching fields to link together. Then as you change the main form, the subform will change records to match.

If you are still having difficulties, post back.
lwells

Thank you it has worked. Do you know by any chance how to include a calendar like this example?
http://www.asgardsystems.com/ttspec.html#

Simona

Hi Simona,

Well not really. Seems alot of people would like to have some kind of calendar view in which to add data and display similar to this one, or the one that Outlook has, but unfortunately, it would strickly have to be something custom made for your particular application. I am sure you could spend the time and add all the controls to a form and arrange them in some fashion that would mimic the view but not the functionality of all the other more efficient versions available to do what you want. Its a little like trying to make a peanut butter sandwich look like a pizza...it can be done...but sure won't taste the same as pizza.

Personally, for making calendar schedules, appointments and reminders I stay with Outlook. It has far greater advantages and functionality than what I could ever begin to program into Access.

Sorry I couldn't help you on this one.
lwells

Thanks anyway lwells.

Do you know how to hide the scrolling records form the sub form so just one the scroll from the main form may be used to scroll through records?

Simona

Hi Simona,

I have never tried it before, but you might take a look at this link...its from Stephen Lebans website. He has lots of creative things he has done with Access

http://www.lebans.com/customscrollbar.htm

The example he uses is with a list box, but I am sure it can be applied to your subform just the same.

Give it a whirl and see what you think.
lwells

It has some useful info, thanks however what I need to do is to remove the next new from the sub form (i think is called a embeded subform). So when I look at the main form I see only one Previous Next and New.

Simona

Hi Simona,

Not sure I understood the question...Are you referring to the navigation buttons shown at the bottom of the subform that allows you to move between subform records?

lwells

Yes sorry I didn't know what they were called.