Exclude data from field, when another field equals a certain numbe

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

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

On Wed, 28 Apr 2010 07:24:06 -0700, 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

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]