Using Yes/No fields to get a number count? (1 Viewer)

[stinej]

Hi,

I'm trying to use Yes/No fields to represent the days of the week with the toggle reflecting whether work was performed on a day. I want to be able to count the Yes/True responses and then use this number as a multiplier.

I'm assuming this needs to be done with code, and I'm guessing it's pretty simple - but I can't seem to get it to work. Below is what I've tried. I've declared the var earlier.

Private Sub TotalDaysPerWeek_AfterUpdate()
If [Monday] = "Y" Then GetValM = 1 Else GetValM = 0
If [Tuesday] = "Y" Then GetValT = 1 Else GetValT = 0
If [Wednesday] = "Y" Then GetValW = 1 Else GetValW = 0
If [Thursday] = "Y" Then GetValTh = 1 Else GetValTh = 0
If [Friday] = "Y" Then GetValF = 1 Else GetValF = 0

[TotalDaysPerWeek] = GetValM + GetValT + GetValW + GetValTh + GetValF + 1

Thank you.

 

[WayneRyan]

j,

True = -1
False = 0

Use this when you need to calculate. Here it's 8 hours per checkbox.

Dim Total As Long

Total = Abs((Nz(Me.Chk1, 0) + Nz(Me.Chk2, 0) + Nz(Me.Chk3, 0) + Nz(Me.Chk4, 0)) * 8)
MsgBox (Total)

Wayne

 

[stinej]

Thanks Wayne.

I think I didn't explain what I wanted to do properly... after using your code I thought about things a bit and the following is a more accurate description.

I want to build an event procedure on click for each of the check boxes where if the condition is true it will add on to a field I have named TotalDaysPerWeek.

So, when I click on a checkbox I have named Monday it will add 1 to the total of TotalDaysPerWeek and if I check it off it will subtract 1 from the same field.


Thanks! Josh

 

[WayneRyan]

Josh,

See this thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=61886&highlight=control

Wayne

 

[stinej]

Thanks Wayne, I've tried to appraoch this a couple of different ways and still no luck, I can only assume it's my syntax. Below I've pasted two simple statement that I haven't been able to get to work. I think the select case is the better of the two appraoches for what I want to do.

1.

If Me.Monday = True Then Me.TotalDaysPerWeek = (Me.TotalDaysPerWeek + 1) Else Me.TotalDaysPerWeek = Me.TotalDaysPerWeek

End If

2.

Select Case GetMonday

Case True
If Me.Monday = True Then Me.TotalDaysPerWeek = Me.TotalDaysPerWeek + 1

Case False
If Me.Monday = False Then Me.TotalDaysPerWeek = Me.TotalDaysPerWeek - 1

End Select


Where I am trying to update the field TotalDaysPerWeek on click based on whether the checkbox is true or false. The checkbox name is Monday.

Thanks for all your help! J

 

[Navyguy]

Is this helpful?

When I was playing around a while ago (learning very basic stuff), I used a crosstab query to count the selections that were checked. I did not know any code back then (still don't!!!) but I got the results that I thought were proper. I am not sure if this is helpful...

Navyguy

 

[stinej]

Thanks, but that doesn't help as the fields need to update as they are clicked.

If anyone has any ideas I would appreciate it.

Thanks,
J

 

[ChrisO]

J

It’s time to post enough of an example to test.

In A97 please so that everyone can have a crack at it.

Regards,
Chris.

 

[billyr]

Tally the Days

stinej,
Check out the attached demo db. Open frmTest1 and click days to your hearts content. Each day's onclick event calls TallyDays which updates the Total Days text box. Be sure to add error checking to TallyDays before you integrate it into your application.