Dlookup problems

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!
 
A

adsl

Smallville said:
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!
Click to expand...
 
A

adsl

adsl said:
Smallville said:
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!
Click to expand...
Click to expand...
 
A

adsl

adsl said:
Smallville said:
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!
Click to expand...
Click to expand...
 
G

Guest

does the fields

[data_pull01]![CLI_TYPE], [data_pull01]![claim_type], [BEG_THRSHLD] ,
[END_THRSHLD])
always have value?
what are this fields?
break the code and check if they all return a value, if not I suggest you
use the NZ function to a value
E.g NZ([BEG_THRSHLD],0)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DLooKUp with multiple criteria 14
DLookup and Nz 0
DLookUp 1
dlookup 2
Access 2007 DLOOKUP 26
DLookUp 7
DLookUp behavior 1
Dlookup 7

Top