How to select database dependent on lookup

[Garry]

Hi, I can lookup individual fields on a report with:



=IIf(DLookUp("[CT]","Client")="Leisure",DLookUp("[Cost]","RateLeisure"),DLookUp("[Cost]","RateBusiness"))



I have many fields with calculations



How can I choose one or other database from the lookup



=IIf(DLookUp("[CT]","Client")="Leisure",[RateLeisure],[RateBusiness])



Cheers, Garry

 

[Garry]

Should have said

How can I choose one or other database from the lookup to be the
Recordsaet

Hi, I can lookup individual fields on a report with:



=IIf(DLookUp("[CT]","Client")="Leisure",DLookUp("[Cost]","RateLeisure"),DLookUp("[Cost]","RateBusiness"))



I have many fields with calculations



How can I choose one or other database from the lookup



=IIf(DLookUp("[CT]","Client")="Leisure",[RateLeisure],[RateBusiness])



Cheers, Garry

 

[John W. Vinson]

Hi, I can lookup individual fields on a report with:



=IIf(DLookUp("[CT]","Client")="Leisure",DLookUp("[Cost]","RateLeisure"),DLookUp("[Cost]","RateBusiness"))



I have many fields with calculations



How can I choose one or other database from the lookup



=IIf(DLookUp("[CT]","Client")="Leisure",[RateLeisure],[RateBusiness])



Cheers, Garry

Do you mean a different DATABASE - a .mdb or .accdb file containing multiple
tables, forms, reports, queries and code?

Or do you mean a different TABLE within the current database?

And if you have (as appears to be the case) two more-or-less identical tables
for leisure rates and business rates, your table design may be incorrect.
Storing data in tablenames (or fieldnames) is really a bad idea; you may want
instead to consider having a Rates table with a RateType field, which could
have values "Leisure", "Business", "Government Contract", "Gold Star
Customer", etc. etc.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Garry]

HI John

Both tables are in the form of linked xlsx files, supplied by a third party

I think I might produce two reports

One to look up leisure costs the other to look up business

Is this the way to go do you think or is my preferred choice doable

Many thanks, Garry

Hi, I can lookup individual fields on a report with:



=IIf(DLookUp("[CT]","Client")="Leisure",DLookUp("[Cost]","RateLeisure"),DLookUp("[Cost]","RateBusiness"))



I have many fields with calculations



How can I choose one or other database from the lookup



=IIf(DLookUp("[CT]","Client")="Leisure",[RateLeisure],[RateBusiness])



Cheers, Garry

Do you mean a different DATABASE - a .mdb or .accdb file containing
multiple
tables, forms, reports, queries and code?

Or do you mean a different TABLE within the current database?

And if you have (as appears to be the case) two more-or-less identical
tables
for leisure rates and business rates, your table design may be incorrect.
Storing data in tablenames (or fieldnames) is really a bad idea; you may
want
instead to consider having a Rates table with a RateType field, which
could
have values "Leisure", "Business", "Government Contract", "Gold Star
Customer", etc. etc.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com