469,366 Members | 2,229 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

Form to perform calculation

77
Hello,

I am working on a project for my department to design a form to calculate budgets. I really don't know much about access or programming in general.

At this point, I am trying to design a form where I can add fields to a table. (At least I think this is what I want to do). The form currently has a text box for each category of employee, which is bound to a table. Every employee category will have different rules that determine the final budget. What I need is a way to add in additional employees within a category: for example one budget might have three category one employees, two category five employees, and ten category eight employees. Each of these employees will have will then have attendant information associated with it such as the name, salary, percent of effort etc...

My form currently has a listing for each employee category. What I would like to design is either a form or subform where I can input the number of employees within each cateogry, and a new field or new text box will automatically be created.

Thanks for your help.

James
Feb 6 '07
133 10546
Rabbit
12,516 Expert Mod 8TB
Salary is complicated because each employee type has a different way that it is listed. I really appreciate what you have put together, but I don't know if you design one single querry to account for all the complications related to salary. Is it possible that you can design something that will give me a querry that just determines the number of months for each financial year based on whether there is full-time or part-time? I'm just thinking that I will have to use a different query for each situation for things to work as they should.

The reason you can't have an FY1 for 5/1/2007 is because you can potentially have a budget that starts before that date. 5/1/2007 can't be FY1 until 5/1/2007.

Thanks again.
Complicated calculations are not at all a problem when using code.

So you're saying that it has to take into account the current date? Well, you didn't say that but that's what I'm assuming from what you're saying. That and any other calculation related problems are easy to fix as long as I understand the exact process to calculate them. The main problem is getting it to run on your machine.
Mar 8 '07 #101
Rabbit
12,516 Expert Mod 8TB
If you copied everything exactly the way I laid it out, it should at least run, even if the result is incorrect at the moment.
Mar 8 '07 #102
Rabbit
12,516 Expert Mod 8TB
I can e-mail you my copy. I understand if you don't want to because I personally would never download a database from anyone I didn't know in real life.

If you want me to, just PM me your e-mail.
Mar 8 '07 #103
muhes
77
I can e-mail you my copy. I understand if you don't want to because I personally would never download a database from anyone I didn't know in real life.

If you want me to, just PM me your e-mail.
Hello Rabbit,

I have been able to duplicate your results, so that seems ok.

As far as salary goes the complications aren't so much the calculations required, but rather the variety in how each agency calculates salary. The months are the constant, but how they are used does vary. To give you an example, one agency creates a base salary based on the total project time, and then simply multiplies the percent of effort for each person by that base salary, and by the number of months. Another agency evaluates what the greatest time possible would be. The exact specifications are a little complicated to explain.

I really appreciate your willingness to help me develop this database, but I wonder if you would want to go through the specifics of each agency. I can't see how you could design one single query that could accommodate all these variables.

I think that I will be able to design the salary queries if you can help me to develop an accurate means of determining the months of each financial year. The spread system seems fine, so long as there is a way to refer back to each individual number.

With regards to the current date, I think you have the right idea. My only concern is that if it is tied to the current date, and the database is opened six months later to look at the old data, would that potentially change the numbers? Is there a way to "freeze" the current date?

Thanks.
Mar 8 '07 #104
Rabbit
12,516 Expert Mod 8TB
Hello Rabbit,

I have been able to duplicate your results, so that seems ok.

As far as salary goes the complications aren't so much the calculations required, but rather the variety in how each agency calculates salary. The months are the constant, but how they are used does vary. To give you an example, one agency creates a base salary based on the total project time, and then simply multiplies the percent of effort for each person by that base salary, and by the number of months. Another agency evaluates what the greatest time possible would be. The exact specifications are a little complicated to explain.

I really appreciate your willingness to help me develop this database, but I wonder if you would want to go through the specifics of each agency. I can't see how you could design one single query that could accommodate all these variables.

I think that I will be able to design the salary queries if you can help me to develop an accurate means of determining the months of each financial year. The spread system seems fine, so long as there is a way to refer back to each individual number.

With regards to the current date, I think you have the right idea. My only concern is that if it is tied to the current date, and the database is opened six months later to look at the old data, would that potentially change the numbers? Is there a way to "freeze" the current date?

Thanks.
That would be correct, if opened 6 months later, the data would go out of whack.
You can "freeze" the current date by either making the query a make table query that will create a table or you can have the date be a parameter so that when they go to run the query again, they can enter any date they want.
Mar 8 '07 #105
Rabbit
12,516 Expert Mod 8TB
This new code should give you what you want and it no longer calculates salary.
Expand|Select|Wrap|Line Numbers
  1. Public Function CalcSalary(BudgetID As Integer, MainID As Integer, EmpID As Integer) As String
  2.  
  3. Dim i, AY1, AY2, AY3, S1, S2, S3 As Byte
  4. Dim CurrentFY, ChangeMonth, AYMonths, SummerMonths As Byte
  5. Dim ChangeDate1, ChangeDate2, ChangeDate3, EndDate, StartDate As Date
  6.  
  7. AY1 = 0
  8. AY2 = 0
  9. AY3 = 0
  10. S1 = 0
  11. S2 = 0
  12. S3 = 0
  13. AYMonths = DLookup("[AY Months]", "TblEntry", "[Entry ID #] = " & MainID)
  14. SummerMonths = DLookup("[Summer Months]", "TblEntry", "[Entry ID #] = " & MainID)
  15. StartDate = DLookup("[Project Start Date]", "TblDate", "[Budget ID] = " & BudgetID)
  16. EndDate = DateAdd("yyyy", 1, StartDate)
  17. ChangeMonth = DLookup("[Change Month]", "TblEmployeeCategory", "[Employee ID Number] = " & EmpID)
  18. If Month(Date) < ChangeMonth Then
  19.     ChangeDate1 = CDate(ChangeMonth & "/1/" & (Year(Date) - 1))
  20. Else
  21.     ChangeDate1 = CDate(ChangeMonth & "/1/" & Year(Date))
  22. End If
  23. ChangeDate2 = DateAdd("yyyy", 1, ChangeDate1)
  24. ChangeDate3 = DateAdd("yyyy", 1, ChangeDate2)
  25.  
  26. If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
  27.     CurrentFY = 1
  28. ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
  29.     CurrentFY = 2
  30. Else
  31.     CurrentFY = 3
  32. End If
  33.  
  34. Do Until AYMonths = 0
  35.     Select Case Month(EndDate)
  36.         Case 6, 7, 8
  37.         Case Else
  38.             AYMonths = AYMonths - 1
  39.             Select Case CurrentFY
  40.                 Case 1
  41.                     AY1 = AY1 + 1
  42.                 Case 2
  43.                     AY2 = AY2 + 1
  44.                 Case 3
  45.                     AY3 = AY3 + 1
  46.             End Select
  47.     End Select
  48.     EndDate = DateAdd("m", -1, EndDate)
  49.     If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
  50. Loop
  51.  
  52. EndDate = DateAdd("yyyy", 1, StartDate)
  53. If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
  54.     CurrentFY = 1
  55. ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
  56.     CurrentFY = 2
  57. Else
  58.     CurrentFY = 3
  59. End If
  60.  
  61. Do Until SummerMonths = 0
  62.     Select Case Month(EndDate)
  63.         Case 6, 7, 8
  64.             SummerMonths = SummerMonths - 1
  65.             Select Case CurrentFY
  66.                 Case 1
  67.                     S1 = S1 + 1
  68.                 Case 2
  69.                     S2 = S2 + 1
  70.                 Case 3
  71.                     S3 = S3 + 1
  72.             End Select
  73.         Case Else
  74.     End Select
  75.     EndDate = DateAdd("m", -1, EndDate)
  76.     If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
  77. Loop
  78.  
  79. CalcSalary = AY1 & ", " & S1 & ", " & AY2 & ", " & S2 & ", " & AY3 & ", " & S3
  80.  
  81. End Function
About calculating the salary, it you really wanted to it would be possible to program every contingency so that it can return the correct salary, it would just require more and more code to account for each factor.
Mar 8 '07 #106
muhes
77
This new code should give you what you want and it no longer calculates salary.
Expand|Select|Wrap|Line Numbers
  1. Public Function CalcSalary(BudgetID As Integer, MainID As Integer, EmpID As Integer) As String
  2.  
  3. Dim i, AY1, AY2, AY3, S1, S2, S3 As Byte
  4. Dim CurrentFY, ChangeMonth, AYMonths, SummerMonths As Byte
  5. Dim ChangeDate1, ChangeDate2, ChangeDate3, EndDate, StartDate As Date
  6.  
  7. AY1 = 0
  8. AY2 = 0
  9. AY3 = 0
  10. S1 = 0
  11. S2 = 0
  12. S3 = 0
  13. AYMonths = DLookup("[AY Months]", "TblEntry", "[Entry ID #] = " & MainID)
  14. SummerMonths = DLookup("[Summer Months]", "TblEntry", "[Entry ID #] = " & MainID)
  15. StartDate = DLookup("[Project Start Date]", "TblDate", "[Budget ID] = " & BudgetID)
  16. EndDate = DateAdd("yyyy", 1, StartDate)
  17. ChangeMonth = DLookup("[Change Month]", "TblEmployeeCategory", "[Employee ID Number] = " & EmpID)
  18. If Month(Date) < ChangeMonth Then
  19.     ChangeDate1 = CDate(ChangeMonth & "/1/" & (Year(Date) - 1))
  20. Else
  21.     ChangeDate1 = CDate(ChangeMonth & "/1/" & Year(Date))
  22. End If
  23. ChangeDate2 = DateAdd("yyyy", 1, ChangeDate1)
  24. ChangeDate3 = DateAdd("yyyy", 1, ChangeDate2)
  25.  
  26. If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
  27.     CurrentFY = 1
  28. ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
  29.     CurrentFY = 2
  30. Else
  31.     CurrentFY = 3
  32. End If
  33.  
  34. Do Until AYMonths = 0
  35.     Select Case Month(EndDate)
  36.         Case 6, 7, 8
  37.         Case Else
  38.             AYMonths = AYMonths - 1
  39.             Select Case CurrentFY
  40.                 Case 1
  41.                     AY1 = AY1 + 1
  42.                 Case 2
  43.                     AY2 = AY2 + 1
  44.                 Case 3
  45.                     AY3 = AY3 + 1
  46.             End Select
  47.     End Select
  48.     EndDate = DateAdd("m", -1, EndDate)
  49.     If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
  50. Loop
  51.  
  52. EndDate = DateAdd("yyyy", 1, StartDate)
  53. If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
  54.     CurrentFY = 1
  55. ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
  56.     CurrentFY = 2
  57. Else
  58.     CurrentFY = 3
  59. End If
  60.  
  61. Do Until SummerMonths = 0
  62.     Select Case Month(EndDate)
  63.         Case 6, 7, 8
  64.             SummerMonths = SummerMonths - 1
  65.             Select Case CurrentFY
  66.                 Case 1
  67.                     S1 = S1 + 1
  68.                 Case 2
  69.                     S2 = S2 + 1
  70.                 Case 3
  71.                     S3 = S3 + 1
  72.             End Select
  73.         Case Else
  74.     End Select
  75.     EndDate = DateAdd("m", -1, EndDate)
  76.     If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
  77. Loop
  78.  
  79. CalcSalary = AY1 & ", " & S1 & ", " & AY2 & ", " & S2 & ", " & AY3 & ", " & S3
  80.  
  81. End Function
About calculating the salary, it you really wanted to it would be possible to program every contingency so that it can return the correct salary, it would just require more and more code to account for each factor.
Hello Rabbit,

Thank you for providing this modified code. Do I need a new SQL code for the query?

I really appreciate your willingness to help write out that more complex code, but I think that it would get pretty extravagant. I don't want to take advantage of your generous help. I do appreciate the offer though.

Thanks.
Mar 8 '07 #107
Rabbit
12,516 Expert Mod 8TB
Not a problem.

Yes, you will have to modify the SQL slightly. You'll want to take out the Budgeted Salary field as that is no longer supported by the Code. You will also want to remove the Boolean parameter in the Spread field.
Mar 8 '07 #108
muhes
77
Not a problem.

Yes, you will have to modify the SQL slightly. You'll want to take out the Budgeted Salary field as that is no longer supported by the Code. You will also want to remove the Boolean parameter in the Spread field.
Hello Rabbit,

I think I know what you mean about the budgeted salary field, but where do I remove the Boolean parameter in the spread field? I didn't see anything that was "As Boolean" or "bln"

Thanks
Mar 8 '07 #109
Rabbit
12,516 Expert Mod 8TB
In the Function Declaration it used to be something like:
Expand|Select|Wrap|Line Numbers
  1. Public Function CalcSalary(ID1 as Integer, ID2 as Integer, ID3 as Integer, Optional GiveString as Boolean) As Variant
So I would call the function using something like:
Expand|Select|Wrap|Line Numbers
  1. Spread: CalcSalary(EmpID, EntryID, DateID, True)
However, I removed that Boolean from the Code so you would just get rid of the last comma and True from Spread.
Mar 8 '07 #110
muhes
77
In the Function Declaration it used to be something like:
Expand|Select|Wrap|Line Numbers
  1. Public Function CalcSalary(ID1 as Integer, ID2 as Integer, ID3 as Integer, Optional GiveString as Boolean) As Variant
So I would call the function using something like:
Expand|Select|Wrap|Line Numbers
  1. Spread: CalcSalary(EmpID, EntryID, DateID, True)
However, I removed that Boolean from the Code so you would just get rid of the last comma and True from Spread.
Hi Rabbit,

I'm having a hard time determining which part of the code you are referring to. The new code you sent me today has the first line:
Expand|Select|Wrap|Line Numbers
  1. Public Function CalcSalary(BudgetID As Integer, MainID As Integer, EmpID As Integer) As String 
Which part refers to the boolean expression? Or are you referring to the SQL code?

Thanks.
Mar 8 '07 #111
Rabbit
12,516 Expert Mod 8TB
I'm referring to the SQL code. Because I removed the Boolean from the Function Code, the SQL code will have to be changed to reflect that.
Mar 8 '07 #112
muhes
77
I'm referring to the SQL code. Because I removed the Boolean from the Function Code, the SQL code will have to be changed to reflect that.
Thanks Rabbit.

I removed the true and , from the code, and now it gives me the spread for both budgeted salary and spread, which is fine. But the numbers are as they were before. Perhaps I can help troubleshoot, but I need to better understand the VBA code. I have been trying to educate myself, but it is slow going. In looking over your code I assume that:

Expand|Select|Wrap|Line Numbers
  1. If Month(Date) < ChangeMonth Then
  2.     ChangeDate1 = CDate(ChangeMonth & "/1/" & (Year(Date) - 1))
  3. Else
  4.     ChangeDate1 = CDate(ChangeMonth & "/1/" & Year(Date))
  5. End If
  6. ChangeDate2 = DateAdd("yyyy", 1, ChangeDate1)
  7. ChangeDate3 = DateAdd("yyyy", 1, ChangeDate2)
  8.  
  9. If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
  10.     CurrentFY = 1
  11. ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
  12.     CurrentFY = 2
  13. Else
  14.     CurrentFY = 3
  15. End If
  16.  
is the part of the code that determines which AY year is used. Can you help me better understand the key parts?

Thanks.
Mar 8 '07 #113
Rabbit
12,516 Expert Mod 8TB
You can get rid of the Budgeted Salary field altogether, no need for it any longer.

Expand|Select|Wrap|Line Numbers
  1. Public Function CalcSalary(BudgetID As Integer, MainID As Integer, EmpID As Integer) As String
  2.  
  3. Dim i, AY1, AY2, AY3, S1, S2, S3 As Byte
  4. Dim CurrentFY, ChangeMonth, AYMonths, SummerMonths As Byte
  5. Dim ChangeDate1, ChangeDate2, ChangeDate3, EndDate, StartDate As Date
  6.  
  7. AY1 = 0
  8. AY2 = 0
  9. AY3 = 0
  10. S1 = 0
  11. S2 = 0
  12. S3 = 0
  13. AYMonths = DLookup("[AY Months]", "TblEntry", "[Entry ID #] = " & MainID)
  14. SummerMonths = DLookup("[Summer Months]", "TblEntry", "[Entry ID #] = " & MainID)
  15. StartDate = DLookup("[Project Start Date]", "TblDate", "[Budget ID] = " & BudgetID)
  16. EndDate = DateAdd("yyyy", 1, StartDate)
  17. ChangeMonth = DLookup("[Change Month]", "TblEmployeeCategory", "[Employee ID Number] = " & EmpID)
Everything above is pretty much self explanatory.

Here is where I decide which fiscal year to start in.
If the current month is before the change month then fiscal year 1 is last year's change month to this year's change month. Otherwise, this means that the current month is either equal to or after the change month. This means fiscal year 1 is change month of this year till change month of next year.
Expand|Select|Wrap|Line Numbers
  1. If Month(Date) < ChangeMonth Then
  2.     ChangeDate1 = CDate(ChangeMonth & "/1/" & (Year(Date) - 1))
  3. Else
  4.     ChangeDate1 = CDate(ChangeMonth & "/1/" & Year(Date))
  5. End If
  6. ChangeDate2 = DateAdd("yyyy", 1, ChangeDate1)
  7. ChangeDate3 = DateAdd("yyyy", 1, ChangeDate2)
Since we want to budget for the largest amount possible, we start with the end date which is a year from the start date. This tells it which fiscal year to start in.
Expand|Select|Wrap|Line Numbers
  1. If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
  2.     CurrentFY = 1
  3. ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
  4.     CurrentFY = 2
  5. Else
  6.     CurrentFY = 3
  7. End If
This code loops until AYMonths is 0, each loop will step back one month in time and if it finds an academic month, it will add one to that academic year. But it will do nothing if the month is 6,7,8 which are summer months.
Expand|Select|Wrap|Line Numbers
  1. Do Until AYMonths = 0
  2.     Select Case Month(EndDate)
  3.         Case 6, 7, 8
  4.         Case Else
  5.             AYMonths = AYMonths - 1
  6.             Select Case CurrentFY
  7.                 Case 1
  8.                     AY1 = AY1 + 1
  9.                 Case 2
  10.                     AY2 = AY2 + 1
  11.                 Case 3
  12.                     AY3 = AY3 + 1
  13.             End Select
  14.     End Select
  15.     EndDate = DateAdd("m", -1, EndDate)
  16.     If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
  17. Loop
This just resets everything and does the same thing except for summer months this time.
Expand|Select|Wrap|Line Numbers
  1. EndDate = DateAdd("yyyy", 1, StartDate)
  2. If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
  3.     CurrentFY = 1
  4. ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
  5.     CurrentFY = 2
  6. Else
  7.     CurrentFY = 3
  8. End If
  9.  
  10. Do Until SummerMonths = 0
  11.     Select Case Month(EndDate)
  12.         Case 6, 7, 8
  13.             SummerMonths = SummerMonths - 1
  14.             Select Case CurrentFY
  15.                 Case 1
  16.                     S1 = S1 + 1
  17.                 Case 2
  18.                     S2 = S2 + 1
  19.                 Case 3
  20.                     S3 = S3 + 1
  21.             End Select
  22.         Case Else
  23.     End Select
  24.     EndDate = DateAdd("m", -1, EndDate)
  25.     If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
  26. Loop
  27.  
  28. CalcSalary = AY1 & ", " & S1 & ", " & AY2 & ", " & S2 & ", " & AY3 & ", " & S3
  29.  
  30. End Function
Mar 9 '07 #114
muhes
77
You can get rid of the Budgeted Salary field altogether, no need for it any longer.

Expand|Select|Wrap|Line Numbers
  1. Public Function CalcSalary(BudgetID As Integer, MainID As Integer, EmpID As Integer) As String
  2.  
  3. Dim i, AY1, AY2, AY3, S1, S2, S3 As Byte
  4. Dim CurrentFY, ChangeMonth, AYMonths, SummerMonths As Byte
  5. Dim ChangeDate1, ChangeDate2, ChangeDate3, EndDate, StartDate As Date
  6.  
  7. AY1 = 0
  8. AY2 = 0
  9. AY3 = 0
  10. S1 = 0
  11. S2 = 0
  12. S3 = 0
  13. AYMonths = DLookup("[AY Months]", "TblEntry", "[Entry ID #] = " & MainID)
  14. SummerMonths = DLookup("[Summer Months]", "TblEntry", "[Entry ID #] = " & MainID)
  15. StartDate = DLookup("[Project Start Date]", "TblDate", "[Budget ID] = " & BudgetID)
  16. EndDate = DateAdd("yyyy", 1, StartDate)
  17. ChangeMonth = DLookup("[Change Month]", "TblEmployeeCategory", "[Employee ID Number] = " & EmpID)
Everything above is pretty much self explanatory.

Here is where I decide which fiscal year to start in.
If the current month is before the change month then fiscal year 1 is last year's change month to this year's change month. Otherwise, this means that the current month is either equal to or after the change month. This means fiscal year 1 is change month of this year till change month of next year.
Expand|Select|Wrap|Line Numbers
  1. If Month(Date) < ChangeMonth Then
  2.     ChangeDate1 = CDate(ChangeMonth & "/1/" & (Year(Date) - 1))
  3. Else
  4.     ChangeDate1 = CDate(ChangeMonth & "/1/" & Year(Date))
  5. End If
  6. ChangeDate2 = DateAdd("yyyy", 1, ChangeDate1)
  7. ChangeDate3 = DateAdd("yyyy", 1, ChangeDate2)
Since we want to budget for the largest amount possible, we start with the end date which is a year from the start date. This tells it which fiscal year to start in.
Expand|Select|Wrap|Line Numbers
  1. If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
  2.     CurrentFY = 1
  3. ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
  4.     CurrentFY = 2
  5. Else
  6.     CurrentFY = 3
  7. End If
This code loops until AYMonths is 0, each loop will step back one month in time and if it finds an academic month, it will add one to that academic year. But it will do nothing if the month is 6,7,8 which are summer months.
Expand|Select|Wrap|Line Numbers
  1. Do Until AYMonths = 0
  2.     Select Case Month(EndDate)
  3.         Case 6, 7, 8
  4.         Case Else
  5.             AYMonths = AYMonths - 1
  6.             Select Case CurrentFY
  7.                 Case 1
  8.                     AY1 = AY1 + 1
  9.                 Case 2
  10.                     AY2 = AY2 + 1
  11.                 Case 3
  12.                     AY3 = AY3 + 1
  13.             End Select
  14.     End Select
  15.     EndDate = DateAdd("m", -1, EndDate)
  16.     If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
  17. Loop
This just resets everything and does the same thing except for summer months this time.
Expand|Select|Wrap|Line Numbers
  1. EndDate = DateAdd("yyyy", 1, StartDate)
  2. If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
  3.     CurrentFY = 1
  4. ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
  5.     CurrentFY = 2
  6. Else
  7.     CurrentFY = 3
  8. End If
  9.  
  10. Do Until SummerMonths = 0
  11.     Select Case Month(EndDate)
  12.         Case 6, 7, 8
  13.             SummerMonths = SummerMonths - 1
  14.             Select Case CurrentFY
  15.                 Case 1
  16.                     S1 = S1 + 1
  17.                 Case 2
  18.                     S2 = S2 + 1
  19.                 Case 3
  20.                     S3 = S3 + 1
  21.             End Select
  22.         Case Else
  23.     End Select
  24.     EndDate = DateAdd("m", -1, EndDate)
  25.     If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
  26. Loop
  27.  
  28. CalcSalary = AY1 & ", " & S1 & ", " & AY2 & ", " & S2 & ", " & AY3 & ", " & S3
  29.  
  30. End Function

Hello Rabbit,

After looking this over it is difficult to tell, but it seems that it might not be counting the start month. I tried it, and in order to get the numbers for October, I have to input September. Is this possible?

Also, which part of the code is attached to the employee type? I think that this will be important for the later salary calculations.

Thanks.
Mar 9 '07 #115
Rabbit
12,516 Expert Mod 8TB
Hello Rabbit,

After looking this over it is difficult to tell, but it seems that it might not be counting the start month. I tried it, and in order to get the numbers for October, I have to input September. Is this possible?

Also, which part of the code is attached to the employee type? I think that this will be important for the later salary calculations.

Thanks.
Anything that has to do with change month stems from the Employee Type.

Yes, it's not counting the start month because of the way the dates are set up. Try subtracting a day from the End Date. This might bring up issues later but we'll get to that when we get to it.
Mar 9 '07 #116
muhes
77
Anything that has to do with change month stems from the Employee Type.

Yes, it's not counting the start month because of the way the dates are set up. Try subtracting a day from the End Date. This might bring up issues later but we'll get to that when we get to it.
Hello Rabbit

I tried to subtract 1 month from End Date, but I think I did something wrong because it had no apparent effect:
Expand|Select|Wrap|Line Numbers
  1. EndDate = DateAdd("yyyy", 1, StartDate - (Month(Date) - 1))
  2.  
Can you tell me the format for month?

Also, I noticed something really weird. I was able to get the query to work by duplicating your exact tables, but in order to due this I had to change the date ID to a number from and auto-number. When I deleted the records and changed it back to auto-number, trying to get the new table to conform to my form, it no longer functions. Any idea why?

Thanks
Mar 9 '07 #117
Rabbit
12,516 Expert Mod 8TB
Hello Rabbit

I tried to subtract 1 month from End Date, but I think I did something wrong because it had no apparent effect:
Expand|Select|Wrap|Line Numbers
  1. EndDate = DateAdd("yyyy", 1, StartDate - (Month(Date) - 1))
  2.  
Can you tell me the format for month?

Also, I noticed something really weird. I was able to get the query to work by duplicating your exact tables, but in order to due this I had to change the date ID to a number from and auto-number. When I deleted the records and changed it back to auto-number, trying to get the new table to conform to my form, it no longer functions. Any idea why?

Thanks
To subtract one month, you use:
Expand|Select|Wrap|Line Numbers
  1. EndDate = DateAdd("m", -1, EndDate)
I'm not sure what you mean by getting the new table to conform to your form. Did you change the name of the table? Then name of the fields? If these things change, you have to change the settings of your form to match.

When you bind a form to a table, this does not mean that any changes made to the underlying table gets reflected in the form.
Mar 9 '07 #118
muhes
77
To subtract one month, you use:
Expand|Select|Wrap|Line Numbers
  1. EndDate = DateAdd("m", -1, EndDate)
I'm not sure what you mean by getting the new table to conform to your form. Did you change the name of the table? Then name of the fields? If these things change, you have to change the settings of your form to match.

When you bind a form to a table, this does not mean that any changes made to the underlying table gets reflected in the form.
Hello Rabbit,

Good news, the code is now providing the correct months.

With regard to the problem I am having with TblDate, I haven't changed the name of the tables or any of the fields. The only thing changed is the data type of the [Budget ID] field, changed from a number to autonumber. This requires me to first delete the records that exist, and then create new records. When I do this, the query doesn't seem to work. I want this field to be an autonumber but in order to perfectly duplicate your tables, I had to change it. Any idea what is happening? Should I perhaps delete the table and recreate it?

Thanks
Mar 9 '07 #119
Rabbit
12,516 Expert Mod 8TB
Hello Rabbit,

Good news, the code is now providing the correct months.

With regard to the problem I am having with TblDate, I haven't changed the name of the tables or any of the fields. The only thing changed is the data type of the [Budget ID] field, changed from a number to autonumber. This requires me to first delete the records that exist, and then create new records. When I do this, the query doesn't seem to work. I want this field to be an autonumber but in order to perfectly duplicate your tables, I had to change it. Any idea what is happening? Should I perhaps delete the table and recreate it?

Thanks
That's good to hear.

That's odd because my TblDate was Autonumber in the first place and it worked. Yes, try recreating the table and see if that fixes things.
Mar 9 '07 #120
muhes
77
That's good to hear.

That's odd because my TblDate was Autonumber in the first place and it worked. Yes, try recreating the table and see if that fixes things.
Hi Rabbit,

That seemed to work. However, I have a new problem related to my form. As you might recall the way we set this up was to do a main form which has [project start date] and [# of Years]. Then there is a subform which has info from two tables: TblEntry [Name] (person name), [Salary], [AY Months], [Summer Months], [AY Percent of Effort], [Summer Percent of Effort], and from TblEmployees [Name] (position name), this field is a combo box.

The form is setup as a datasheet.

Each budget ID should represent a separate budget. When I click on a new record for the main form, it isn't different from the first Budget ID. Any idea why? Also, I have had trouble creating relationships. [Budget ID] should have a one to many relationship with TblEntry. But when I do create that, I get an error message. What can I do to change the structure so that it functions appropriately?

Also, I wanted to start developing separate queries for the salaries, but I need to be able to refer to the various numbers of the spread field created by your query. How do I do this? Or should I try to reform the VBA to create a separate identity for each AY, S number?

Thanks.
Mar 9 '07 #121
Rabbit
12,516 Expert Mod 8TB
Hi Rabbit,

That seemed to work. However, I have a new problem related to my form. As you might recall the way we set this up was to do a main form which has [project start date] and [# of Years]. Then there is a subform which has info from two tables: TblEntry [Name] (person name), [Salary], [AY Months], [Summer Months], [AY Percent of Effort], [Summer Percent of Effort], and from TblEmployees [Name] (position name), this field is a combo box.

The form is setup as a datasheet.

Each budget ID should represent a separate budget. When I click on a new record for the main form, it isn't different from the first Budget ID. Any idea why? Also, I have had trouble creating relationships. [Budget ID] should have a one to many relationship with TblEntry. But when I do create that, I get an error message. What can I do to change the structure so that it functions appropriately?

Also, I wanted to start developing separate queries for the salaries, but I need to be able to refer to the various numbers of the spread field created by your query. How do I do this? Or should I try to reform the VBA to create a separate identity for each AY, S number?

Thanks.
This is because you have to tell it to assign the budget ID field from the main form to the budget ID field of the subform when you create a new record.

I have no problems setting up the relationships, what's the error message that you're getting?

You can refer to the various numbers using a variety of methods. One of which is to change the VBA. I originally did it this way because I didn't want to have the function called 6 different times for each record. One way is to change the VBA to return each number seperately.

Another way is to use the Mid() function to extract the number you want because they'll always be in the same position and format.
Mar 9 '07 #122
muhes
77
This is because you have to tell it to assign the budget ID field from the main form to the budget ID field of the subform when you create a new record.

I have no problems setting up the relationships, what's the error message that you're getting?

You can refer to the various numbers using a variety of methods. One of which is to change the VBA. I originally did it this way because I didn't want to have the function called 6 different times for each record. One way is to change the VBA to return each number seperately.

Another way is to use the Mid() function to extract the number you want because they'll always be in the same position and format.
Is the Mid() function a SQL function as well? How is it structured?

Thanks.
Mar 10 '07 #123
Rabbit
12,516 Expert Mod 8TB
Mid is function that can be used anywhere.
Expand|Select|Wrap|Line Numbers
  1. Mid(String, Start Position, Length)
First parameter is a string, in this case, Spread. Start position is an integer, this will change depending on which number you want. Length is how many characters to return, in this case, 1.
Mar 10 '07 #124
muhes
77
Mid is function that can be used anywhere.
Expand|Select|Wrap|Line Numbers
  1. Mid(String, Start Position, Length)
First parameter is a string, in this case, Spread. Start position is an integer, this will change depending on which number you want. Length is how many characters to return, in this case, 1.
Hi Rabbit,

So to find the salary for a category 1 employee could I create a query with a criteria of 1 for that field, and then an expression something like:

Expand|Select|Wrap|Line Numbers
  1. [Salary]/9*Mid(Spread,1,1) + [Salary]/9*Mid(Spread,2,1) + [Salary]/9*Mid(Spread,3,1)*1.034  +[Salary]/9*Mid(Spread,4,1)*1.034 + [Salary]/9*Mid(Spread,5,1)*1.034*1.034 + [Salary]/9*Mid(Spread,6,1)*1.034*1.034 
  2.  
?

Thanks.
Mar 10 '07 #125
Rabbit
12,516 Expert Mod 8TB
Close, in spread, each number is seperated with a "-".
So AY Months 1 is position 1, Summer Months 1 is position 3, and so on.
Mid returns a string so you may have to surround it with the Val() function.
Mar 11 '07 #126
muhes
77
Close, in spread, each number is seperated with a "-".
So AY Months 1 is position 1, Summer Months 1 is position 3, and so on.
Mid returns a string so you may have to surround it with the Val() function.
Hello Rabbit,

So if I am understanding this correctly you are saying that the spaces also count, so it would go something like 1,3,5,7 etc...; correct?

Also in terms of surrounding a function can I put the Val() function only at the beginning:

Expand|Select|Wrap|Line Numbers
  1. Val([Salary]/9*Mid(Spread,1,1) + [Salary]/9*Mid(Spread,3,1) + [Salary]/9*Mid(Spread,5,1)*1.034  +[Salary]/9*Mid(Spread,7,1)*1.034 + [Salary]/9*Mid(Spread,9,1)*1.034*1.034 + [Salary]/9*Mid(Spread,11,1)*1.034*1.034)
  2.  
as compared to:

Expand|Select|Wrap|Line Numbers
  1. [Salary]/9*Val(Mid(Spread,1,1)) + [Salary]/9*Val(Mid(Spread,3,1)) + [Salary]/9*Val(Mid(Spread,5,1))*1.034 etc...
  2.  
Thanks,

James
Mar 12 '07 #127
NeoPa
32,185 Expert Mod 16PB
In answer to your first question - Any character in a string takes up a position in the string whether it's a space or punctuation or any other character.
In answer to the Val() question - Val() should surround any string when used within a formula. Val, basically, converts a string to the numeric equivalent. This can be done automatically in some circumstances by VBA itself, but it is better to do it explicitly as then there is no ambiguity.
Consider the formula :
Expand|Select|Wrap|Line Numbers
  1. 5 * "3.2"
Essentially, because the "3.2" side is a string, it is useless in this formula.
It should be :
Expand|Select|Wrap|Line Numbers
  1. 5 * Val("3.2")
This will always produce the correct result (16).
Mar 12 '07 #128
MMcCarthy
14,534 Expert Mod 8TB
This thread is already way too long for anyone else to wade through.

Please post a new thread for this new question.

ADMIN
Mar 12 '07 #129
NeoPa
32,185 Expert Mod 16PB
Mary,
I've previously PMed both parties explaining the normal situation with threads and what can, and cannot, be expected from our experts. I got a reply from Rabbit indicating he was prepared to keep looking after this thread as a form of private party and the OP understands that this only continues on his continued interest. I've popped in every now and again to post simple answers and keep an eye on it periodically.
It's almost a form of tutelage :)
Certainly no other experts are required to contribute nor expected to by either party.
Mar 12 '07 #130
MMcCarthy
14,534 Expert Mod 8TB
Mary,
I've previously PMed both parties explaining the normal situation with threads and what can, and cannot, be expected from our experts. I got a reply from Rabbit indicating he was prepared to keep looking after this thread as a form of private party and the OP understands that this only continues on his continued interest. I've popped in every now and again to post simple answers and keep an eye on it periodically.
It's almost a form of tutelage :)
Certainly no other experts are required to contribute nor expected to by either party.
Post# 129 indicated a new problem which is why I posted. I understand that Rabbit make this commitment but at some point this has to move to a new thread and I felt that a new problem was the time to do that.

This thread will be practically useless to anyone searching for answers to their problems as they will never wade through all the posts and a new thread will at least keep subsequent answers in a managable place. I appreciate Rabbit's commitment to helping the OP but a new thread won't negate that. The OP could even stress that Rabbit has been assisting them and a link could be posted to this thread. One of the great things about the scripts is that answers can be found by members searching without them having to post a question themselves.

However, I don't want to impose too much on this situation and will leave the decision to the OP and Rabbit.

Mary
Mar 12 '07 #131
Rabbit
12,516 Expert Mod 8TB
I don't mind starting a new thread.

Continued here.
Mar 12 '07 #132
NeoPa
32,185 Expert Mod 16PB
Post# 129 indicated a new problem which is why I posted. I understand that Rabbit make this commitment but at some point this has to move to a new thread and I felt that a new problem was the time to do that.

This thread will be practically useless to anyone searching for answers to their problems as they will never wade through all the posts and a new thread will at least keep subsequent answers in a managable place. I appreciate Rabbit's commitment to helping the OP but a new thread won't negate that. The OP could even stress that Rabbit has been assisting them and a link could be posted to this thread. One of the great things about the scripts is that answers can be found by members searching without them having to post a question themselves.

However, I don't want to impose too much on this situation and will leave the decision to the OP and Rabbit.

Mary
That's fine Mary.
I just wanted to bring the extraordinary nature of this particular thread to your attention. Not trying to be rebellious (Me ? As if!).
Mar 12 '07 #133
MMcCarthy
14,534 Expert Mod 8TB
That's fine Mary.
I just wanted to bring the extraordinary nature of this particular thread to your attention. Not trying to be rebellious (Me ? As if!).
Never Ade :p

I know you wouldn't do something like that.
Mar 12 '07 #134

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by DebbieG | last post: by
reply views Thread by gavo | last post: by
2 posts views Thread by RICHARD BROMBERG | last post: by
4 posts views Thread by vg-mail | last post: by
2 posts views Thread by campos | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.