G
Guest
I have been looking through several different help sources to find an answer
to a DLookup question I have. I have made several attempts, but I still
cannot quite get my expression to work properly.
Here is the scenario:
I have a table of values (data_pull02) including CLI_TYPE, CLAIM_TYPE and
TOT_SUBMT_CHRG. I am trying to solve for a TIER_NUM (basically a ranking
based on the previously listed fields) for each record. I am attempting to
use DLookup to pull the TIER_NUM from reference table CPSADMIN_MV_ANA_TIERS.
The fields used from this table are CLI_TYPE (text), CLAIM_TYPE (text),
BEG_THRSHLD (num) and END_THRSHLD (num).
Spelled out, here is my request:
Find TIER_NUM
From table CPSADMIN_MV_ANA_TIERS
Where [datapull02]![CLI_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLI_TYPE]
and [datapull02]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE]
and [datapull02]![TOT_SUBMT_CHRG] is between [BEG_THRSHLD] and
[END_THRSHLD]
This is the first expression I used:
DLookUp("[TIER_NUM]","CPSADMIN_MV_ANA_TIERS","[CLI_TYPE] = '" &
[data_pull01]![CLI_TYPE] & "' and [CLAIM_TYPE] = '" &
[data_pull01]![claim_type] & "' And [TOT_SUBMT_CHRG] >= " & [BEG_THRSHLD] &
" and [TOT_SUBMT_CHRG]<= " & [END_THRSHLD])
This prompts for a value on BEG/END_THRSHLD and returns #Error.
This is the second expression I used:
DLookUp("[TIER_NUM]","CPSADMIN_MV_ANA_TIERS","[CLI_TYPE] = '" &
[data_pull01]![CLI_TYPE] & "' and [CLAIM_TYPE] = '" &
[data_pull01]![claim_type] & "' And [TOT_SUBMT_CHRG] >= " & "[BEG_THRSHLD]"
& " and [TOT_SUBMT_CHRG]<= " & "[END_THRSHLD]")
This returns mostly Nulls with a few #Error.
I have been going in circles on this for 3 days and have not come close. Any
help offered would be greatly appreciated.
Thanks!
to a DLookup question I have. I have made several attempts, but I still
cannot quite get my expression to work properly.
Here is the scenario:
I have a table of values (data_pull02) including CLI_TYPE, CLAIM_TYPE and
TOT_SUBMT_CHRG. I am trying to solve for a TIER_NUM (basically a ranking
based on the previously listed fields) for each record. I am attempting to
use DLookup to pull the TIER_NUM from reference table CPSADMIN_MV_ANA_TIERS.
The fields used from this table are CLI_TYPE (text), CLAIM_TYPE (text),
BEG_THRSHLD (num) and END_THRSHLD (num).
Spelled out, here is my request:
Find TIER_NUM
From table CPSADMIN_MV_ANA_TIERS
Where [datapull02]![CLI_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLI_TYPE]
and [datapull02]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE]
and [datapull02]![TOT_SUBMT_CHRG] is between [BEG_THRSHLD] and
[END_THRSHLD]
This is the first expression I used:
DLookUp("[TIER_NUM]","CPSADMIN_MV_ANA_TIERS","[CLI_TYPE] = '" &
[data_pull01]![CLI_TYPE] & "' and [CLAIM_TYPE] = '" &
[data_pull01]![claim_type] & "' And [TOT_SUBMT_CHRG] >= " & [BEG_THRSHLD] &
" and [TOT_SUBMT_CHRG]<= " & [END_THRSHLD])
This prompts for a value on BEG/END_THRSHLD and returns #Error.
This is the second expression I used:
DLookUp("[TIER_NUM]","CPSADMIN_MV_ANA_TIERS","[CLI_TYPE] = '" &
[data_pull01]![CLI_TYPE] & "' and [CLAIM_TYPE] = '" &
[data_pull01]![claim_type] & "' And [TOT_SUBMT_CHRG] >= " & "[BEG_THRSHLD]"
& " and [TOT_SUBMT_CHRG]<= " & "[END_THRSHLD]")
This returns mostly Nulls with a few #Error.
I have been going in circles on this for 3 days and have not come close. Any
help offered would be greatly appreciated.
Thanks!