By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,985 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,985 IT Pros & Developers. It's quick & easy.

Calculated Field Returning Incorrect Results

P: 77
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).
Thanks NeoPa for your help and explanation. I learn a little more each time.

I have a new problem that I don't understand. I used what was provided to design a query, and I tested it out with type 1 employees, and the numbers matched my expectations:

Expand|Select|Wrap|Line Numbers
  1. Salary1: [Salary]/9*Val(Mid([Spread],1,1))*[AY Percent]+[Salary]/9*Val(Mid([Spread],3,1))*[Summer Percent]+[Salary]/9*Val(Mid([Spread],5,1))*1.034*[AY Percent]+[Salary]/9*Val(Mid([Spread],7,1))*1.034*[Summer Percent]+[Salary]/9*Val(Mid([Spread],9,1))*1.034*1.034*[AY Percent]+[Salary]/9*Val(Mid([Spread],11,1))*1.034*1.034*[Summer Percent]
  2.  
I took this same code, and the only thing I changed was dividing the [Salary] field by 12 instead of 9, but for some reason I'm getting the wrong answers. I looked over the calculations, and I figured out that it is only doing AY calculations, so the 1,5,9 section of the spread. I honestly don't understand why this is happening, but I guess that it has something to do with the VBA code, perhaps the way the variables are structured? Can anyone help me with this?

Thanks.
Mar 12 '07 #1
Share this Question
Share on Google+
13 Replies


Rabbit
Expert Mod 10K+
P: 12,349
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.
Mar 12 '07 #2

Rabbit
Expert Mod 10K+
P: 12,349
Continued from here.
Mar 12 '07 #3

NeoPa
Expert Mod 15k+
P: 31,347
I'd better register my interest then :)
Mar 12 '07 #4

P: 77
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:
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.
Mar 13 '07 #5

P: 77
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.
Mar 14 '07 #6

Rabbit
Expert Mod 10K+
P: 12,349
Always good when you figure things out yourself. Let us know if you run into any more trouble.
Mar 14 '07 #7

P: 77
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.
Mar 15 '07 #8

Rabbit
Expert Mod 10K+
P: 12,349
I'm not sure I follow, what do you mean by full spread? You mean beyond one year?
Mar 15 '07 #9

P: 77
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.
Mar 16 '07 #10

Rabbit
Expert Mod 10K+
P: 12,349
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.
Mar 16 '07 #11

P: 77
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.
Mar 16 '07 #12

Rabbit
Expert Mod 10K+
P: 12,349
Thanks Rabbit,

That seemed to work perfectly.
Not a problem, good luck.
Mar 16 '07 #13

Rabbit
Expert Mod 10K+
P: 12,349

Post your reply

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