Calculation In Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is my first time working with access and I have been trying to complete
the following calculation using the query builder for the past couple of days
without success.

I am trying to calculate RegHrs based on the following conditions below:

If Statutory = “N†and ActualDailyHours > 0 add ActualDailyHours to
RegHrs
Or Statutory = “N†and DailyHours > 0 add Daily Hours to RegHrs

This is my latest version but I still get invalid syntax ???

Reghrs:IFF [DriverSchedule_YTD]![Statutory] ="N" And
[DriverSchedule_YTD]![DailyHours] > 0,
Sum ([DriverSchedule_YTD]![lDailyHours] Or
IFF [DriverSchedule_YTD]![Statutory] ="N" And
[DriverSchedule_YTD]![ActualDailyHours] > 0,
Sum ([DriverSchedule_YTD]![ActualDailyHours]

Does someone know the correct syntax ?
Any help is appreciated
 
This is my first time working with access and I have been trying to complete
the following calculation using the query builder for the past couple of days
without success.

I am trying to calculate RegHrs based on the following conditions below:

If Statutory = “N” and ActualDailyHours > 0 add ActualDailyHours to
RegHrs
Or Statutory = “N” and DailyHours > 0 add Daily Hours to RegHrs

This is my latest version but I still get invalid syntax ???

Reghrs:IFF [DriverSchedule_YTD]![Statutory] ="N" And
[DriverSchedule_YTD]![DailyHours] > 0,
Sum ([DriverSchedule_YTD]![lDailyHours] Or
IFF [DriverSchedule_YTD]![Statutory] ="N" And
[DriverSchedule_YTD]![ActualDailyHours] > 0,
Sum ([DriverSchedule_YTD]![ActualDailyHours]

Does someone know the correct syntax ?
Any help is appreciated
Click to expand...

For one thing, the function is named IIF, not IFF. IIF is a
*function*. The syntax is

IFF(<logical expression>, <result if true>, <result if false>)

The parentheses are required and you left them out.

Sum is also a function which requires open and close parentheses. You
have only the open parens.

Finally, you can't use OR in the way you're using it. There can be an
OR in the logical expression, but for what you want you need nested
IIF.

YOur requirements are confusing. You're calculating the value of
RegHrs but you want to add various things to RegHrs. That's circular!
What's in RegHrs to begin with? What do you want RegHrs to contain if
Statutory is not equal to N, or if neither hours value is greater than
0? What is the context of this expression?

John W. Vinson[MVP]
 
John Vinson said:
This is my first time working with access and I have been trying to complete
the following calculation using the query builder for the past couple of days
without success.

I am trying to calculate RegHrs based on the following conditions below:

If Statutory = “N†and ActualDailyHours > 0 add ActualDailyHours to
RegHrs
Or Statutory = “N†and DailyHours > 0 add Daily Hours to RegHrs

This is my latest version but I still get invalid syntax ???

Reghrs:IFF [DriverSchedule_YTD]![Statutory] ="N" And
[DriverSchedule_YTD]![DailyHours] > 0,
Sum ([DriverSchedule_YTD]![lDailyHours] Or
IFF [DriverSchedule_YTD]![Statutory] ="N" And
[DriverSchedule_YTD]![ActualDailyHours] > 0,
Sum ([DriverSchedule_YTD]![ActualDailyHours]

Does someone know the correct syntax ?
Any help is appreciated
Click to expand...

For one thing, the function is named IIF, not IFF. IIF is a
*function*. The syntax is

IFF(<logical expression>, <result if true>, <result if false>)

The parentheses are required and you left them out.

Sum is also a function which requires open and close parentheses. You
have only the open parens.

Finally, you can't use OR in the way you're using it. There can be an
OR in the logical expression, but for what you want you need nested
IIF.

YOur requirements are confusing. You're calculating the value of
RegHrs but you want to add various things to RegHrs. That's circular!
What's in RegHrs to begin with? What do you want RegHrs to contain if
Statutory is not equal to N, or if neither hours value is greater than
0? What is the context of this expression?

John W. Vinson[MVP]
Click to expand...
 
John Vinson said:
This is my first time working with access and I have been trying to complete
the following calculation using the query builder for the past couple of days
without success.

I am trying to calculate RegHrs based on the following conditions below:

If Statutory = “N†and ActualDailyHours > 0 add ActualDailyHours to
RegHrs
Or Statutory = “N†and DailyHours > 0 add Daily Hours to RegHrs

This is my latest version but I still get invalid syntax ???

Reghrs:IFF [DriverSchedule_YTD]![Statutory] ="N" And
[DriverSchedule_YTD]![DailyHours] > 0,
Sum ([DriverSchedule_YTD]![lDailyHours] Or
IFF [DriverSchedule_YTD]![Statutory] ="N" And
[DriverSchedule_YTD]![ActualDailyHours] > 0,
Sum ([DriverSchedule_YTD]![ActualDailyHours]

Does someone know the correct syntax ?
Any help is appreciated
Click to expand...

For one thing, the function is named IIF, not IFF. IIF is a
*function*. The syntax is

IFF(<logical expression>, <result if true>, <result if false>)

The parentheses are required and you left them out.

Sum is also a function which requires open and close parentheses. You
have only the open parens.

Finally, you can't use OR in the way you're using it. There can be an
OR in the logical expression, but for what you want you need nested
IIF.

YOur requirements are confusing. You're calculating the value of
RegHrs but you want to add various things to RegHrs. That's circular!
What's in RegHrs to begin with? What do you want RegHrs to contain if
Statutory is not equal to N, or if neither hours value is greater than
0? What is the context of this expression?

John W. Vinson[MVP]
Hi John,
Click to expand...

Thanks for your quick response. The IFF was a typo I have tried so many
variations of syntax that I didn’t notice. I actually have some simple
calculations using IIF working on my reports but I am having trouble doing
this specific calculation.

Some further details as you requested

1. RegHrs: is introduced in my query to do the calculation and is currently
0.
2.Either Daily Hours (means scheduled hours but I did not rename in the
table) or Actual Daily Hours will contain a value.
3. If Statutory is not “N†then the idea is to leave RegHrs @ 0 and print
0 on the
The report
4.That is context for the strange logic: If Statutory = “N’ and
ActualDailyHours > 0 Add ActualDailyHours To Reghrs
Or If Stautory = “N’ and DailyHours > 0 Add DailyHours To Reghrs
5. If Statutory is “Y†I will do another separate calculation and populate
a field called Othrs which also is currently 0.
 
Hi John,

Thanks for your quick response. The IFF was a typo I have tried so many
variations of syntax that I didn’t notice. I actually have some simple
calculations using IIF working on my reports but I am having trouble doing
this specific calculation.

Some further details as you requested

1. RegHrs: is introduced in my query to do the calculation and is currently
0.
2. Either Daily Hours (means scheduled hours but I did not rename in the
table) or Actual Daily Hours will contain a value.
3. If Statutory is not “N†then the idea is to leave RegHrs @ 0 and print
0 on the
The report
4.That is context for the strange logic: If Statutory = “N’ and
ActualDailyHours > 0 Add ActualDailyHours To Reghrs
Or If Stautory = “N’ and DailyHours > 0 Add DailyHours To Reghrs

5. If Statutory is “Y†I will do another separate calculation and populate
a field called Othrs which also is currently 0.

I am just trying to get this calcution working first.

Thanks










dskanes said:
John Vinson said:
This is my first time working with access and I have been trying to complete
the following calculation using the query builder for the past couple of days
without success.

I am trying to calculate RegHrs based on the following conditions below:

If Statutory = “N†and ActualDailyHours > 0 add ActualDailyHours to
RegHrs
Or Statutory = “N†and DailyHours > 0 add Daily Hours to RegHrs

This is my latest version but I still get invalid syntax ???

Reghrs:IFF [DriverSchedule_YTD]![Statutory] ="N" And
[DriverSchedule_YTD]![DailyHours] > 0,
Sum ([DriverSchedule_YTD]![lDailyHours] Or
IFF [DriverSchedule_YTD]![Statutory] ="N" And
[DriverSchedule_YTD]![ActualDailyHours] > 0,
Sum ([DriverSchedule_YTD]![ActualDailyHours]

Does someone know the correct syntax ?
Any help is appreciated
Click to expand...

For one thing, the function is named IIF, not IFF. IIF is a
*function*. The syntax is

IFF(<logical expression>, <result if true>, <result if false>)

The parentheses are required and you left them out.

Sum is also a function which requires open and close parentheses. You
have only the open parens.

Finally, you can't use OR in the way you're using it. There can be an
OR in the logical expression, but for what you want you need nested
IIF.

YOur requirements are confusing. You're calculating the value of
RegHrs but you want to add various things to RegHrs. That's circular!
What's in RegHrs to begin with? What do you want RegHrs to contain if
Statutory is not equal to N, or if neither hours value is greater than
0? What is the context of this expression?

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Hi John,

Thanks for your quick response. The IFF was a typo I have tried so many
variations of syntax that I didn’t notice. I actually have some simple
calculations using IIF working on my reports but I am having trouble doing
this specific calculation.

Some further details as you requested

1. RegHrs: is introduced in my query to do the calculation and is currently
0.
Click to expand...

You can't "introduce" a field and then redefine it as something else.
Just use an expression which gives the desired result, 0 or whatever
else it should be. A SQL query is not a procedure; it does things "all
at once", so you can't set RegHrs to one value and then later to some
other value.
2. Either Daily Hours (means scheduled hours but I did not rename in the
table) or Actual Daily Hours will contain a value.
3. If Statutory is not “N” then the idea is to leave RegHrs @ 0 and print
0 on the
The report
4.That is context for the strange logic: If Statutory = “N’ and
ActualDailyHours > 0 Add ActualDailyHours To Reghrs
Or If Stautory = “N’ and DailyHours > 0 Add DailyHours To Reghrs
Click to expand...

Ok. Try this. Since you have more than two branches, it's better to
use the more flexible Switch() function. It takes arguments in pairs;
the first member of each pair is evaluated as being True or False; if
it's True the function returns the second member of the pair, if not
it goes on to the next member of the pair. So:

RegHrs: Switch([Statutory] = "Y", 0,
[ActualDailyHours] > 0, [ActualDailyHours],
[DailyHours] > 0, [DailyHours],
True, 0)

The last line returns zero if none of the other conditions apply.

You would then Sum the value of RegHrs in your Totals query (rather
than putting sums into the definition of RegHrs).

John W. Vinson[MVP]
 
thanks it worked fine. Have a great day

John Vinson said:
Hi John,

Thanks for your quick response. The IFF was a typo I have tried so many
variations of syntax that I didn’t notice. I actually have some simple
calculations using IIF working on my reports but I am having trouble doing
this specific calculation.

Some further details as you requested

1. RegHrs: is introduced in my query to do the calculation and is currently
0.
Click to expand...

You can't "introduce" a field and then redefine it as something else.
Just use an expression which gives the desired result, 0 or whatever
else it should be. A SQL query is not a procedure; it does things "all
at once", so you can't set RegHrs to one value and then later to some
other value.
2. Either Daily Hours (means scheduled hours but I did not rename in the
table) or Actual Daily Hours will contain a value.
3. If Statutory is not “N†then the idea is to leave RegHrs @ 0 and print
0 on the
The report
4.That is context for the strange logic: If Statutory = “N’ and
ActualDailyHours > 0 Add ActualDailyHours To Reghrs
Or If Stautory = “N’ and DailyHours > 0 Add DailyHours To Reghrs
Click to expand...

Ok. Try this. Since you have more than two branches, it's better to
use the more flexible Switch() function. It takes arguments in pairs;
the first member of each pair is evaluated as being True or False; if
it's True the function returns the second member of the pair, if not
it goes on to the next member of the pair. So:

RegHrs: Switch([Statutory] = "Y", 0,
[ActualDailyHours] > 0, [ActualDailyHours],
[DailyHours] > 0, [DailyHours],
True, 0)

The last line returns zero if none of the other conditions apply.

You would then Sum the value of RegHrs in your Totals query (rather
than putting sums into the definition of RegHrs).

John W. Vinson[MVP]
Click to expand...
 
Back
Top