gracie1972
asked on
Quarters and Total days calculations not working correctly on a Form/Query
I have a table that I created from a Query out of SQL.
tblFiscalQuarters_2
The Data looks where my data like similar to this:
FStart FEnd Year FYear FQ Days Vacation Weekends Total
1/1/2012 2/29/2012 2012 FY2012 FQ3 60 2 17 41
3/1/2012 5/31/2012 2012 FY2012 FQ4 92 1 26 64
6/1/2012 8/31/2012 2012 FY2013 FQ1 92 1 26 64
9/1/2012 11/30/2012 2012 FY2013 FQ2 91 3 26 62
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
SECOND TABLE:
Copy Of tblProjects1
ProjectID
Project
FStartQ
FEndQ
Days
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
On my form in Access I will select:
ProjectID
Project
FStartQ:
FYear (ComboBox pulling from tblFiscalQuarts), then FQ (populated by VB Code) , then Days (populated by VB Code).
FEndQ:
FYear (ComboBox pulling from tblFiscalQuarts), then FQ (populated by VB Code) , then Days (populated by VB Code).
of days(populated by VB Code).
Both Days fields total (VB Script) and are stored the main table.
DAYS:
Days need to automatically sum (FEndQ.Days + FStartQ.Days) and store in main table.
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----
However if my users choose FY2013, FQ1 and FY2014 FQ1, it is not totaling all the days in the range.
Plus my VB Code is concatenating the numbers and not subtracting them.
I attached the DB to make it easier to understand what I am doing.
Database1.mdb
tblFiscalQuarters_2
The Data looks where my data like similar to this:
FStart FEnd Year FYear FQ Days Vacation Weekends Total
1/1/2012 2/29/2012 2012 FY2012 FQ3 60 2 17 41
3/1/2012 5/31/2012 2012 FY2012 FQ4 92 1 26 64
6/1/2012 8/31/2012 2012 FY2013 FQ1 92 1 26 64
9/1/2012 11/30/2012 2012 FY2013 FQ2 91 3 26 62
--------------------------
SECOND TABLE:
Copy Of tblProjects1
ProjectID
Project
FStartQ
FEndQ
Days
--------------------------
On my form in Access I will select:
ProjectID
Project
FStartQ:
FYear (ComboBox pulling from tblFiscalQuarts), then FQ (populated by VB Code) , then Days (populated by VB Code).
FEndQ:
FYear (ComboBox pulling from tblFiscalQuarts), then FQ (populated by VB Code) , then Days (populated by VB Code).
of days(populated by VB Code).
Both Days fields total (VB Script) and are stored the main table.
DAYS:
Days need to automatically sum (FEndQ.Days + FStartQ.Days) and store in main table.
--------------------------
However if my users choose FY2013, FQ1 and FY2014 FQ1, it is not totaling all the days in the range.
Plus my VB Code is concatenating the numbers and not subtracting them.
I attached the DB to make it easier to understand what I am doing.
Database1.mdb
ASKER
What if they want to do a range? Your VB Code worked.
However, if there are:
NikeQtr DaysCount
FY2013 Q1 92
FY2013 Q2 91
FY2013 Q3 59
FY2013 Q4 92
And the user chooses FY2013 Q1 ending FY2013 Q3 How does can I know to calculate the range of all ? 92+91+59 ?
However, if there are:
NikeQtr DaysCount
FY2013 Q1 92
FY2013 Q2 91
FY2013 Q3 59
FY2013 Q4 92
And the user chooses FY2013 Q1 ending FY2013 Q3 How does can I know to calculate the range of all ? 92+91+59 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect! Thanks!
Private Sub QEnd_AfterUpdate()
Me.QEndDays.Value = Me.QEnd.Column(2)
Me.CountDays.Value = CLng(Me.QStartDays.Value) + CLng(Me.QEndDays.Value)
End Sub