Access 2007 - Equivalent to Excel's indirect function

[RL]

Afternoon all,

Is there a function in Access that does the equivalent of an indirect
function in Excel?

I have created a query and want one of the fields [AB] to be populated with
either fields [CD] or [EF] depending on the contents of field [GH].

I know I could do this example with an iif statement, but the real query
could bring back the contents of more than just two fields - I'd be here all
day writing a nested iff function!

Instead, I would like the query to build a reference to either field [CD] or
[EF] using the contents of [GH].

e.g. "[tbl.XY]![" & [GH] & "]"

At the moment, that would simply print the string [tbl.XY][CD] for example.
Is there a function which commands access bring back the contents of that
reference rather than just printing the string?

Any ideas??

Many thanks,
RL

 

[KARL DEWEY]

I have created a query and want one of the fields [AB] to be populated with
either fields [CD] or [EF] depending on the contents of field [GH].
How many different contents of field [GH] are to be used in the decission
making process? Normally the IIF statement would look at [GH] for one
particular items such as "X". If you have many items that can make the
choice then instead of nested IIF's it is better to use IN function or a
translation table.

Post the actual table and field names along with what [GH] must contain to
place contentnts of [CD] into [AB].

 

[RL]

Bit of background...

Our reps work on an 8 week cycle. I am making a tool which, based on a date
range which they enter, spits out a list of accounts that that they should be
reviewing.

A table [t_Cycle] contains one record per rep. Each rep record contains one
field for each day in the cycle. i.e. Monday week 1, Tuesday week 1, ..... ,
Friday week 8. Those fields are populated with geographical areas that should
be reviewed on each of those that days.

I have created a macro which creates a separate table [t_DatesSelected],
listing each date in the range [Date], with the week number (1-8) [Week], the
weekday (Monday - Friday) [Weekday] and whichever rep entered the range [Rep].

The query links [t_Cycle] to [t_DatesSelected] on each table's [Rep] field.
For each date record, I want the query to return a field with the contents of
one of the 40 days in the cycle (Monday Week 1 - Friday Week 8), based on
that date's [Week] and [Weekday] fields.

So the two tables are as follows:

t_Cycle

Rep
Monday Week 1
Tuesday Week 1
Wednesday Week 1
.......
Friday Week 8


t_DatesSelected

Date
Week
Weekday
Rep

I want the query to return

Date
Area

Does that make sense?!

I have created a query and want one of the fields [AB] to be populated with

either fields [CD] or [EF] depending on the contents of field [GH].
How many different contents of field [GH] are to be used in the decission
making process? Normally the IIF statement would look at [GH] for one
particular items such as "X". If you have many items that can make the
choice then instead of nested IIF's it is better to use IN function or a
translation table.

Post the actual table and field names along with what [GH] must contain to
place contentnts of [CD] into [AB].

--
KARL DEWEY
Build a little - Test a little

Afternoon all,

Is there a function in Access that does the equivalent of an indirect
function in Excel?

I have created a query and want one of the fields [AB] to be populated with
either fields [CD] or [EF] depending on the contents of field [GH].

I know I could do this example with an iif statement, but the real query
could bring back the contents of more than just two fields - I'd be here all
day writing a nested iff function!

Instead, I would like the query to build a reference to either field [CD] or
[EF] using the contents of [GH].

e.g. "[tbl.XY]![" & [GH] & "]"

At the moment, that would simply print the string [tbl.XY][CD] for example.
Is there a function which commands access bring back the contents of that
reference rather than just printing the string?

Any ideas??

Many thanks,
RL

 

[KARL DEWEY]

Each rep record contains one field for each day in the cycle. i.e. Monday
week 1, Tuesday week 1, ..... , Friday week 8.
This is a SPREADSHEET instead of table for a relational database.

Your tables needs to look like this --
Rep ---
RepID - Autonumber - primary key
LName - text
FName -
etc -

GeoVisit ---
GeoVisitID - Autonumber - primary key
GeoArea -
RepID - number - integer - foreign key related to Rep table
VisitDate - DateTime
Remarks -
etc.

But what has this got to do with your orignal post about an IIF statement?

--
KARL DEWEY
Build a little - Test a little

Bit of background...

Our reps work on an 8 week cycle. I am making a tool which, based on a date
range which they enter, spits out a list of accounts that that they should be
reviewing.

A table [t_Cycle] contains one record per rep. Each rep record contains one
field for each day in the cycle. i.e. Monday week 1, Tuesday week 1, ..... ,
Friday week 8. Those fields are populated with geographical areas that should
be reviewed on each of those that days.

I have created a macro which creates a separate table [t_DatesSelected],
listing each date in the range [Date], with the week number (1-8) [Week], the
weekday (Monday - Friday) [Weekday] and whichever rep entered the range [Rep].

The query links [t_Cycle] to [t_DatesSelected] on each table's [Rep] field.
For each date record, I want the query to return a field with the contents of
one of the 40 days in the cycle (Monday Week 1 - Friday Week 8), based on
that date's [Week] and [Weekday] fields.

So the two tables are as follows:

t_Cycle

Rep
Monday Week 1
Tuesday Week 1
Wednesday Week 1
......
Friday Week 8


t_DatesSelected

Date
Week
Weekday
Rep

I want the query to return

Date
Area

Does that make sense?!

I have created a query and want one of the fields [AB] to be populated with

either fields [CD] or [EF] depending on the contents of field [GH].
How many different contents of field [GH] are to be used in the decission
making process? Normally the IIF statement would look at [GH] for one
particular items such as "X". If you have many items that can make the
choice then instead of nested IIF's it is better to use IN function or a
translation table.

Post the actual table and field names along with what [GH] must contain to
place contentnts of [CD] into [AB].

--
KARL DEWEY
Build a little - Test a little

Afternoon all,

Is there a function in Access that does the equivalent of an indirect
function in Excel?

I have created a query and want one of the fields [AB] to be populated with
either fields [CD] or [EF] depending on the contents of field [GH].

I know I could do this example with an iif statement, but the real query
could bring back the contents of more than just two fields - I'd be here all
day writing a nested iff function!

Instead, I would like the query to build a reference to either field [CD] or
[EF] using the contents of [GH].

e.g. "[tbl.XY]![" & [GH] & "]"

At the moment, that would simply print the string [tbl.XY][CD] for example.
Is there a function which commands access bring back the contents of that
reference rather than just printing the string?

Any ideas??

Many thanks,
RL