Conditional Query?

[jeffoest]

Hi all, first time poster here. I'm not a super experienced Access user - but know enough to join a few tables and combine data with (no SQL - just using the Query Design view). So here is my question.



If I have just two tables that I want to combine where both have fields that represent the same thing - let's say "Project Sponsor". What I want to do have happen is that if Table1 has that field populated, then use that population in the result. However if Table1 does NOT have it populated but Table 2 DOES, then I would like to use the result from Table 2. In other words, the result is just one table with one field "Project Sponsor". But it's kind of conditional I suppose.

Thanks for any help - hope I explained it well enough!

[pbaldy]

If the first is Null:

Nz(Table1.FieldName, Table2.FieldName)

[jeffoest]

Thanks! Do I have to put this code into the SQL or can I somehow enter it in the query design view?

[pbaldy]

It can be put in design view:

Whatever: Nz(Table1.FieldName, Table2.FieldName)

[jeffoest]

Ok - playing with it but having trouble... As you said If the first is Null:

Nz(Table1.FieldName, Table2.FieldName) - but I want the first if it's not null, right?

I'm sorry for being so needy - but where in Design View do I put this equation? Tried "Criteria but that didn't work"....

[pbaldy]

You put it in the field row, in a new column.

[jeffoest]

OK - I did that and put the following expression into the field row...
[Nz(Epics.Sponsor,SC Workbook.Sponsor)] which correspond to my table names and field names.

It didn't seem to work without the brackets at all. But, now when I run the query I get an error:
" is not a valid name. Make sure that it does not include invalid characters....

[pbaldy]

Try:

Whatever: Nz(Epics.Sponsor,[SC Workbook].Sponsor)

[jeffoest]

Holy cow - I think we have a winner! Paul - thanks SO much! - Jeff

[pbaldy]

Happy to help Jeff!