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.

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



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Joanne" <(E-Mail Removed)> wrote in message
news:5D768884-86C4-4778-A7C2-(E-Mail Removed)...
> 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.
>
>

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

Joanne <(E-Mail Removed)> wrote:

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

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.

"Stewart Berman" wrote:

> Your "Total Vacation" field contains strings ("N/A").
>
> Joanne <(E-Mail Removed)> wrote:
>
> >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.
> >
>
>

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
http://I.Am/DougSteele
(no e-mails, please!)


"Joanne" <(E-Mail Removed)> wrote in message
news:9D95D858-9ECB-43B2-9637-(E-Mail Removed)...
> 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.
>
> "Stewart Berman" wrote:
>
>> Your "Total Vacation" field contains strings ("N/A").
>>
>> Joanne <(E-Mail Removed)> wrote:
>>
>> >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.
>> >
>>
>>

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




"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message
news:(E-Mail Removed)...
> 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.