Field calculation resulting as 0 (second request for help)

[margot_eon]

Hello, I have submitted this question a week or so ago and the suggestions
received were not helpful to me. Can someone look into this? Sorry for the
lenght of the message, I put the message thread from beginning...
--------------------------
Initial request:
Trying to calculated the number of vacation-days left for each employee.
The results comes up as 0 in every employee records. Why?
Fields are:
#MonthsWorked : =DateDiff("m",[Datehired],Date())
#DaysAccumulated: =[#MonthsWorked]*2.5
#DaysTaken: manual entry and thefield where I get the wrong result:
#DaysLeft: =Sum([#DayAccumulated]-[#DaysTalen]) - this calculation is
returning 0 in every records.

Suggestion for Microsoft
Sum is not required to carry out the calculation;
Me.#DaysLeft: =Me.#DayAccumulated - Me.#DaysTaken. The Me. part is
assuming they are field names rather than dimmed variables, if they are
dimmed variables obviously remove the Me. part!

My Response:
I tried the suggested formula but it returns #Name?
FYI, I use the Expression builder to enter the formula as I'm not a
programmer. Any other suggestions?
I wonder; since the field #DaysAccumulated is a calculated field, would that
be cause for the error?

Suggestion for Microsoft:
How did you try it, with the Me. part or without, I think (never use
Expression builder myself) you should have; #DaysLeft:
=[#DayAccumulated]-[#DaysTalen]
If you still get 0 then remove the DaysTaken half of the calculation to
check that DaysAccumulted is retuirning a valid number.

My Response:
I tried:
Me.#DaysLeft = Me.#DayAccumulated - Me.#DaysTaken - returns #Name? In the
Expression Builder, by default, it adds a = sign in front of the whole
formula, making it look like this: =Me.#DaysLeft: =Me.#DayAccumulated -
Me.#DaysTaken therefore making the formula wrong.
And:
=me.#DayAccumulated-me.#DaysTaken - returns #Name?
And:
=[#DayAccumulated]-[#DaysTaken] - returns #Name? as well.
And:
=Sum([employee info]!#DayAccumulated-[employee info]!#DaysTaken) - return 0
And:
=Sum([#DayAccumulated]-[#DaysTaken]) - returns and empty field
The formula in #DayAccumulated is returning a valid number.

Suggestion for Microsoft:
ummmm, can you not put; Me.[#DaysLeft:] = Iif(Not
IsNull(Me.[#DayAccumulated]),Me.[#DayAccumulated],0) - Iif(Not
IsNull(Me.[#DaysTaken]),Me.[#DaysTaken],0)
In the afterUpdate event of each of the 2 textboxes concerned in vba? and
scrap the expression builder part entirely?

My Response:
Thanks but this does not help me as I need to find a solution for this and I
will not start to learn programming in VBA today!!
Can you pass my request to someone who is familiar with Expression builder?


THank you!!

 

[Douglas J. Steele]

If it's possible that the value is Null, I'm afraid you need to tell Access
what to do with Null values.

The suggestion you posted (using IIf) is one approach, but a simpler
approach is to use the Nz function.

Me.#DaysLeft: = Nz(Me.#DayAccumulated, 0) - Nz(Me.#DaysTaken, 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello, I have submitted this question a week or so ago and the
suggestions
received were not helpful to me. Can someone look into this? Sorry for
the
lenght of the message, I put the message thread from beginning...
--------------------------
Initial request:
Trying to calculated the number of vacation-days left for each employee.
The results comes up as 0 in every employee records. Why?
Fields are:
#MonthsWorked : =DateDiff("m",[Datehired],Date())
#DaysAccumulated: =[#MonthsWorked]*2.5
#DaysTaken: manual entry and thefield where I get the wrong result:
#DaysLeft: =Sum([#DayAccumulated]-[#DaysTalen]) - this calculation is
returning 0 in every records.

Suggestion for Microsoft
Sum is not required to carry out the calculation;
Me.#DaysLeft: =Me.#DayAccumulated - Me.#DaysTaken. The Me. part is
assuming they are field names rather than dimmed variables, if they are
dimmed variables obviously remove the Me. part!

My Response:
I tried the suggested formula but it returns #Name?
FYI, I use the Expression builder to enter the formula as I'm not a
programmer. Any other suggestions?
I wonder; since the field #DaysAccumulated is a calculated field, would
that
be cause for the error?

Suggestion for Microsoft:
How did you try it, with the Me. part or without, I think (never use
Expression builder myself) you should have; #DaysLeft:
=[#DayAccumulated]-[#DaysTalen]
If you still get 0 then remove the DaysTaken half of the calculation to
check that DaysAccumulted is retuirning a valid number.

My Response:
I tried:
Me.#DaysLeft = Me.#DayAccumulated - Me.#DaysTaken - returns #Name? In the
Expression Builder, by default, it adds a = sign in front of the whole
formula, making it look like this: =Me.#DaysLeft: =Me.#DayAccumulated -
Me.#DaysTaken therefore making the formula wrong.
And:
=me.#DayAccumulated-me.#DaysTaken - returns #Name?
And:
=[#DayAccumulated]-[#DaysTaken] - returns #Name? as well.
And:
=Sum([employee info]!#DayAccumulated-[employee info]!#DaysTaken) - return
0
And:
=Sum([#DayAccumulated]-[#DaysTaken]) - returns and empty field
The formula in #DayAccumulated is returning a valid number.

Suggestion for Microsoft:
ummmm, can you not put; Me.[#DaysLeft:] = Iif(Not
IsNull(Me.[#DayAccumulated]),Me.[#DayAccumulated],0) - Iif(Not
IsNull(Me.[#DaysTaken]),Me.[#DaysTaken],0)
In the afterUpdate event of each of the 2 textboxes concerned in vba?
and
scrap the expression builder part entirely?

My Response:
Thanks but this does not help me as I need to find a solution for this and
I
will not start to learn programming in VBA today!!
Can you pass my request to someone who is familiar with Expression
builder?


THank you!!