Access 2007 - Equivalent to Excel's indirect function

  • Thread starter Thread starter RL
  • Start date Start date
R

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
 
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].
 
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?!


KARL DEWEY said:
I have created a query and want one of the fields [AB] to be populated with
Click to expand...
Click to expand...
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


RL said:
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
Click to expand...
Click to expand...
 
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


RL said:
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?!


KARL DEWEY said:
I have created a query and want one of the fields [AB] to be populated with
Click to expand...
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


RL said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top