Calculated Fields not Working

[Joanne]

I'm so confused, I just feel like giving this whole thing up. But I'll try
to explain. I have a subform in a form which is for totaling vacation time.
In the subform each line represents one request for vacation time. The
fields are as follows:
Begin Date End Date Type of Day Total Days

The "type of day" can be "personal", "vacation", "jury duty" or "carryover".
The total days field has the following in it:
=IIf([Type Of Day]="Personal" Or [Type Of Day]="Jury Duty" Or [Type of
Day]="Carryover","N/A",DateDiff("d",[OLP Begin Date],[OLP End Date]))

This works fine. But then I've put a calculated field into my query called
"Total Vacation" with the same code. Then in my footer on the subform I put
=Sum([TotalVacation]) and I get #error when I view the form. I'm so confused
because 1. I don't understand why I'm getting the error and 2. I don't
understand why I have to bind the field in the footer to a field in the query
when it could just calculate right from the form, couldn't it? Couldn't it
just add up all of the "total days" entries right in the form? This seems
like cheating since it's not really doing a direct calculation. What if I
changed the code in the form and forgot to change it in the query? Thanks
for getting me unconfused about this.

 

[Douglas J. Steele]

That's because you're putting a string (N/A) in some cases, and Sum won't
work on strings.

Try:

Sum(IIf([Type Of Day]="Personal" Or [Type Of Day]="Jury Duty" Or [Type of
Day]="Carryover",0,DateDiff("d",[OLP Begin Date],[OLP End Date])))

 

[Stewart Berman]

Your "Total Vacation" field contains strings ("N/A").

 

[Joanne]

Thank you for your responses. They were very helpful. But what about the
idea of calculating directly from the calculated field on the form rather
than from a field in the query? Can't this be done? Because what if I
changed the calculated field in the query and forgot to change it in the
form? It seems like cheating in a way.

Your "Total Vacation" field contains strings ("N/A").

I'm so confused, I just feel like giving this whole thing up. But I'll try
to explain. I have a subform in a form which is for totaling vacation time.
In the subform each line represents one request for vacation time. The
fields are as follows:
Begin Date End Date Type of Day Total Days

The "type of day" can be "personal", "vacation", "jury duty" or "carryover".
The total days field has the following in it:
=IIf([Type Of Day]="Personal" Or [Type Of Day]="Jury Duty" Or [Type of
Day]="Carryover","N/A",DateDiff("d",[OLP Begin Date],[OLP End Date]))

This works fine. But then I've put a calculated field into my query called
"Total Vacation" with the same code. Then in my footer on the subform I put
=Sum([TotalVacation]) and I get #error when I view the form. I'm so confused
because 1. I don't understand why I'm getting the error and 2. I don't
understand why I have to bind the field in the footer to a field in the query
when it could just calculate right from the form, couldn't it? Couldn't it
just add up all of the "total days" entries right in the form? This seems
like cheating since it's not really doing a direct calculation. What if I
changed the code in the form and forgot to change it in the query? Thanks
for getting me unconfused about this.

 

[Douglas J. Steele]

As far as I'm aware, you cannot simply refer to a calculated field in
another calculation: you need to repeat the calculation, whether in a form
or a query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you for your responses. They were very helpful. But what about the
idea of calculating directly from the calculated field on the form rather
than from a field in the query? Can't this be done? Because what if I
changed the calculated field in the query and forgot to change it in the
form? It seems like cheating in a way.

Your "Total Vacation" field contains strings ("N/A").

I'm so confused, I just feel like giving this whole thing up. But I'll
try
to explain. I have a subform in a form which is for totaling vacation
time.
In the subform each line represents one request for vacation time. The
fields are as follows:
Begin Date End Date Type of Day Total Days

The "type of day" can be "personal", "vacation", "jury duty" or
"carryover".
The total days field has the following in it:
=IIf([Type Of Day]="Personal" Or [Type Of Day]="Jury Duty" Or [Type of
Day]="Carryover","N/A",DateDiff("d",[OLP Begin Date],[OLP End Date]))

This works fine. But then I've put a calculated field into my query
called
"Total Vacation" with the same code. Then in my footer on the subform I
put
=Sum([TotalVacation]) and I get #error when I view the form. I'm so
confused
because 1. I don't understand why I'm getting the error and 2. I don't
understand why I have to bind the field in the footer to a field in the
query
when it could just calculate right from the form, couldn't it? Couldn't
it
just add up all of the "total days" entries right in the form? This
seems
like cheating since it's not really doing a direct calculation. What if
I
changed the code in the form and forgot to change it in the query?
Thanks
for getting me unconfused about this.

 

[vanderghast]

With Jet you can refer to a computed expression into another one:


SELECT field1 + 1 AS exp1,
exp1 + 1 AS exp2
FROM somewhere

Here, exp1 is re-used in exp2.
But you cannot use the alias in the WHERE clause:


...
WHERE exp1 > 10


would ask for a parameter, exp1, to be solved. That is somehow logical since
the SELECT clause is technically executed AFTER the WHERE clause did get
evaluated.

Unfortunately too, you cannot use alias in the ORDER BY clause, EXCEPT for a
UNION query.


With MS SQL Server, you cannot use an alias in a computed expression since
alias are seen as column name given to fields in the result, BUT you can use
them in the ORDER BY clause. You can use a virtual table, though:


SELECT exp1, exp1+1 AS exp2
FROM ( SELECT field1+1 AS exp1
FROM somewhere)



Vanderghast, Access MVP