Make a field using two tables

[hrts4him]

I have two tables with the same field lets call them a.flag and b.flag. I am making a join query of these two tables, and need to add a field that create an if.then.else criteria. If table a.flag is blank then b.flag else a.flag.

 

[pbaldy]

If it's Null:

Nz(a.Flag, b.Flag)

 

[hrts4him]

This is what I have
Field: flag
Table: A
Show = yes (flag checked)
Criteria: Nz([a].[flag],.[flag])

Doesn't seem to work. Did I miss something?

 

[pbaldy]

I would expect it as a field, not a criteria. That's not the joining field is it?

 

[hrts4him]

That is not my key joining fields. I have two tables and I join them by c,d and e. I just need a to come from one table or the other. Would I need to do that outside of query design?

 

[pbaldy]

Just put that in a new field instead of the criteria. Access will alias it with Expr1, which you can change.

 

[hrts4him]

So it would look like
Field: new field name = nz (

 

[pbaldy]

Yes; have you tried it?

 

[hrts4him]

Yes and i get a parameter box but when i try to take expr out it adds it back

 

[hrts4him]

Ok so here is what i did and it worked
Test2: Nz
Worked great!

 

[pbaldy]

The parameter prompt is Access telling you it can't find something (whatever is listed in the box). Double check your spellings. You can't take out the Expr1, but you can change it to something of your choosing. Thus in design view you'd have:

DesiredFieldName: Nz(a.Flag, b.Flag)

If you're still stuck, can you attach the db here?

 

[pbaldy]

Ah you got it, good.