Calculated Field Returning Incorrect Results

If all you changed was 9 to 12, there is no reason for it not to work.
The only thing the code does is to return the spread of the months in fiscal years split up into academic months and summer months. It has no bearing on anything else. Try making multiple fields that only return the values without doing the calculation to see if all the numbers being returned are correct.

If not, post a detailed explanation and example.

Continued from here.

I'd better register my interest then :)

If all you changed was 9 to 12, there is no reason for it not to work.
The only thing the code does is to return the spread of the months in fiscal years split up into academic months and summer months. It has no bearing on anything else. Try making multiple fields that only return the values without doing the calculation to see if all the numbers being returned are correct.

If not, post a detailed explanation and example.

Hello Rabbit,

Thanks for your advice. It took me a little while, but I was able to eventually isolate the problem. It turned out that I was using the wrong spread; it should have been 1,4,7,10 etc... It just happened that my S2 and AY2 were the same number, so it looked right, even though it was pulling from the wrong place.

I am trying to turn my focus back to the forms. We had discussed this previously, but I'm still a little confused about how I need to structure one part of the form.

The relevant tables being used are:

Use this table structure:
My main form has the fields: TblDate [Project Start Date], [# of years]. My subform has the fields: TblEmployees [Name] TblEntry [Salary], [Name], [AY Months], [AY Percent], [Summer Months], [Summer Percent].

The problem. When I select a new record on the main form, the subform still retains the fields of the previous subform.

Other minor problems: I can't select a number less then 100% for the fields [AY Percent] and [Summer Percent]. Their fields have the properties numeric and percentage.

Thanks as always for the help.

Hello Rabbit,

Thanks for your advice. It took me a little while, but I was able to eventually isolate the problem. It turned out that I was using the wrong spread; it should have been 1,4,7,10 etc... It just happened that my S2 and AY2 were the same number, so it looked right, even though it was pulling from the wrong place.

I am trying to turn my focus back to the forms. We had discussed this previously, but I'm still a little confused about how I need to structure one part of the form.

The relevant tables being used are:

Use this table structure:

Expand|Select|Wrap|Line Numbers

1. Table Name=TblDate
2. Project Start Date; Date
3. # of years; Numeric
4. Budget ID; Autonumber; PK
5.  
6. Table Name=TblEntry
7. Entry ID #; Autonumber; PK
8. Name ID #; Numeric; FK 
9. Budget ID #; Numeric; FK
10. Salary; Numeric
11. Employee ID #; Numeric; FK
12. Name; String
13. AY Months; Numeric
14. Summer Months; Numeric
15. AY Percent; Numeric
16. Summer Percent; Numeric
17.  
18. Table Name=TblEmployeeCategory
19. Employee ID Number; Autonumber; PK
20. Employee Type; String
21. Change Month; Numeric
22.  
23. Table Name=TblEmployees
24. Name ID #; Autonumber; PK
25. Name; String
26. Employee ID Number; Numeric; FK
27.  

My main form has the fields: TblDate [Project Start Date], [# of years]. My subform has the fields: TblEmployees [Name] TblEntry [Salary], [Name], [AY Months], [AY Percent], [Summer Months], [Summer Percent].

The problem. When I select a new record on the main form, the subform still retains the fields of the previous subform.

Other minor problems: I can't select a number less then 100% for the fields [AY Percent] and [Summer Percent]. Their fields have the properties numeric and percentage.

Thanks as always for the help.

Hi Rabbit,

I figured out what I was doing wrong. I had attached the subform to the wrong field of the main form; it needed to be with [Budget ID]. I also realized why I wasn't able to display the % fields; I chose the wrong field size. I should have chosen double.

Thanks.

Always good when you figure things out yourself. Let us know if you run into any more trouble.

Always good when you figure things out yourself. Let us know if you run into any more trouble.

Hi Rabbit,

Yeah, I always feel better when I can figure something out on my own as well.

I've been trying to figure this one out as well, but so far I haven't come up with it. Is there an easy modification that I can make to the VBA code so that it will always calculate the full spread regardless of the number of AY and summer months. This would run as a separate module with a different name.

This wouldn't replace the original code, but would rather be used for a specific agency, so that on the report it would pull from the querries built on this code.

Thanks.

I'm not sure I follow, what do you mean by full spread? You mean beyond one year?

I'm not sure I follow, what do you mean by full spread? You mean beyond one year?

Hi Rabbit,



I'm sorry I didn't explain that very well. What I meant by "full spread", was that it would return results as though there were always full months for both AY and Summer. For example, If you had a start date of 3/1/2007, and 9 AY months, and 3 summer months, the spread would be 3,1,6,2,0,0. But if there were say 2 AY months and 2 summer months, the new spread would be 0,0,2,2,0,0 (which would be the numbers needed for 98% of cases; however, there are a very few instances that would require a calculation based on the 3,1,6,2,0,0 spread regardless of the number of actual months. It's annoying, but that's how they do things. So basically, I want to copy the code, open a second module, paste it, and change the name, and hopefully just pull out the one piece or two pieces that evaluate whether there are less than the full months, and subtract down until there are none. Then I could run a query based on this differently named function and (possibly) get a different answer for the spread.

Thanks.

Hi Rabbit,



I'm sorry I didn't explain that very well. What I meant by "full spread", was that it would return results as though there were always full months for both AY and Summer. For example, If you had a start date of 3/1/2007, and 9 AY months, and 3 summer months, the spread would be 3,1,6,2,0,0. But if there were say 2 AY months and 2 summer months, the new spread would be 0,0,2,2,0,0 (which would be the numbers needed for 98% of cases; however, there are a very few instances that would require a calculation based on the 3,1,6,2,0,0 spread regardless of the number of actual months. It's annoying, but that's how they do things. So basically, I want to copy the code, open a second module, paste it, and change the name, and hopefully just pull out the one piece or two pieces that evaluate whether there are less than the full months, and subtract down until there are none. Then I could run a query based on this differently named function and (possibly) get a different answer for the spread.

Thanks.

Well, you could copy the code, change the name of the function, and manually set AY and Summer months to 9 and 3 respectively instead of having the code DLookup the values from the table.

Well, you could copy the code, change the name of the function, and manually set AY and Summer months to 9 and 3 respectively instead of having the code DLookup the values from the table.

Thanks Rabbit,

That seemed to work perfectly.

Thanks Rabbit,

That seemed to work perfectly.

Not a problem, good luck.

Split to: Conditional Form Opening & Report Filtering