Multiple fields in one field on a form

[MMichaels]

This seemed so easy to me when it came up and a suggestion and now that I have tried actually doing it, I can't figure it out!

I have a "search" form and it is a search by last name. I have 5 forms but they all are pulling from one table. So, I have one main table behind this one search form and it is searching for the membername, membernumber, ID and FormType. Now, not all the forms have membernames, instead, they have a vendorname, how can I get this form to show the membername if there is no vendorname and vice versa??? Is it a formula in the control source? I've tried numerous things with no luck... I am on Windows 2000 Pro and Access 2000.

Thanks in advance for any help!!!

Melissa

 

[rjoubert]

Try this for the control source...

= IIF(IsNull([MemberName]), [VendorName], [MemberName])

 

[MMichaels]

That works, but how about if I had three of them, like membernumber, vendorname and assetnumber? Then how would I state it?

 

[Remou]

What do you want the textbox to show? Is it possible that all three will have a result? Do you want to show this?

[tt]Trim(membernumber & " " & vendorname & " " & assetnumber)[/tt]

Or do you only want to show one? Which takes priority?

 

[TheAceMan1]

rjoubert . . .

Is this homework?

See Ya! . . . . . .

 

[MMichaels]

Thank you SOOOO much, Remou, that worked PERFECTLY! You saved the day!

TheAceMan1 - homework? Wow... college was so long ago.. I'm a Database Analyst who hasn't used Access for years.

 

[rjoubert]

TheAceMan1 - MMichaels is the OP, not me.

 

[ProgramError]

an expansion on remou's suggestion

=Trim([membernumber] & IIf(([membernumber] And [vendornumber]),", ","") & [vendornumber] & IIf((([membernumber] Or [vendornumber]) And [assetnumber]),", ","") & [assetnumber])

will give you a , between the fields only if the field is not empty

Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.