Prev: Min value & corresponding date
Next: (SELECT
From: MacNut2004 on 28 Apr 2010 10:24
Hello,

I have 2 fields. I have Job # and GL #.

I need to exclude from the GL field, values that are 1234, but ONLY if the
Job # = 999999.

I have tried using this formula in the criteria of the GL# field in my query:

IIf([Job #]=999999,<>1234,[GL #])

and instead of 999999 still showing up in the query and excluding GL# 1234,
999999 doesn't show up at all!

What am I doing wrong??

Thank you!!!!!!!!

MN
From: Jerry Whittle on 28 Apr 2010 11:24
IIf([Job #]<>999999, [GL #], IIf([GL #]=1234, Null, [GL #]))

I'm assuming by exclude that you mean Null.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"MacNut2004" wrote:

> Hello,
>
> I have 2 fields. I have Job # and GL #.
>
> I need to exclude from the GL field, values that are 1234, but ONLY if the
> Job # = 999999.
>
> I have tried using this formula in the criteria of the GL# field in my query:
>
> IIf([Job #]=999999,<>1234,[GL #])
>
> and instead of 999999 still showing up in the query and excluding GL# 1234,
> 999999 doesn't show up at all!
>
> What am I doing wrong??
>
> Thank you!!!!!!!!
>
> MN
From: John W. Vinson on 28 Apr 2010 13:57
On Wed, 28 Apr 2010 07:24:06 -0700, MacNut2004
<MacNut2004(a)discussions.microsoft.com> wrote:

>Hello,
>
>I have 2 fields. I have Job # and GL #.
>
>I need to exclude from the GL field, values that are 1234, but ONLY if the
>Job # = 999999.
>
>I have tried using this formula in the criteria of the GL# field in my query:
>
>IIf([Job #]=999999,<>1234,[GL #])
>
>and instead of 999999 still showing up in the query and excluding GL# 1234,
>999999 doesn't show up at all!
>
>What am I doing wrong??
>
>Thank you!!!!!!!!
>
>MN

You can't pass an operator such as <> in this way. Could you post the entire
SQL of the query? I would guess you will need a clause such as

([Job #] = 999999 AND [GL #] <> 1234) OR ([Job #] <> 999999)
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Min value & corresponding date
Next: (SELECT