Calculated Field Returning Incorrect Results

[muhes]

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

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:

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]
 

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.

[Rabbit]

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.

[Rabbit]

Continued from here.

[NeoPa]

I'd better register my interest then :)

[muhes]

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:

Table Name=TblDate
Project Start Date; Date
# of years; Numeric
Budget ID; Autonumber; PK
 
 Table Name=TblEntry
Entry ID #; Autonumber; PK
Name ID #; Numeric; FK 
Budget ID #; Numeric; FK
Salary; Numeric
Employee ID #; Numeric; FK
Name; String
AY Months; Numeric
Summer Months; Numeric
AY Percent; Numeric
Summer Percent; Numeric
 
 Table Name=TblEmployeeCategory
Employee ID Number; Autonumber; PK
Employee Type; String
Change Month; Numeric
 
 Table Name=TblEmployees
Name ID #; Autonumber; PK
Name; String
Employee ID Number; Numeric; FK
 

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.

[muhes]

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:

Table Name=TblDate
Project Start Date; Date
# of years; Numeric
Budget ID; Autonumber; PK
 
 Table Name=TblEntry
Entry ID #; Autonumber; PK
Name ID #; Numeric; FK 
Budget ID #; Numeric; FK
Salary; Numeric
Employee ID #; Numeric; FK
Name; String
AY Months; Numeric
Summer Months; Numeric
AY Percent; Numeric
Summer Percent; Numeric
 
 Table Name=TblEmployeeCategory
Employee ID Number; Autonumber; PK
Employee Type; String
Change Month; Numeric
 
 Table Name=TblEmployees
Name ID #; Autonumber; PK
Name; String
Employee ID Number; Numeric; FK
 

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.

[Rabbit]

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

[muhes]

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.

[Rabbit]

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

[muhes]

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.

[Rabbit]

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.

[muhes]

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.

[Rabbit]

Thanks Rabbit,

That seemed to work perfectly.

Not a problem, good luck.

[Rabbit]

Split to: Conditional Form Opening & Report Filtering