Form to perform calculation

[muhes]

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

[Rabbit]

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.

[Rabbit]

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.

[Rabbit]

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.

[muhes]

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.

[Rabbit]

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.

[Rabbit]

This new code should give you what you want and it no longer calculates salary.

Public Function CalcSalary(BudgetID As Integer, MainID As Integer, EmpID As Integer) As String
 
Dim i, AY1, AY2, AY3, S1, S2, S3 As Byte
Dim CurrentFY, ChangeMonth, AYMonths, SummerMonths As Byte
Dim ChangeDate1, ChangeDate2, ChangeDate3, EndDate, StartDate As Date
 
AY1 = 0
AY2 = 0
AY3 = 0
S1 = 0
S2 = 0
S3 = 0
AYMonths = DLookup("[AY Months]", "TblEntry", "[Entry ID #] = " & MainID)
SummerMonths = DLookup("[Summer Months]", "TblEntry", "[Entry ID #] = " & MainID)
StartDate = DLookup("[Project Start Date]", "TblDate", "[Budget ID] = " & BudgetID)
EndDate = DateAdd("yyyy", 1, StartDate)
ChangeMonth = DLookup("[Change Month]", "TblEmployeeCategory", "[Employee ID Number] = " & EmpID)
If Month(Date) < ChangeMonth Then
    ChangeDate1 = CDate(ChangeMonth & "/1/" & (Year(Date) - 1))
Else
    ChangeDate1 = CDate(ChangeMonth & "/1/" & Year(Date))
End If
ChangeDate2 = DateAdd("yyyy", 1, ChangeDate1)
ChangeDate3 = DateAdd("yyyy", 1, ChangeDate2)
 
If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
    CurrentFY = 1
ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
    CurrentFY = 2
Else
    CurrentFY = 3
End If
 
Do Until AYMonths = 0
    Select Case Month(EndDate)
        Case 6, 7, 8
        Case Else
            AYMonths = AYMonths - 1
            Select Case CurrentFY
                Case 1
                    AY1 = AY1 + 1
                Case 2
                    AY2 = AY2 + 1
                Case 3
                    AY3 = AY3 + 1
            End Select
    End Select
    EndDate = DateAdd("m", -1, EndDate)
    If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
Loop
 
EndDate = DateAdd("yyyy", 1, StartDate)
If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
    CurrentFY = 1
ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
    CurrentFY = 2
Else
    CurrentFY = 3
End If
 
Do Until SummerMonths = 0
    Select Case Month(EndDate)
        Case 6, 7, 8
            SummerMonths = SummerMonths - 1
            Select Case CurrentFY
                Case 1
                    S1 = S1 + 1
                Case 2
                    S2 = S2 + 1
                Case 3
                    S3 = S3 + 1
            End Select
        Case Else
    End Select
    EndDate = DateAdd("m", -1, EndDate)
    If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
Loop
 
CalcSalary = AY1 & ", " & S1 & ", " & AY2 & ", " & S2 & ", " & AY3 & ", " & S3
 
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.

[muhes]

This new code should give you what you want and it no longer calculates salary.

Public Function CalcSalary(BudgetID As Integer, MainID As Integer, EmpID As Integer) As String
 
Dim i, AY1, AY2, AY3, S1, S2, S3 As Byte
Dim CurrentFY, ChangeMonth, AYMonths, SummerMonths As Byte
Dim ChangeDate1, ChangeDate2, ChangeDate3, EndDate, StartDate As Date
 
AY1 = 0
AY2 = 0
AY3 = 0
S1 = 0
S2 = 0
S3 = 0
AYMonths = DLookup("[AY Months]", "TblEntry", "[Entry ID #] = " & MainID)
SummerMonths = DLookup("[Summer Months]", "TblEntry", "[Entry ID #] = " & MainID)
StartDate = DLookup("[Project Start Date]", "TblDate", "[Budget ID] = " & BudgetID)
EndDate = DateAdd("yyyy", 1, StartDate)
ChangeMonth = DLookup("[Change Month]", "TblEmployeeCategory", "[Employee ID Number] = " & EmpID)
If Month(Date) < ChangeMonth Then
    ChangeDate1 = CDate(ChangeMonth & "/1/" & (Year(Date) - 1))
Else
    ChangeDate1 = CDate(ChangeMonth & "/1/" & Year(Date))
End If
ChangeDate2 = DateAdd("yyyy", 1, ChangeDate1)
ChangeDate3 = DateAdd("yyyy", 1, ChangeDate2)
 
If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
    CurrentFY = 1
ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
    CurrentFY = 2
Else
    CurrentFY = 3
End If
 
Do Until AYMonths = 0
    Select Case Month(EndDate)
        Case 6, 7, 8
        Case Else
            AYMonths = AYMonths - 1
            Select Case CurrentFY
                Case 1
                    AY1 = AY1 + 1
                Case 2
                    AY2 = AY2 + 1
                Case 3
                    AY3 = AY3 + 1
            End Select
    End Select
    EndDate = DateAdd("m", -1, EndDate)
    If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
Loop
 
EndDate = DateAdd("yyyy", 1, StartDate)
If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
    CurrentFY = 1
ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
    CurrentFY = 2
Else
    CurrentFY = 3
End If
 
Do Until SummerMonths = 0
    Select Case Month(EndDate)
        Case 6, 7, 8
            SummerMonths = SummerMonths - 1
            Select Case CurrentFY
                Case 1
                    S1 = S1 + 1
                Case 2
                    S2 = S2 + 1
                Case 3
                    S3 = S3 + 1
            End Select
        Case Else
    End Select
    EndDate = DateAdd("m", -1, EndDate)
    If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
Loop
 
CalcSalary = AY1 & ", " & S1 & ", " & AY2 & ", " & S2 & ", " & AY3 & ", " & S3
 
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.

[Rabbit]

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.

[muhes]

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

[Rabbit]

In the Function Declaration it used to be something like:

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:

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.

[muhes]

In the Function Declaration it used to be something like:

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:

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:

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.

[Rabbit]

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.

[muhes]

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:

If Month(Date) < ChangeMonth Then
    ChangeDate1 = CDate(ChangeMonth & "/1/" & (Year(Date) - 1))
Else
    ChangeDate1 = CDate(ChangeMonth & "/1/" & Year(Date))
End If
ChangeDate2 = DateAdd("yyyy", 1, ChangeDate1)
ChangeDate3 = DateAdd("yyyy", 1, ChangeDate2)
 
If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
    CurrentFY = 1
ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
    CurrentFY = 2
Else
    CurrentFY = 3
End If
 

is the part of the code that determines which AY year is used. Can you help me better understand the key parts?

Thanks.

[Rabbit]

You can get rid of the Budgeted Salary field altogether, no need for it any longer.

Public Function CalcSalary(BudgetID As Integer, MainID As Integer, EmpID As Integer) As String
 
Dim i, AY1, AY2, AY3, S1, S2, S3 As Byte
Dim CurrentFY, ChangeMonth, AYMonths, SummerMonths As Byte
Dim ChangeDate1, ChangeDate2, ChangeDate3, EndDate, StartDate As Date
 
AY1 = 0
AY2 = 0
AY3 = 0
S1 = 0
S2 = 0
S3 = 0
AYMonths = DLookup("[AY Months]", "TblEntry", "[Entry ID #] = " & MainID)
SummerMonths = DLookup("[Summer Months]", "TblEntry", "[Entry ID #] = " & MainID)
StartDate = DLookup("[Project Start Date]", "TblDate", "[Budget ID] = " & BudgetID)
EndDate = DateAdd("yyyy", 1, StartDate)
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.

If Month(Date) < ChangeMonth Then
    ChangeDate1 = CDate(ChangeMonth & "/1/" & (Year(Date) - 1))
Else
    ChangeDate1 = CDate(ChangeMonth & "/1/" & Year(Date))
End If
ChangeDate2 = DateAdd("yyyy", 1, ChangeDate1)
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.

If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
    CurrentFY = 1
ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
    CurrentFY = 2
Else
    CurrentFY = 3
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.

Do Until AYMonths = 0
    Select Case Month(EndDate)
        Case 6, 7, 8
        Case Else
            AYMonths = AYMonths - 1
            Select Case CurrentFY
                Case 1
                    AY1 = AY1 + 1
                Case 2
                    AY2 = AY2 + 1
                Case 3
                    AY3 = AY3 + 1
            End Select
    End Select
    EndDate = DateAdd("m", -1, EndDate)
    If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
Loop

This just resets everything and does the same thing except for summer months this time.

EndDate = DateAdd("yyyy", 1, StartDate)
If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
    CurrentFY = 1
ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
    CurrentFY = 2
Else
    CurrentFY = 3
End If
 
Do Until SummerMonths = 0
    Select Case Month(EndDate)
        Case 6, 7, 8
            SummerMonths = SummerMonths - 1
            Select Case CurrentFY
                Case 1
                    S1 = S1 + 1
                Case 2
                    S2 = S2 + 1
                Case 3
                    S3 = S3 + 1
            End Select
        Case Else
    End Select
    EndDate = DateAdd("m", -1, EndDate)
    If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
Loop
 
CalcSalary = AY1 & ", " & S1 & ", " & AY2 & ", " & S2 & ", " & AY3 & ", " & S3
 
End Function

[muhes]

You can get rid of the Budgeted Salary field altogether, no need for it any longer.

Public Function CalcSalary(BudgetID As Integer, MainID As Integer, EmpID As Integer) As String
 
Dim i, AY1, AY2, AY3, S1, S2, S3 As Byte
Dim CurrentFY, ChangeMonth, AYMonths, SummerMonths As Byte
Dim ChangeDate1, ChangeDate2, ChangeDate3, EndDate, StartDate As Date
 
AY1 = 0
AY2 = 0
AY3 = 0
S1 = 0
S2 = 0
S3 = 0
AYMonths = DLookup("[AY Months]", "TblEntry", "[Entry ID #] = " & MainID)
SummerMonths = DLookup("[Summer Months]", "TblEntry", "[Entry ID #] = " & MainID)
StartDate = DLookup("[Project Start Date]", "TblDate", "[Budget ID] = " & BudgetID)
EndDate = DateAdd("yyyy", 1, StartDate)
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.

If Month(Date) < ChangeMonth Then
    ChangeDate1 = CDate(ChangeMonth & "/1/" & (Year(Date) - 1))
Else
    ChangeDate1 = CDate(ChangeMonth & "/1/" & Year(Date))
End If
ChangeDate2 = DateAdd("yyyy", 1, ChangeDate1)
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.

If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
    CurrentFY = 1
ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
    CurrentFY = 2
Else
    CurrentFY = 3
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.

Do Until AYMonths = 0
    Select Case Month(EndDate)
        Case 6, 7, 8
        Case Else
            AYMonths = AYMonths - 1
            Select Case CurrentFY
                Case 1
                    AY1 = AY1 + 1
                Case 2
                    AY2 = AY2 + 1
                Case 3
                    AY3 = AY3 + 1
            End Select
    End Select
    EndDate = DateAdd("m", -1, EndDate)
    If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
Loop

This just resets everything and does the same thing except for summer months this time.

EndDate = DateAdd("yyyy", 1, StartDate)
If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
    CurrentFY = 1
ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
    CurrentFY = 2
Else
    CurrentFY = 3
End If
 
Do Until SummerMonths = 0
    Select Case Month(EndDate)
        Case 6, 7, 8
            SummerMonths = SummerMonths - 1
            Select Case CurrentFY
                Case 1
                    S1 = S1 + 1
                Case 2
                    S2 = S2 + 1
                Case 3
                    S3 = S3 + 1
            End Select
        Case Else
    End Select
    EndDate = DateAdd("m", -1, EndDate)
    If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
Loop
 
CalcSalary = AY1 & ", " & S1 & ", " & AY2 & ", " & S2 & ", " & AY3 & ", " & S3
 
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.

[Rabbit]

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.

[muhes]

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:

EndDate = DateAdd("yyyy", 1, StartDate - (Month(Date) - 1))
 

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

[Rabbit]

Hello Rabbit

I tried to subtract 1 month from End Date, but I think I did something wrong because it had no apparent effect:

EndDate = DateAdd("yyyy", 1, StartDate - (Month(Date) - 1))
 

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:

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.

[muhes]

To subtract one month, you use:

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

[Rabbit]

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.

[muhes]

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.

[Rabbit]

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.

[muhes]

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.

[Rabbit]

Mid is function that can be used anywhere.

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.

[muhes]

Mid is function that can be used anywhere.

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:

[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 
 

?

Thanks.

[Rabbit]

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.

[muhes]

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:

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)
 

as compared to:

[Salary]/9*Val(Mid(Spread,1,1)) + [Salary]/9*Val(Mid(Spread,3,1)) + [Salary]/9*Val(Mid(Spread,5,1))*1.034 etc...
 

Thanks,

James

[NeoPa]

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 :

5 * "3.2"

Essentially, because the "3.2" side is a string, it is useless in this formula.
It should be :

5 * Val("3.2")

This will always produce the correct result (16).

[MMcCarthy]

This thread is already way too long for anyone else to wade through.

Please post a new thread for this new question.

ADMIN

[NeoPa]

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.

[MMcCarthy]

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

[Rabbit]

I don't mind starting a new thread.

Continued here.

[NeoPa]

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!).

[MMcCarthy]

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.