Solved combining 2 expressions into one query? (1 Viewer)

[lwarren1968]

How do I combined the 2 expressions below? Ultimately, I want the 2nd part to override the 1st if applicable.

GROSS AMT1: IIf([Transaction ID],[Gross],"")

BUT or however

IIf([Payflow Transaction ID (PNREF)],"",[Gross])

 

[plog]

I don't understand what you want. Perhaps you can make a chart.

A = [Transaction ID]
B = [Payflow Transaction ID (PNREF)]

A=True, B=True, Result=?
A=True, B=False, Result=?
A=False, B=True, Result=?
A=False, B=False, Result=?

Fill in the question marks please.

 

[lwarren1968]

Thank you for taking a look at this. Hopefully below will clarify.

A=True, B=True, Result=B (B SHOULD OVERRIDE A IF B is = TO (PNREF)
A=True, B=False, Result=A
A=False, B=True, Result=B
A=False, B=False, Result=A (BUT THIS SHOULD NEVER OCCUR)

 

[plog]

That doesn't jive with your first post--the results of it were only [Gross] or "". Why are they no longer results? Then you add in this "(B SHOULD OVERRIDE A IF B is = TO (PNREF)" which makes no sense in context.

Now I need data examples. Open a spreadsheet, put all relevant field names as column headers then fill it in with sample data to demonstrate all the cases you need to accomodate.

 

[plog]

if there is an ID in the [transaction ID] field and in (Payflow Tranaction ID(PNREF) field then update [New Field} with [Gross] field otherwise "0.00".

This will produce the results of that:

NewField: IIf(IsNull([Transaction ID]) OR IsNull([Payflow Transaction ID (PNREF)]), 0, Gross)

 

[lwarren1968]

That doesn't jive with your first post--the results of it were only [Gross] or "". Why are they no longer results? Then you add in this "(B SHOULD OVERRIDE A IF B is = TO (PNREF)" which makes no sense in context.

Now I need data examples. Open a spreadsheet, put all relevant field names as column headers then fill it in with sample data to demonstrate all the cases you need to accomodate.

Your right. I guess i'm reading into this to much, but basically if there is an ID in the [transaction ID] field and in (Payflow Tranaction ID(PNREF) field then update [New Field} with [Gross] field otherwise "0.00".

Transaction ID	Payflow Transaction ID (PNREF)	Gross	NEW FIELD
2MA06083W9460441L	BFPP3ECA54FC	25.00	25.00
9FL84965SR1138357	BCPP3EC395B2	559.98	559.98
2VN42830485100800		25.00	0.00