Select based on value of 2 fields

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

Guest

I need to select data from a table that could come from one of two fields
depending on the values. In Access query, I used the following and it worked:

BN: IIf([BranchRandom] Is Not Null,[BranchRandom],[Branch])
Can anybody tell me how to do it in SQL for a form in a ADP Project??


Then I only pull records that match the Branch# entered in my form by using:
[Forms]![frmCreateRandomsPrintCOCForms]![txtBranchNumber] as criteria in the
Access query. Is this possible to do in the SQL for the form in the ADP
Project?

Thanks!
Susan
 
I can never remember the exact syntaxes supported by the command, but what
you're looking for is the CASE statement (and I only have SQL Server
installed on my laptop, not on my "main" computer, so I can't readily check
it out right now), but here goes....

I *believe* the following will work:

CASE BranchRandom WHEN NULL THEN Branch ELSE BranchRandom END

but if not, then the following should definitely work (unless I screwed
something else up...I'm sick & tired, literally):

CASE WHEN BranchRandom IS NULL THEN Branch ELSE BranchRandom END

The first syntax evaluates specifically BranchRandom, and the WHEN clauses
can ONLY apply to BranchRandom, and I believe they can only be values (i.e.,
WHEN 1, WHEN 2, etc. as opposed to WHEN > 1); the second syntax evaluates
any condition, and the WHEN clause is the entire condition (i.e., WHEN
BranchRandom >= SomeOtherValue, WHEN SomeThirdValue = 2, WHEN
SomeFourthValue > 5) and the first condition that matches is the one
executed.

Sorry if that's not well explained; like I said, I'm sick & I'm tired...you
may want to consult SQL Server help for a better (albeit longer)
description.



Rob
 
And of course, now I'm actually paying attention to what you're doing...you
can replace the IIf entirely with:

ADP: COALESCE(BranchRandom, Branch)
or if you ever go back to the MDB: Nz(BranchRandom, Branch)



Rob
 
Back
Top