Formula

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

Guest

I have a formula (IF Statement) in an Excel Spreadsheet which is doing the
following:

=IF(AND(D2>=800,D2<=999),D2*0.04, "")

I want to do the same thing in an Access Query but am having problems. Some
info on my database is:

Table Name - PRPERN
Field Name I want to do the calculation on - "Hours"

Can anyone help me with this formula?

Thank you!

Warren Phillips
 
On the query form, try:

HoursResult: iif(([Hours] >= 800) AND ([Hours] <=999), [Hours] * 0.04, "")

Mauricio Silva
 
Fixing my typos:

IIF(([Hours] > 800) And ([Hours] <= 999),[Hours] * 0.04,"")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Lynn Trapp said:
IIF([Hours] > 800 And [Hours] <= 999),[Hours] * 0.04,"")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


WDP said:
I have a formula (IF Statement) in an Excel Spreadsheet which is doing the
following:

=IF(AND(D2>=800,D2<=999),D2*0.04, "")

I want to do the same thing in an Access Query but am having problems.
Some
info on my database is:

Table Name - PRPERN
Field Name I want to do the calculation on - "Hours"

Can anyone help me with this formula?

Thank you!

Warren Phillips
Click to expand...
Click to expand...
 
That did it......Thank you very much!!!!!!



Lynn Trapp said:
Fixing my typos:

IIF(([Hours] > 800) And ([Hours] <= 999),[Hours] * 0.04,"")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Lynn Trapp said:
IIF([Hours] > 800 And [Hours] <= 999),[Hours] * 0.04,"")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


WDP said:
I have a formula (IF Statement) in an Excel Spreadsheet which is doing the
following:

=IF(AND(D2>=800,D2<=999),D2*0.04, "")

I want to do the same thing in an Access Query but am having problems.
Some
info on my database is:

Table Name - PRPERN
Field Name I want to do the calculation on - "Hours"

Can anyone help me with this formula?

Thank you!

Warren Phillips
Click to expand...
Click to expand...
Click to expand...
 
Small problem with this. Here is my the formula in my Query:

4: IIf((Query1![Safe Hours]>=800) And (Query1![Safe
Hours]<=999),Query1![Safe Hours]*0.04,"")

I am trying to right click on this new field and choose "Properties"...and
change the "Field Properties". I would expect this to allow me to choose
"Standard" and "2" decimal places.....but for some reason the ONLY choices I
get are:

Description
Format
Input Mask
Caption
Smart Tags

Access is not seeing the data as numeric even though the "Safe Hours" field
I am choosing in my query is a number that is formated as "Standard" with "2"
decimal places.

Any thoughts on how I can fix this???

Again....Thank you

Warren









WDP said:
That did it......Thank you very much!!!!!!



Lynn Trapp said:
Fixing my typos:

IIF(([Hours] > 800) And ([Hours] <= 999),[Hours] * 0.04,"")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


Lynn Trapp said:
IIF([Hours] > 800 And [Hours] <= 999),[Hours] * 0.04,"")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


I have a formula (IF Statement) in an Excel Spreadsheet which is doing the
following:

=IF(AND(D2>=800,D2<=999),D2*0.04, "")

I want to do the same thing in an Access Query but am having problems.
Some
info on my database is:

Table Name - PRPERN
Field Name I want to do the calculation on - "Hours"

Can anyone help me with this formula?

Thank you!

Warren Phillips
Click to expand...
Click to expand...
Click to expand...
 
Try using NULL vice "" in your formula. Using the "" causes the field to be
forced to a string format.
Small problem with this. Here is my the formula in my Query:

4: IIf((Query1![Safe Hours]>=800) And (Query1![Safe
Hours]<=999),Query1![Safe Hours]*0.04,"")

I am trying to right click on this new field and choose "Properties"...and
change the "Field Properties". I would expect this to allow me to choose
"Standard" and "2" decimal places.....but for some reason the ONLY choices I
get are:

Description
Format
Input Mask
Caption
Smart Tags

Access is not seeing the data as numeric even though the "Safe Hours" field
I am choosing in my query is a number that is formated as "Standard" with "2"
decimal places.

Any thoughts on how I can fix this???

Again....Thank you

Warren

WDP said:
That did it......Thank you very much!!!!!!



Lynn Trapp said:
Fixing my typos:

IIF(([Hours] > 800) And ([Hours] <= 999),[Hours] * 0.04,"")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


IIF([Hours] > 800 And [Hours] <= 999),[Hours] * 0.04,"")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


I have a formula (IF Statement) in an Excel Spreadsheet which is doing the
following:

=IF(AND(D2>=800,D2<=999),D2*0.04, "")

I want to do the same thing in an Access Query but am having problems.
Some
info on my database is:

Table Name - PRPERN
Field Name I want to do the calculation on - "Hours"

Can anyone help me with this formula?

Thank you!

Warren Phillips
Click to expand...
Click to expand...
Click to expand...
 
John
This solved my issue.....Thank you for taking the time to respond

Warren


John Spencer (MVP) said:
Try using NULL vice "" in your formula. Using the "" causes the field to be
forced to a string format.
Small problem with this. Here is my the formula in my Query:

4: IIf((Query1![Safe Hours]>=800) And (Query1![Safe
Hours]<=999),Query1![Safe Hours]*0.04,"")

I am trying to right click on this new field and choose "Properties"...and
change the "Field Properties". I would expect this to allow me to choose
"Standard" and "2" decimal places.....but for some reason the ONLY choices I
get are:

Description
Format
Input Mask
Caption
Smart Tags

Access is not seeing the data as numeric even though the "Safe Hours" field
I am choosing in my query is a number that is formated as "Standard" with "2"
decimal places.

Any thoughts on how I can fix this???

Again....Thank you

Warren

WDP said:
That did it......Thank you very much!!!!!!



:

Fixing my typos:

IIF(([Hours] > 800) And ([Hours] <= 999),[Hours] * 0.04,"")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


IIF([Hours] > 800 And [Hours] <= 999),[Hours] * 0.04,"")

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html


I have a formula (IF Statement) in an Excel Spreadsheet which is doing the
following:

=IF(AND(D2>=800,D2<=999),D2*0.04, "")

I want to do the same thing in an Access Query but am having problems.
Some
info on my database is:

Table Name - PRPERN
Field Name I want to do the calculation on - "Hours"

Can anyone help me with this formula?

Thank you!

Warren Phillips
Click to expand...
Click to expand...
Click to expand...
 
Back
Top