Access Report - Set Text box value from one of two query fields based on criteria...

[NetAdmin]

I have a situation which I can't figure out, but seems like it should be easy to do. I was tasked with maintaining the home built Access applications here at work after the programmer quit.
I cannot re-write this whole application (I Could - but my boss won't let me spend the time on it).

I have a particular query which pulls data from a table, and is then used in a report. For example...

Field1 = name (text field)
Field2 = new_name (text field)
field3 through field10 = more related data

there are about 300 records of which about 100 have a entry in field2 - the rest are blank in field2

Here is my situation:

Report has a textbox on it txtName
I want to set the txtName box to field2 if it has an entry, and set it to field1 if field2 is blank.



Can anyone shed some light on how to pull this off? Thanks to all who take the time to read and reply - I do appreciate your time.

[ranman256]

You could have 2 rpts, one for blank fld2 and one for not...its the SAME report but 2 different queries.
an if on the open report button

if isnull(field2) then
   docmd.openreport "rReport ", acViewPreview
else
   docmd.openreport "rReport with Fld2", acViewPreview
end if

or 1 report with a FILTER the fld2 with a value

DoCmd.OpenReport "report", acViewPreview, , "[field2]='" & Forms!frmMain!field2 & "'"

[NetAdmin]

Clarification:

The final report has to list all 300 records on one report, but with the above needs fulfilled too - if the field2 field has an entry that has to be the txtName entry for that record, otherwise it has to be field1's entry.

[ranman256]

Right .
"rReport" would use field1 entry
"rReport with Fld2" would used field2 entry