Calculated field in query - what syntax?

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I am trying to write a query that will look at 10 different items and
generate a result.

(If field 1 = 1 and Field 2 =1 and field 3 = "ok" and field 4 =3 etc. then
"Approved" else "Not Approved")

What Syntax is required to perform this calculation?

Any help appreciated.

Rob
 
Rob said:
I am trying to write a query that will look at 10 different items and
generate a result.

(If field 1 = 1 and Field 2 =1 and field 3 = "ok" and field 4 =3 etc. then
"Approved" else "Not Approved")

What Syntax is required to perform this calculation?
Click to expand...

Look in help for the IIf() and Switch() functions. They are the closest,
but you will need to nest them and it could get pretty ugly. You might be
better off writing a custom function so the complexities are handled in a
code window rather than the expression in the query. For instance, what
you supplied so far would look like...

Approval: IIf(field1 = 1, IIf(field2 = 1, IIf(field3 = "ok", IIf(field4 =
3, "Approved","Not Approved"),
"Not Approved"), "Not Approved"), "Not Approved")

The same thing handled with a custom function would just be...

Approval: CustFunc(field1, field2, field3, field4)

In a module the code for CustFunc() can be as complicated as required but
still easy to follow because you have the ability to spread it over
multiple lines and add comments etc..

Function CustFunc(fld1 as integer, fld2 as integer, fld3 as String, fld4 as
integer) as String

If fld1 = 1 _
And fld2 = 1 _
And fld3 = "ok" _
And fld4 = 3 Then
CustFunc = "Approved"
Else
CustFunc = "Not Approved"
End if

End Function
 
Thanks Rick,
I am fairly new at this and I don't understand how to tie a function into a
query and how that all works yet.
The sample you provided below does give me the necessary results though
(even if it could be done better a different way)

Thanks,
Rob
 
Can I add an "or" to all these "ands"?
i.e. If I have 3 fields that will essentially cancel each other out...

if fld 1 is Date()-15
OR fld 2 = Exempt
OR fld3 Is Not Null
And fld4 =1 Then
ApprStatus = "Approved"
Else
ApprStauts = "Not Approved"

I know that would probably work in a function adjusting the code as
necessary, but can I do this in your earlier suggestion in the query?

Rob
 
I am trying to write a query that will look at 10 different items and
generate a result.

(If field 1 = 1 and Field 2 =1 and field 3 = "ok" and field 4 =3 etc. then
"Approved" else "Not Approved")

What Syntax is required to perform this calculation?
Click to expand...

The IIF function:

IIF(<logical expression>, "ValueIfTrue", "ValueIfFalse")

There are limits on the length of the expression - if you get an error
you may need a custom VBA function.
 
Rob said:
Can I add an "or" to all these "ands"?
i.e. If I have 3 fields that will essentially cancel each other out...

if fld 1 is Date()-15
OR fld 2 = Exempt
OR fld3 Is Not Null
And fld4 =1 Then
ApprStatus = "Approved"
Else
ApprStauts = "Not Approved"

I know that would probably work in a function adjusting the code as
necessary, but can I do this in your earlier suggestion in the query?
Click to expand...

If you wanted to use IIf() it can support ORs, but that can make them even
more complicated. Simple example...

Approval: IIf(field1 = 1 Or field2 = "ok", "Approved", "Not Approved")

The big complication with ORs is how they should be grouped with the other
tests. You need to use parenthesis properly because there is a big
difference between...

If (fld1 = 1 OR fld2 = "ok") AND fld3 = 12
....and...
If fld1 = 1 OR (fld2 = "ok" AND fld3 = 12)

Here again, this is a lot easier to keep straight in the code for a custom
function than it is in a long nested expression. The danger is if testing
appears to give the correct results only to find out later that the
expression wasn't quite right.
 
Thanks again, that really worked well.
Rob


Rick Brandt said:
If you wanted to use IIf() it can support ORs, but that can make them even
more complicated. Simple example...

Approval: IIf(field1 = 1 Or field2 = "ok", "Approved", "Not Approved")

The big complication with ORs is how they should be grouped with the other
tests. You need to use parenthesis properly because there is a big
difference between...

If (fld1 = 1 OR fld2 = "ok") AND fld3 = 12
...and...
If fld1 = 1 OR (fld2 = "ok" AND fld3 = 12)

Here again, this is a lot easier to keep straight in the code for a custom
function than it is in a long nested expression. The danger is if testing
appears to give the correct results only to find out later that the
expression wasn't quite right.
Click to expand...
 
Back
Top