DLooKUp with multiple criteria

G

Guest

I am trying to run a DLooKUp to pull a ranking level. The problem is that
there are 4 sets of criteria to match on:

1) CLI_TYPE (txt)
2) CLAIM_TYPE (txt)
3) BEG_THRSHLD (num)
4) END_THRSHLD (num)

My statement is as follows:

DLookUp("[tier_num]","CPSADMIN_MV_ANA_TIERS","[cases]![CLI_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLI_TYPE]"
And "[cases]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE]" And "[total]
=[BEG_THRSHLD]" And "[total]<=[END_THRSHLD]")
Click to expand...

I do not get an error, but I do not get the correct results. Any suggestions?

Thanks.
 
D

Douglas J. Steele

The AND needs to be in the quotes. However, the value to which you're
comparing doesn't.

Try something like

DLookUp("[tier_num]","CPSADMIN_MV_ANA_TIERS","[CLI_TYPE] = '" &
[cases]![CLI_TYPE] & "'
And [CLAIM_TYPE] = '" & [cases]![CLAIM_TYPE] & "' And [total] >= " &
[BEG_THRSHLD & " And [total ]<= " & [END_THRSHLD])

Exagerated for clarity, that's

DLookUp("[tier_num]","CPSADMIN_MV_ANA_TIERS","[CLI_TYPE] = ' " &
[cases]![CLI_TYPE] & " '
And [CLAIM_TYPE] = ' " & [cases]![CLAIM_TYPE] & " ' And [total] >= " &
[BEG_THRSHLD & " And [total ]<= " & [END_THRSHLD])

I'm not 100% certain that's correct, since I don't really understand where
the values you're comparing to are coming from.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Smallville said:
I am trying to run a DLooKUp to pull a ranking level. The problem is that
there are 4 sets of criteria to match on:

1) CLI_TYPE (txt)
2) CLAIM_TYPE (txt)
3) BEG_THRSHLD (num)
4) END_THRSHLD (num)

My statement is as follows:

DLookUp("[tier_num]","CPSADMIN_MV_ANA_TIERS","[cases]![CLI_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLI_TYPE]"
And "[cases]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE]" And
"[total]
=[BEG_THRSHLD]" And "[total]<=[END_THRSHLD]")
Click to expand...

I do not get an error, but I do not get the correct results. Any
suggestions?

Thanks.
Click to expand...
 
T

tina

the left side of each equation (x = y) must be the name of a field in your
domain (CPSADMIN_MV_ANA_TIERS). because the function "opens" the domain,
just use the appropriate field name on the left side of the equation - don't
include a reference to the domain. the right side of the equation must be a
value that is available to the system at runtime - usually either a "hard"
value or a reference to a control on an open form. if you're refering to a
control on an open form, the syntax depends on where you're running the
DLookup function from.

also, each And must be inside the double quotes, not outside. i'll
assume all the following statements are true:

tier_num is a field in CPSADMIN_MV_ANA_TIERS.
tier_num is the value you want the DLookup function to return.
CPSADMIN_MV_ANA_TIERS is the name of a table or query in your database.
CLI_TYPE, CLAIM_TYPE, BEG_THRSHLD, and END_THRSHLD are all fields in
CPSADMIN_MV_ANA_TIERS.
you're running this function in an open form's VBA module.
cases is the name of the form.
CLI_TYPE, CLAIM_TYPE, and total are the names of fields in form cases,
and are all numeric data type, not text data type.

assuming the correctness of the above statements, the syntax would be

DLookUp("tier_num", "CPSADMIN_MV_ANA_TIERS", _
"CLI_TYPE = " & Me!CLI_TYPE & " And CLAIM_TYPE = " _
& Me!CLAIM_TYPE & " And BEG_THRSHLD <= " _
& Me!total & " And END_THRSHLD >= " & Me!total)

Me!CLI_TYPE refers to the CLI_TYPE field in the open form's RecordSource
property. ditto for Me!CLAIM_TYPE and Me!total.

see the DLookup Function Help topic in Access VBA Help for detailed
information on the function.

hth


Smallville said:
I am trying to run a DLooKUp to pull a ranking level. The problem is that
there are 4 sets of criteria to match on:

1) CLI_TYPE (txt)
2) CLAIM_TYPE (txt)
3) BEG_THRSHLD (num)
4) END_THRSHLD (num)

My statement is as follows:

DLookUp("[tier_num]","CPSADMIN_MV_ANA_TIERS","[cases]![CLI_TYPE]=[CPSADMIN_M
V_ANA_TIERS]![CLI_TYPE]"
And "[cases]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE]" And "[total]
=[BEG_THRSHLD]" And "[total]<=[END_THRSHLD]")
Click to expand...

I do not get an error, but I do not get the correct results. Any suggestions?

Thanks.
Click to expand...
 
G

Guest

I have tried both offered versions of this (thanks!). However, none works. I
will try to be more specific in the hopes I can find a solution to this:

Reference Table: CPSADMIN_MV_ANA_TIERS
Sought Value: TIER_NUM
Criteria: CLI_TYPE (text string)
CLAIM_TYPE (text string)
BEG_THRSHLD (number)
END_THRSHLD (number)

This is a query written against another table where I am trying to pull the
tier number based on matches against CLI_TYPE and CLAIM_TYPE and the value of
the transaction is between BEG_THRSHLD and END_THRSHLD. I am writing the
query through query builder and there is no form or VBA involved. Those
examples provided below resulted in open string errors.

Thanks!


tina said:
the left side of each equation (x = y) must be the name of a field in your
domain (CPSADMIN_MV_ANA_TIERS). because the function "opens" the domain,
just use the appropriate field name on the left side of the equation - don't
include a reference to the domain. the right side of the equation must be a
value that is available to the system at runtime - usually either a "hard"
value or a reference to a control on an open form. if you're refering to a
control on an open form, the syntax depends on where you're running the
DLookup function from.

also, each And must be inside the double quotes, not outside. i'll
assume all the following statements are true:

tier_num is a field in CPSADMIN_MV_ANA_TIERS.
tier_num is the value you want the DLookup function to return.
CPSADMIN_MV_ANA_TIERS is the name of a table or query in your database.
CLI_TYPE, CLAIM_TYPE, BEG_THRSHLD, and END_THRSHLD are all fields in
CPSADMIN_MV_ANA_TIERS.
you're running this function in an open form's VBA module.
cases is the name of the form.
CLI_TYPE, CLAIM_TYPE, and total are the names of fields in form cases,
and are all numeric data type, not text data type.

assuming the correctness of the above statements, the syntax would be

DLookUp("tier_num", "CPSADMIN_MV_ANA_TIERS", _
"CLI_TYPE = " & Me!CLI_TYPE & " And CLAIM_TYPE = " _
& Me!CLAIM_TYPE & " And BEG_THRSHLD <= " _
& Me!total & " And END_THRSHLD >= " & Me!total)

Me!CLI_TYPE refers to the CLI_TYPE field in the open form's RecordSource
property. ditto for Me!CLAIM_TYPE and Me!total.

see the DLookup Function Help topic in Access VBA Help for detailed
information on the function.

hth


Smallville said:
I am trying to run a DLooKUp to pull a ranking level. The problem is that
there are 4 sets of criteria to match on:

1) CLI_TYPE (txt)
2) CLAIM_TYPE (txt)
3) BEG_THRSHLD (num)
4) END_THRSHLD (num)

My statement is as follows:

DLookUp("[tier_num]","CPSADMIN_MV_ANA_TIERS","[cases]![CLI_TYPE]=[CPSADMIN_M
V_ANA_TIERS]![CLI_TYPE]"
And "[cases]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE]" And "[total]
=[BEG_THRSHLD]" And "[total]<=[END_THRSHLD]")
Click to expand...

I do not get an error, but I do not get the correct results. Any suggestions?

Thanks.
Click to expand...
Click to expand...
 
G

Guest

I have made some mods to this statement but now I get a prompt asking for
values for BEG_THRSHLD and END_THRSHLD.

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])


To clarify, I am trying to find TIER_NUM where
[datapull01]![CLI_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLI_TYPE] AND
[datapull01]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE] AND
[datapull01]![TOT_SUBMT_CHRG]>=[CPSADMIN_MV_ANA_TIERS]![BEG_THRSHLD] AND
[datapull01]![TOT_SUBMT_CHRG]<=[CPSADMIN_MV_ANA_TIERS]![END_THRSHLD].

Again, CLI_TYPE and CLAIM_TYPE are Text Strings and BEG/END_THRSHLD are
Numbers. I am building this statement in Query Builder and there are no forms
involved. I am seeking the correct result from another table withing the DB.
I continue to go in circles on this and any help would be greatly appreciated.

Thanks!

Smallville said:
I have tried both offered versions of this (thanks!). However, none works. I
will try to be more specific in the hopes I can find a solution to this:

Reference Table: CPSADMIN_MV_ANA_TIERS
Sought Value: TIER_NUM
Criteria: CLI_TYPE (text string)
CLAIM_TYPE (text string)
BEG_THRSHLD (number)
END_THRSHLD (number)

This is a query written against another table where I am trying to pull the
tier number based on matches against CLI_TYPE and CLAIM_TYPE and the value of
the transaction is between BEG_THRSHLD and END_THRSHLD. I am writing the
query through query builder and there is no form or VBA involved. Those
examples provided below resulted in open string errors.

Thanks!


tina said:
the left side of each equation (x = y) must be the name of a field in your
domain (CPSADMIN_MV_ANA_TIERS). because the function "opens" the domain,
just use the appropriate field name on the left side of the equation - don't
include a reference to the domain. the right side of the equation must be a
value that is available to the system at runtime - usually either a "hard"
value or a reference to a control on an open form. if you're refering to a
control on an open form, the syntax depends on where you're running the
DLookup function from.

also, each And must be inside the double quotes, not outside. i'll
assume all the following statements are true:

tier_num is a field in CPSADMIN_MV_ANA_TIERS.
tier_num is the value you want the DLookup function to return.
CPSADMIN_MV_ANA_TIERS is the name of a table or query in your database.
CLI_TYPE, CLAIM_TYPE, BEG_THRSHLD, and END_THRSHLD are all fields in
CPSADMIN_MV_ANA_TIERS.
you're running this function in an open form's VBA module.
cases is the name of the form.
CLI_TYPE, CLAIM_TYPE, and total are the names of fields in form cases,
and are all numeric data type, not text data type.

assuming the correctness of the above statements, the syntax would be

DLookUp("tier_num", "CPSADMIN_MV_ANA_TIERS", _
"CLI_TYPE = " & Me!CLI_TYPE & " And CLAIM_TYPE = " _
& Me!CLAIM_TYPE & " And BEG_THRSHLD <= " _
& Me!total & " And END_THRSHLD >= " & Me!total)

Me!CLI_TYPE refers to the CLI_TYPE field in the open form's RecordSource
property. ditto for Me!CLAIM_TYPE and Me!total.

see the DLookup Function Help topic in Access VBA Help for detailed
information on the function.

hth


Smallville said:
I am trying to run a DLooKUp to pull a ranking level. The problem is that
there are 4 sets of criteria to match on:

1) CLI_TYPE (txt)
2) CLAIM_TYPE (txt)
3) BEG_THRSHLD (num)
4) END_THRSHLD (num)

My statement is as follows:

DLookUp("[tier_num]","CPSADMIN_MV_ANA_TIERS","[cases]![CLI_TYPE]=[CPSADMIN_M
V_ANA_TIERS]![CLI_TYPE]"
And "[cases]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE]" And "[total]
=[BEG_THRSHLD]" And "[total]<=[END_THRSHLD]")

I do not get an error, but I do not get the correct results. Any suggestions?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
A

adsl

tina said:
the left side of each equation (x = y) must be the name of a field in your
domain (CPSADMIN_MV_ANA_TIERS). because the function "opens" the domain,
just use the appropriate field name on the left side of the equation -
don't
include a reference to the domain. the right side of the equation must be
a
value that is available to the system at runtime - usually either a "hard"
value or a reference to a control on an open form. if you're refering to a
control on an open form, the syntax depends on where you're running the
DLookup function from.

also, each And must be inside the double quotes, not outside. i'll
assume all the following statements are true:

tier_num is a field in CPSADMIN_MV_ANA_TIERS.
tier_num is the value you want the DLookup function to return.
CPSADMIN_MV_ANA_TIERS is the name of a table or query in your database.
CLI_TYPE, CLAIM_TYPE, BEG_THRSHLD, and END_THRSHLD are all fields in
CPSADMIN_MV_ANA_TIERS.
you're running this function in an open form's VBA module.
cases is the name of the form.
CLI_TYPE, CLAIM_TYPE, and total are the names of fields in form
cases,
and are all numeric data type, not text data type.

assuming the correctness of the above statements, the syntax would be

DLookUp("tier_num", "CPSADMIN_MV_ANA_TIERS", _
"CLI_TYPE = " & Me!CLI_TYPE & " And CLAIM_TYPE = " _
& Me!CLAIM_TYPE & " And BEG_THRSHLD <= " _
& Me!total & " And END_THRSHLD >= " & Me!total)

Me!CLI_TYPE refers to the CLI_TYPE field in the open form's RecordSource
property. ditto for Me!CLAIM_TYPE and Me!total.

see the DLookup Function Help topic in Access VBA Help for detailed
information on the function.

hth


Smallville said:
I am trying to run a DLooKUp to pull a ranking level. The problem is that
there are 4 sets of criteria to match on:

1) CLI_TYPE (txt)
2) CLAIM_TYPE (txt)
3) BEG_THRSHLD (num)
4) END_THRSHLD (num)

My statement is as follows:

DLookUp("[tier_num]","CPSADMIN_MV_ANA_TIERS","[cases]![CLI_TYPE]=[CPSADMIN_M
V_ANA_TIERS]![CLI_TYPE]"
And "[cases]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE]" And "[total]
=[BEG_THRSHLD]" And "[total]<=[END_THRSHLD]")
Click to expand...

I do not get an error, but I do not get the correct results. Any suggestions?

Thanks.
Click to expand...
Click to expand...
 
A

adsl

Smallville said:
I have tried both offered versions of this (thanks!). However, none works.
I
will try to be more specific in the hopes I can find a solution to this:

Reference Table: CPSADMIN_MV_ANA_TIERS
Sought Value: TIER_NUM
Criteria: CLI_TYPE (text string)
CLAIM_TYPE (text string)
BEG_THRSHLD (number)
END_THRSHLD (number)

This is a query written against another table where I am trying to pull
the
tier number based on matches against CLI_TYPE and CLAIM_TYPE and the value
of
the transaction is between BEG_THRSHLD and END_THRSHLD. I am writing the
query through query builder and there is no form or VBA involved. Those
examples provided below resulted in open string errors.

Thanks!


tina said:
the left side of each equation (x = y) must be the name of a field in
your
domain (CPSADMIN_MV_ANA_TIERS). because the function "opens" the domain,
just use the appropriate field name on the left side of the equation -
don't
include a reference to the domain. the right side of the equation must be
a
value that is available to the system at runtime - usually either a
"hard"
value or a reference to a control on an open form. if you're refering to
a
control on an open form, the syntax depends on where you're running the
DLookup function from.

also, each And must be inside the double quotes, not outside. i'll
assume all the following statements are true:

tier_num is a field in CPSADMIN_MV_ANA_TIERS.
tier_num is the value you want the DLookup function to return.
CPSADMIN_MV_ANA_TIERS is the name of a table or query in your database.
CLI_TYPE, CLAIM_TYPE, BEG_THRSHLD, and END_THRSHLD are all fields in
CPSADMIN_MV_ANA_TIERS.
you're running this function in an open form's VBA module.
cases is the name of the form.
CLI_TYPE, CLAIM_TYPE, and total are the names of fields in form
cases,
and are all numeric data type, not text data type.

assuming the correctness of the above statements, the syntax would be

DLookUp("tier_num", "CPSADMIN_MV_ANA_TIERS", _
"CLI_TYPE = " & Me!CLI_TYPE & " And CLAIM_TYPE = " _
& Me!CLAIM_TYPE & " And BEG_THRSHLD <= " _
& Me!total & " And END_THRSHLD >= " & Me!total)

Me!CLI_TYPE refers to the CLI_TYPE field in the open form's RecordSource
property. ditto for Me!CLAIM_TYPE and Me!total.

see the DLookup Function Help topic in Access VBA Help for detailed
information on the function.

hth


Smallville said:
I am trying to run a DLooKUp to pull a ranking level. The problem is
that
there are 4 sets of criteria to match on:

1) CLI_TYPE (txt)
2) CLAIM_TYPE (txt)
3) BEG_THRSHLD (num)
4) END_THRSHLD (num)

My statement is as follows:

DLookUp("[tier_num]","CPSADMIN_MV_ANA_TIERS","[cases]![CLI_TYPE]=[CPSADMIN_M
V_ANA_TIERS]![CLI_TYPE]"
And "[cases]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE]" And "[total]
=[BEG_THRSHLD]" And "[total]<=[END_THRSHLD]")

I do not get an error, but I do not get the correct results. Any suggestions?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
A

adsl

Smallville said:
I have made some mods to this statement but now I get a prompt asking for
values for BEG_THRSHLD and END_THRSHLD.

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])


To clarify, I am trying to find TIER_NUM where
[datapull01]![CLI_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLI_TYPE] AND
[datapull01]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE] AND
[datapull01]![TOT_SUBMT_CHRG]>=[CPSADMIN_MV_ANA_TIERS]![BEG_THRSHLD] AND
[datapull01]![TOT_SUBMT_CHRG]<=[CPSADMIN_MV_ANA_TIERS]![END_THRSHLD].

Again, CLI_TYPE and CLAIM_TYPE are Text Strings and BEG/END_THRSHLD are
Numbers. I am building this statement in Query Builder and there are no
forms
involved. I am seeking the correct result from another table withing the
DB.
I continue to go in circles on this and any help would be greatly
appreciated.

Thanks!

Smallville said:
I have tried both offered versions of this (thanks!). However, none
works. I
will try to be more specific in the hopes I can find a solution to this:

Reference Table: CPSADMIN_MV_ANA_TIERS
Sought Value: TIER_NUM
Criteria: CLI_TYPE (text string)
CLAIM_TYPE (text string)
BEG_THRSHLD (number)
END_THRSHLD (number)

This is a query written against another table where I am trying to pull
the
tier number based on matches against CLI_TYPE and CLAIM_TYPE and the
value of
the transaction is between BEG_THRSHLD and END_THRSHLD. I am writing the
query through query builder and there is no form or VBA involved. Those
examples provided below resulted in open string errors.

Thanks!


tina said:
the left side of each equation (x = y) must be the name of a field in
your
domain (CPSADMIN_MV_ANA_TIERS). because the function "opens" the
domain,
just use the appropriate field name on the left side of the equation -
don't
include a reference to the domain. the right side of the equation must
be a
value that is available to the system at runtime - usually either a
"hard"
value or a reference to a control on an open form. if you're refering
to a
control on an open form, the syntax depends on where you're running the
DLookup function from.

also, each And must be inside the double quotes, not outside. i'll
assume all the following statements are true:

tier_num is a field in CPSADMIN_MV_ANA_TIERS.
tier_num is the value you want the DLookup function to return.
CPSADMIN_MV_ANA_TIERS is the name of a table or query in your database.
CLI_TYPE, CLAIM_TYPE, BEG_THRSHLD, and END_THRSHLD are all fields in
CPSADMIN_MV_ANA_TIERS.
you're running this function in an open form's VBA module.
cases is the name of the form.
CLI_TYPE, CLAIM_TYPE, and total are the names of fields in form
cases,
and are all numeric data type, not text data type.

assuming the correctness of the above statements, the syntax would be

DLookUp("tier_num", "CPSADMIN_MV_ANA_TIERS", _
"CLI_TYPE = " & Me!CLI_TYPE & " And CLAIM_TYPE = " _
& Me!CLAIM_TYPE & " And BEG_THRSHLD <= " _
& Me!total & " And END_THRSHLD >= " & Me!total)

Me!CLI_TYPE refers to the CLI_TYPE field in the open form's
RecordSource
property. ditto for Me!CLAIM_TYPE and Me!total.

see the DLookup Function Help topic in Access VBA Help for detailed
information on the function.

hth


I am trying to run a DLooKUp to pull a ranking level. The problem is
that
there are 4 sets of criteria to match on:

1) CLI_TYPE (txt)
2) CLAIM_TYPE (txt)
3) BEG_THRSHLD (num)
4) END_THRSHLD (num)

My statement is as follows:


DLookUp("[tier_num]","CPSADMIN_MV_ANA_TIERS","[cases]![CLI_TYPE]=[CPSADMIN_M
V_ANA_TIERS]![CLI_TYPE]"
And "[cases]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE]" And
"[total]
=[BEG_THRSHLD]" And "[total]<=[END_THRSHLD]")

I do not get an error, but I do not get the correct results. Any
suggestions?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
T

tina

sorry, i don't understand the context of how you're using a DLookUp()
function in a query. post your SQL statement, please.


Smallville said:
I have tried both offered versions of this (thanks!). However, none works. I
will try to be more specific in the hopes I can find a solution to this:

Reference Table: CPSADMIN_MV_ANA_TIERS
Sought Value: TIER_NUM
Criteria: CLI_TYPE (text string)
CLAIM_TYPE (text string)
BEG_THRSHLD (number)
END_THRSHLD (number)

This is a query written against another table where I am trying to pull the
tier number based on matches against CLI_TYPE and CLAIM_TYPE and the value of
the transaction is between BEG_THRSHLD and END_THRSHLD. I am writing the
query through query builder and there is no form or VBA involved. Those
examples provided below resulted in open string errors.

Thanks!


tina said:
the left side of each equation (x = y) must be the name of a field in your
domain (CPSADMIN_MV_ANA_TIERS). because the function "opens" the domain,
just use the appropriate field name on the left side of the equation - don't
include a reference to the domain. the right side of the equation must be a
value that is available to the system at runtime - usually either a "hard"
value or a reference to a control on an open form. if you're refering to a
control on an open form, the syntax depends on where you're running the
DLookup function from.

also, each And must be inside the double quotes, not outside. i'll
assume all the following statements are true:

tier_num is a field in CPSADMIN_MV_ANA_TIERS.
tier_num is the value you want the DLookup function to return.
CPSADMIN_MV_ANA_TIERS is the name of a table or query in your database.
CLI_TYPE, CLAIM_TYPE, BEG_THRSHLD, and END_THRSHLD are all fields in
CPSADMIN_MV_ANA_TIERS.
you're running this function in an open form's VBA module.
cases is the name of the form.
CLI_TYPE, CLAIM_TYPE, and total are the names of fields in form cases,
and are all numeric data type, not text data type.

assuming the correctness of the above statements, the syntax would be

DLookUp("tier_num", "CPSADMIN_MV_ANA_TIERS", _
"CLI_TYPE = " & Me!CLI_TYPE & " And CLAIM_TYPE = " _
& Me!CLAIM_TYPE & " And BEG_THRSHLD <= " _
& Me!total & " And END_THRSHLD >= " & Me!total)

Me!CLI_TYPE refers to the CLI_TYPE field in the open form's RecordSource
property. ditto for Me!CLAIM_TYPE and Me!total.

see the DLookup Function Help topic in Access VBA Help for detailed
information on the function.

hth


Smallville said:
I am trying to run a DLooKUp to pull a ranking level. The problem is that
there are 4 sets of criteria to match on:

1) CLI_TYPE (txt)
2) CLAIM_TYPE (txt)
3) BEG_THRSHLD (num)
4) END_THRSHLD (num)

My statement is as follows:
Click to expand...
DLookUp("[tier_num]","CPSADMIN_MV_ANA_TIERS","[cases]![CLI_TYPE]=[CPSADMIN_M
V_ANA_TIERS]![CLI_TYPE]"
And "[cases]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE]" And "[total]
=[BEG_THRSHLD]" And "[total]<=[END_THRSHLD]")

I do not get an error, but I do not get the correct results. Any suggestions?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Tina,

Here is the SQL statement:

SELECT 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]) AS tier;




tina said:
sorry, i don't understand the context of how you're using a DLookUp()
function in a query. post your SQL statement, please.


Smallville said:
I have tried both offered versions of this (thanks!). However, none works. I
will try to be more specific in the hopes I can find a solution to this:

Reference Table: CPSADMIN_MV_ANA_TIERS
Sought Value: TIER_NUM
Criteria: CLI_TYPE (text string)
CLAIM_TYPE (text string)
BEG_THRSHLD (number)
END_THRSHLD (number)

This is a query written against another table where I am trying to pull the
tier number based on matches against CLI_TYPE and CLAIM_TYPE and the value of
the transaction is between BEG_THRSHLD and END_THRSHLD. I am writing the
query through query builder and there is no form or VBA involved. Those
examples provided below resulted in open string errors.

Thanks!


tina said:
the left side of each equation (x = y) must be the name of a field in your
domain (CPSADMIN_MV_ANA_TIERS). because the function "opens" the domain,
just use the appropriate field name on the left side of the equation - don't
include a reference to the domain. the right side of the equation must be a
value that is available to the system at runtime - usually either a "hard"
value or a reference to a control on an open form. if you're refering to a
control on an open form, the syntax depends on where you're running the
DLookup function from.

also, each And must be inside the double quotes, not outside. i'll
assume all the following statements are true:

tier_num is a field in CPSADMIN_MV_ANA_TIERS.
tier_num is the value you want the DLookup function to return.
CPSADMIN_MV_ANA_TIERS is the name of a table or query in your database.
CLI_TYPE, CLAIM_TYPE, BEG_THRSHLD, and END_THRSHLD are all fields in
CPSADMIN_MV_ANA_TIERS.
you're running this function in an open form's VBA module.
cases is the name of the form.
CLI_TYPE, CLAIM_TYPE, and total are the names of fields in form cases,
and are all numeric data type, not text data type.

assuming the correctness of the above statements, the syntax would be

DLookUp("tier_num", "CPSADMIN_MV_ANA_TIERS", _
"CLI_TYPE = " & Me!CLI_TYPE & " And CLAIM_TYPE = " _
& Me!CLAIM_TYPE & " And BEG_THRSHLD <= " _
& Me!total & " And END_THRSHLD >= " & Me!total)

Me!CLI_TYPE refers to the CLI_TYPE field in the open form's RecordSource
property. ditto for Me!CLAIM_TYPE and Me!total.

see the DLookup Function Help topic in Access VBA Help for detailed
information on the function.

hth


I am trying to run a DLooKUp to pull a ranking level. The problem is that
there are 4 sets of criteria to match on:

1) CLI_TYPE (txt)
2) CLAIM_TYPE (txt)
3) BEG_THRSHLD (num)
4) END_THRSHLD (num)

My statement is as follows:


DLookUp("[tier_num]","CPSADMIN_MV_ANA_TIERS","[cases]![CLI_TYPE]=[CPSADMIN_M
V_ANA_TIERS]![CLI_TYPE]"
And "[cases]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE]" And
"[total]
=[BEG_THRSHLD]" And "[total]<=[END_THRSHLD]")

I do not get an error, but I do not get the correct results. Any
suggestions?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
T

tina

umm, is that your complete SQL statement? i've never seen anybody do that
before - and i still can't figure out what you're trying to accomplish.

what is data_pull01? a form? another table? the syntax you're using is
"object!property" but you haven't declared the object type as a form,
report, whatever.
the left side of each equation (CLI_TYPE, CLAIM_TYPE, and TOT_SUBMT_CHRG)
have to be fields in your domain (CPSADMIN_MV_ANA_TIERS). but where are the
fields you're referring to on the right side of each equation? two of them
are in data_pull01, obviously, but your reference appears incomplete as i
said above. the other two fields, BEG_THRSHLD and END_THRSHLD, where are
they located?

you're going to have to explain your setup, and what specifically what
you're trying to do, in some detail, because right now i have no clue what
you're doing or how to help you do it correctly, and your use of a SQL
statement makes no sense to me at all.


Smallville said:
Tina,

Here is the SQL statement:

SELECT 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]) AS tier;




tina said:
sorry, i don't understand the context of how you're using a DLookUp()
function in a query. post your SQL statement, please.


Smallville said:
I have tried both offered versions of this (thanks!). However, none
Click to expand...
works.
I
will try to be more specific in the hopes I can find a solution to this:

Reference Table: CPSADMIN_MV_ANA_TIERS
Sought Value: TIER_NUM
Criteria: CLI_TYPE (text string)
CLAIM_TYPE (text string)
BEG_THRSHLD (number)
END_THRSHLD (number)

This is a query written against another table where I am trying to
Click to expand...
pull
the
tier number based on matches against CLI_TYPE and CLAIM_TYPE and the
Click to expand...
value
of
the transaction is between BEG_THRSHLD and END_THRSHLD. I am writing the
query through query builder and there is no form or VBA involved. Those
examples provided below resulted in open string errors.

Thanks!


:

the left side of each equation (x = y) must be the name of a field
Click to expand...
in
your
domain (CPSADMIN_MV_ANA_TIERS). because the function "opens" the domain,
just use the appropriate field name on the left side of the
Click to expand...
equation -
don't
include a reference to the domain. the right side of the equation
Click to expand...
must
be a
value that is available to the system at runtime - usually either a "hard"
value or a reference to a control on an open form. if you're
Click to expand...
refering to
a
control on an open form, the syntax depends on where you're running the
DLookup function from.

also, each And must be inside the double quotes, not outside. i'll
assume all the following statements are true:

tier_num is a field in CPSADMIN_MV_ANA_TIERS.
tier_num is the value you want the DLookup function to return.
CPSADMIN_MV_ANA_TIERS is the name of a table or query in your database.
CLI_TYPE, CLAIM_TYPE, BEG_THRSHLD, and END_THRSHLD are all fields in
CPSADMIN_MV_ANA_TIERS.
you're running this function in an open form's VBA module.
cases is the name of the form.
CLI_TYPE, CLAIM_TYPE, and total are the names of fields in form cases,
and are all numeric data type, not text data type.

assuming the correctness of the above statements, the syntax would be

DLookUp("tier_num", "CPSADMIN_MV_ANA_TIERS", _
"CLI_TYPE = " & Me!CLI_TYPE & " And CLAIM_TYPE = " _
& Me!CLAIM_TYPE & " And BEG_THRSHLD <= " _
& Me!total & " And END_THRSHLD >= " & Me!total)

Me!CLI_TYPE refers to the CLI_TYPE field in the open form's RecordSource
property. ditto for Me!CLAIM_TYPE and Me!total.

see the DLookup Function Help topic in Access VBA Help for detailed
information on the function.

hth


I am trying to run a DLooKUp to pull a ranking level. The problem
Click to expand...
is
that
there are 4 sets of criteria to match on:

1) CLI_TYPE (txt)
2) CLAIM_TYPE (txt)
3) BEG_THRSHLD (num)
4) END_THRSHLD (num)

My statement is as follows:
Click to expand...
DLookUp("[tier_num]","CPSADMIN_MV_ANA_TIERS","[cases]![CLI_TYPE]=[CPSADMIN_M
V_ANA_TIERS]![CLI_TYPE]"
And "[cases]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE]" And
"[total]
=[BEG_THRSHLD]" And "[total]<=[END_THRSHLD]")

I do not get an error, but I do not get the correct results. Any
suggestions?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

As I have mentioned, I used query builder to create this statement. There are
no forms involved, simply two tables - "data_pull02" and
"cpsadmin_mv_ana_tiers"

The fields involved by table are:

data_pull02 cpsadmin_mv_ana_tiers
case_id cli_type (text)
cli_type (text) claim_type (text)
claim_type (text) beg_thrshld (num)
tot_submt_chrg (num) end_thrshld (num)
tier_num

I am trying to solve for [tier_num] for each case_id from
cpsadmin_mv_ana_tiers where cli_type (data_pull02) = cli_type
(cpsadmin_mv_ana_tiers) and claim_type (data_pull02) = claim_type
(cpsadmin_mv_ana_tiers) and tot_submt_chrg is between beg_thrshld and
end_thrshld.

Once again, there are no forms nor any VB involved here. I am attempting to
solve for tier_num in query builder which is where my SQL statement came
from. If you have any ideas, please let me know. I am not sure how much more
specific I can be.

Thanks.






tina said:
umm, is that your complete SQL statement? i've never seen anybody do that
before - and i still can't figure out what you're trying to accomplish.

what is data_pull01? a form? another table? the syntax you're using is
"object!property" but you haven't declared the object type as a form,
report, whatever.
the left side of each equation (CLI_TYPE, CLAIM_TYPE, and TOT_SUBMT_CHRG)
have to be fields in your domain (CPSADMIN_MV_ANA_TIERS). but where are the
fields you're referring to on the right side of each equation? two of them
are in data_pull01, obviously, but your reference appears incomplete as i
said above. the other two fields, BEG_THRSHLD and END_THRSHLD, where are
they located?

you're going to have to explain your setup, and what specifically what
you're trying to do, in some detail, because right now i have no clue what
you're doing or how to help you do it correctly, and your use of a SQL
statement makes no sense to me at all.


Smallville said:
Tina,

Here is the SQL statement:

SELECT 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]) AS tier;




tina said:
sorry, i don't understand the context of how you're using a DLookUp()
function in a query. post your SQL statement, please.


I have tried both offered versions of this (thanks!). However, none works.
I
will try to be more specific in the hopes I can find a solution to this:

Reference Table: CPSADMIN_MV_ANA_TIERS
Sought Value: TIER_NUM
Criteria: CLI_TYPE (text string)
CLAIM_TYPE (text string)
BEG_THRSHLD (number)
END_THRSHLD (number)

This is a query written against another table where I am trying to pull
the
tier number based on matches against CLI_TYPE and CLAIM_TYPE and the value
of
the transaction is between BEG_THRSHLD and END_THRSHLD. I am writing the
query through query builder and there is no form or VBA involved. Those
examples provided below resulted in open string errors.

Thanks!


:

the left side of each equation (x = y) must be the name of a field in
your
domain (CPSADMIN_MV_ANA_TIERS). because the function "opens" the domain,
just use the appropriate field name on the left side of the equation -
don't
include a reference to the domain. the right side of the equation must
be a
value that is available to the system at runtime - usually either a
"hard"
value or a reference to a control on an open form. if you're refering to
a
control on an open form, the syntax depends on where you're running the
DLookup function from.

also, each And must be inside the double quotes, not outside. i'll
assume all the following statements are true:

tier_num is a field in CPSADMIN_MV_ANA_TIERS.
tier_num is the value you want the DLookup function to return.
CPSADMIN_MV_ANA_TIERS is the name of a table or query in your database.
CLI_TYPE, CLAIM_TYPE, BEG_THRSHLD, and END_THRSHLD are all fields in
CPSADMIN_MV_ANA_TIERS.
you're running this function in an open form's VBA module.
cases is the name of the form.
CLI_TYPE, CLAIM_TYPE, and total are the names of fields in form
cases,
and are all numeric data type, not text data type.

assuming the correctness of the above statements, the syntax would be

DLookUp("tier_num", "CPSADMIN_MV_ANA_TIERS", _
"CLI_TYPE = " & Me!CLI_TYPE & " And CLAIM_TYPE = " _
& Me!CLAIM_TYPE & " And BEG_THRSHLD <= " _
& Me!total & " And END_THRSHLD >= " & Me!total)

Me!CLI_TYPE refers to the CLI_TYPE field in the open form's
RecordSource
property. ditto for Me!CLAIM_TYPE and Me!total.

see the DLookup Function Help topic in Access VBA Help for detailed
information on the function.

hth


I am trying to run a DLooKUp to pull a ranking level. The problem is
that
there are 4 sets of criteria to match on:

1) CLI_TYPE (txt)
2) CLAIM_TYPE (txt)
3) BEG_THRSHLD (num)
4) END_THRSHLD (num)

My statement is as follows:



DLookUp("[tier_num]","CPSADMIN_MV_ANA_TIERS","[cases]![CLI_TYPE]=[CPSADMIN_M
V_ANA_TIERS]![CLI_TYPE]"
And "[cases]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE]" And
"[total]
=[BEG_THRSHLD]" And "[total]<=[END_THRSHLD]")

I do not get an error, but I do not get the correct results. Any
suggestions?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
T

tina

ok, that clarifies the situation. at this point, i see no value in using a
DLookup inside the query, where you have direct access to the tables
already. could be what you need is a subquery. give me a day to work with it
and get back to you (or for someone else to offer a solution).


Smallville said:
As I have mentioned, I used query builder to create this statement. There are
no forms involved, simply two tables - "data_pull02" and
"cpsadmin_mv_ana_tiers"

The fields involved by table are:

data_pull02 cpsadmin_mv_ana_tiers
case_id cli_type (text)
cli_type (text) claim_type (text)
claim_type (text) beg_thrshld (num)
tot_submt_chrg (num) end_thrshld (num)
tier_num

I am trying to solve for [tier_num] for each case_id from
cpsadmin_mv_ana_tiers where cli_type (data_pull02) = cli_type
(cpsadmin_mv_ana_tiers) and claim_type (data_pull02) = claim_type
(cpsadmin_mv_ana_tiers) and tot_submt_chrg is between beg_thrshld and
end_thrshld.

Once again, there are no forms nor any VB involved here. I am attempting to
solve for tier_num in query builder which is where my SQL statement came
from. If you have any ideas, please let me know. I am not sure how much more
specific I can be.

Thanks.






tina said:
umm, is that your complete SQL statement? i've never seen anybody do that
before - and i still can't figure out what you're trying to accomplish.

what is data_pull01? a form? another table? the syntax you're using is
"object!property" but you haven't declared the object type as a form,
report, whatever.
the left side of each equation (CLI_TYPE, CLAIM_TYPE, and TOT_SUBMT_CHRG)
have to be fields in your domain (CPSADMIN_MV_ANA_TIERS). but where are the
fields you're referring to on the right side of each equation? two of them
are in data_pull01, obviously, but your reference appears incomplete as i
said above. the other two fields, BEG_THRSHLD and END_THRSHLD, where are
they located?

you're going to have to explain your setup, and what specifically what
you're trying to do, in some detail, because right now i have no clue what
you're doing or how to help you do it correctly, and your use of a SQL
statement makes no sense to me at all.


Smallville said:
Tina,

Here is the SQL statement:

SELECT DLookUp("[TIER_NUM]","CPSADMIN_MV_ANA_TIERS","[CLI_TYPE] = '" &
[data_pull01]![CLI_TYPE] & "' and [CLAIM_TYPE] = '" &
[data_pull01]![claim_type] & "' And [TOT_SUBMT_CHRG] >= " &
Click to expand...
[BEG_THRSHLD]
&
" and [TOT_SUBMT_CHRG]<= " & [END_THRSHLD]) AS tier;




:

sorry, i don't understand the context of how you're using a DLookUp()
function in a query. post your SQL statement, please.


I have tried both offered versions of this (thanks!). However,
Click to expand...
none
works.
I
will try to be more specific in the hopes I can find a solution to this:

Reference Table: CPSADMIN_MV_ANA_TIERS
Sought Value: TIER_NUM
Criteria: CLI_TYPE (text string)
CLAIM_TYPE (text string)
BEG_THRSHLD (number)
END_THRSHLD (number)

This is a query written against another table where I am trying to pull
the
tier number based on matches against CLI_TYPE and CLAIM_TYPE and
Click to expand...
the
value
of
the transaction is between BEG_THRSHLD and END_THRSHLD. I am
Click to expand...
writing
the
query through query builder and there is no form or VBA involved. Those
examples provided below resulted in open string errors.

Thanks!


:

the left side of each equation (x = y) must be the name of a
Click to expand...
field
in
your
domain (CPSADMIN_MV_ANA_TIERS). because the function "opens" the domain,
just use the appropriate field name on the left side of the equation -
don't
include a reference to the domain. the right side of the
Click to expand...
equation
must
be a
value that is available to the system at runtime - usually either a
"hard"
value or a reference to a control on an open form. if you're refering to
a
control on an open form, the syntax depends on where you're
Click to expand...
running
the
DLookup function from.

also, each And must be inside the double quotes, not
Click to expand...
outside.
i'll
assume all the following statements are true:

tier_num is a field in CPSADMIN_MV_ANA_TIERS.
tier_num is the value you want the DLookup function to return.
CPSADMIN_MV_ANA_TIERS is the name of a table or query in your database.
CLI_TYPE, CLAIM_TYPE, BEG_THRSHLD, and END_THRSHLD are all fields in
CPSADMIN_MV_ANA_TIERS.
you're running this function in an open form's VBA module.
cases is the name of the form.
CLI_TYPE, CLAIM_TYPE, and total are the names of fields in form
cases,
and are all numeric data type, not text data type.

assuming the correctness of the above statements, the syntax
Click to expand...
would
be
DLookUp("tier_num", "CPSADMIN_MV_ANA_TIERS", _
"CLI_TYPE = " & Me!CLI_TYPE & " And CLAIM_TYPE = " _
& Me!CLAIM_TYPE & " And BEG_THRSHLD <= " _
& Me!total & " And END_THRSHLD >= " & Me!total)

Me!CLI_TYPE refers to the CLI_TYPE field in the open form's
RecordSource
property. ditto for Me!CLAIM_TYPE and Me!total.

see the DLookup Function Help topic in Access VBA Help for detailed
information on the function.

hth


I am trying to run a DLooKUp to pull a ranking level. The
Click to expand...
problem
is
that
there are 4 sets of criteria to match on:

1) CLI_TYPE (txt)
2) CLAIM_TYPE (txt)
3) BEG_THRSHLD (num)
4) END_THRSHLD (num)

My statement is as follows:
Click to expand...
DLookUp("[tier_num]","CPSADMIN_MV_ANA_TIERS","[cases]![CLI_TYPE]=[CPSADMIN_M
V_ANA_TIERS]![CLI_TYPE]"
And
Click to expand...
"[cases]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE]"
And
"[total]
=[BEG_THRSHLD]" And "[total]<=[END_THRSHLD]")

I do not get an error, but I do not get the correct results. Any
suggestions?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Will do. Thanks for all of the effort!


tina said:
ok, that clarifies the situation. at this point, i see no value in using a
DLookup inside the query, where you have direct access to the tables
already. could be what you need is a subquery. give me a day to work with it
and get back to you (or for someone else to offer a solution).


Smallville said:
As I have mentioned, I used query builder to create this statement. There are
no forms involved, simply two tables - "data_pull02" and
"cpsadmin_mv_ana_tiers"

The fields involved by table are:

data_pull02 cpsadmin_mv_ana_tiers
case_id cli_type (text)
cli_type (text) claim_type (text)
claim_type (text) beg_thrshld (num)
tot_submt_chrg (num) end_thrshld (num)
tier_num

I am trying to solve for [tier_num] for each case_id from
cpsadmin_mv_ana_tiers where cli_type (data_pull02) = cli_type
(cpsadmin_mv_ana_tiers) and claim_type (data_pull02) = claim_type
(cpsadmin_mv_ana_tiers) and tot_submt_chrg is between beg_thrshld and
end_thrshld.

Once again, there are no forms nor any VB involved here. I am attempting to
solve for tier_num in query builder which is where my SQL statement came
from. If you have any ideas, please let me know. I am not sure how much more
specific I can be.

Thanks.






tina said:
umm, is that your complete SQL statement? i've never seen anybody do that
before - and i still can't figure out what you're trying to accomplish.

what is data_pull01? a form? another table? the syntax you're using is
"object!property" but you haven't declared the object type as a form,
report, whatever.
the left side of each equation (CLI_TYPE, CLAIM_TYPE, and TOT_SUBMT_CHRG)
have to be fields in your domain (CPSADMIN_MV_ANA_TIERS). but where are the
fields you're referring to on the right side of each equation? two of them
are in data_pull01, obviously, but your reference appears incomplete as i
said above. the other two fields, BEG_THRSHLD and END_THRSHLD, where are
they located?

you're going to have to explain your setup, and what specifically what
you're trying to do, in some detail, because right now i have no clue what
you're doing or how to help you do it correctly, and your use of a SQL
statement makes no sense to me at all.


Tina,

Here is the SQL statement:

SELECT 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]) AS tier;




:

sorry, i don't understand the context of how you're using a DLookUp()
function in a query. post your SQL statement, please.


I have tried both offered versions of this (thanks!). However, none
works.
I
will try to be more specific in the hopes I can find a solution to
this:

Reference Table: CPSADMIN_MV_ANA_TIERS
Sought Value: TIER_NUM
Criteria: CLI_TYPE (text string)
CLAIM_TYPE (text string)
BEG_THRSHLD (number)
END_THRSHLD (number)

This is a query written against another table where I am trying to
pull
the
tier number based on matches against CLI_TYPE and CLAIM_TYPE and the
value
of
the transaction is between BEG_THRSHLD and END_THRSHLD. I am writing
the
query through query builder and there is no form or VBA involved.
Those
examples provided below resulted in open string errors.

Thanks!


:

the left side of each equation (x = y) must be the name of a field
in
your
domain (CPSADMIN_MV_ANA_TIERS). because the function "opens" the
domain,
just use the appropriate field name on the left side of the
equation -
don't
include a reference to the domain. the right side of the equation
must
be a
value that is available to the system at runtime - usually either a
"hard"
value or a reference to a control on an open form. if you're
refering to
a
control on an open form, the syntax depends on where you're running
the
DLookup function from.

also, each And must be inside the double quotes, not outside.
i'll
assume all the following statements are true:

tier_num is a field in CPSADMIN_MV_ANA_TIERS.
tier_num is the value you want the DLookup function to return.
CPSADMIN_MV_ANA_TIERS is the name of a table or query in your
database.
CLI_TYPE, CLAIM_TYPE, BEG_THRSHLD, and END_THRSHLD are all fields in
CPSADMIN_MV_ANA_TIERS.
you're running this function in an open form's VBA module.
cases is the name of the form.
CLI_TYPE, CLAIM_TYPE, and total are the names of fields in form
cases,
and are all numeric data type, not text data type.

assuming the correctness of the above statements, the syntax would
be

DLookUp("tier_num", "CPSADMIN_MV_ANA_TIERS", _
"CLI_TYPE = " & Me!CLI_TYPE & " And CLAIM_TYPE = " _
& Me!CLAIM_TYPE & " And BEG_THRSHLD <= " _
& Me!total & " And END_THRSHLD >= " & Me!total)

Me!CLI_TYPE refers to the CLI_TYPE field in the open form's
RecordSource
property. ditto for Me!CLAIM_TYPE and Me!total.

see the DLookup Function Help topic in Access VBA Help for detailed
information on the function.

hth


I am trying to run a DLooKUp to pull a ranking level. The problem
is
that
there are 4 sets of criteria to match on:

1) CLI_TYPE (txt)
2) CLAIM_TYPE (txt)
3) BEG_THRSHLD (num)
4) END_THRSHLD (num)

My statement is as follows:




DLookUp("[tier_num]","CPSADMIN_MV_ANA_TIERS","[cases]![CLI_TYPE]=[CPSADMIN_M
V_ANA_TIERS]![CLI_TYPE]"
And "[cases]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE]"
And
"[total]
=[BEG_THRSHLD]" And "[total]<=[END_THRSHLD]")

I do not get an error, but I do not get the correct results. Any
suggestions?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
T

tina

well, i found one query solution. i did a pretty limited test with fake
table data, hopefully it was inclusive enough to validate the query.
assuming it otherwise works on your data, the only caveat is that if you
have a record in table data_pull02 with a tot_submt_chrg value that does not
fall between the beginning and ending threshold values of a matching
(cli_type and claim_type) record in table cpsadmin_mv_ana_tiers, then that
data_pull02 record will be excluded from the query results.

SELECT data_pull02.case_id, data_pull02.cli_type, data_pull02.claim_type,
data_pull02.tot_submt_chrg, cpsadmin_mv_ana_tiers.tier_num
FROM data_pull02 LEFT JOIN cpsadmin_mv_ana_tiers ON (data_pull02.claim_type
= cpsadmin_mv_ana_tiers.claim_type) AND (data_pull02.cli_type =
cpsadmin_mv_ana_tiers.cli_type)
WHERE [beg_thrshld]-[tot_submt_chrg]<=0 AND
[end_thrshld]-[tot_submt_chrg]>=0;

hth


Smallville said:
Will do. Thanks for all of the effort!


tina said:
ok, that clarifies the situation. at this point, i see no value in using a
DLookup inside the query, where you have direct access to the tables
already. could be what you need is a subquery. give me a day to work with it
and get back to you (or for someone else to offer a solution).


Smallville said:
As I have mentioned, I used query builder to create this statement.
Click to expand...
There
are
no forms involved, simply two tables - "data_pull02" and
"cpsadmin_mv_ana_tiers"

The fields involved by table are:

data_pull02 cpsadmin_mv_ana_tiers
case_id cli_type (text)
cli_type (text) claim_type (text)
claim_type (text) beg_thrshld (num)
tot_submt_chrg (num) end_thrshld (num)
tier_num

I am trying to solve for [tier_num] for each case_id from
cpsadmin_mv_ana_tiers where cli_type (data_pull02) = cli_type
(cpsadmin_mv_ana_tiers) and claim_type (data_pull02) = claim_type
(cpsadmin_mv_ana_tiers) and tot_submt_chrg is between beg_thrshld and
end_thrshld.

Once again, there are no forms nor any VB involved here. I am
Click to expand...
attempting
to
solve for tier_num in query builder which is where my SQL statement came
from. If you have any ideas, please let me know. I am not sure how
Click to expand...
much
more
specific I can be.

Thanks.






:

umm, is that your complete SQL statement? i've never seen anybody do that
before - and i still can't figure out what you're trying to accomplish.

what is data_pull01? a form? another table? the syntax you're using is
"object!property" but you haven't declared the object type as a form,
report, whatever.
the left side of each equation (CLI_TYPE, CLAIM_TYPE, and TOT_SUBMT_CHRG)
have to be fields in your domain (CPSADMIN_MV_ANA_TIERS). but where
Click to expand...
are
the
fields you're referring to on the right side of each equation? two
Click to expand...
of
them
are in data_pull01, obviously, but your reference appears incomplete
Click to expand...
as
i
said above. the other two fields, BEG_THRSHLD and END_THRSHLD, where are
they located?

you're going to have to explain your setup, and what specifically what
you're trying to do, in some detail, because right now i have no
Click to expand...
clue
what
you're doing or how to help you do it correctly, and your use of a SQL
statement makes no sense to me at all.


Tina,

Here is the SQL statement:

SELECT 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]) AS tier;




:

sorry, i don't understand the context of how you're using a DLookUp()
function in a query. post your SQL statement, please.


I have tried both offered versions of this (thanks!). However, none
works.
I
will try to be more specific in the hopes I can find a solution to
this:

Reference Table: CPSADMIN_MV_ANA_TIERS
Sought Value: TIER_NUM
Criteria: CLI_TYPE (text string)
CLAIM_TYPE (text string)
BEG_THRSHLD (number)
END_THRSHLD (number)

This is a query written against another table where I am trying to
pull
the
tier number based on matches against CLI_TYPE and CLAIM_TYPE
Click to expand...
and
the
value
of
the transaction is between BEG_THRSHLD and END_THRSHLD. I am writing
the
query through query builder and there is no form or VBA involved.
Those
examples provided below resulted in open string errors.

Thanks!


:

the left side of each equation (x = y) must be the name of a field
in
your
domain (CPSADMIN_MV_ANA_TIERS). because the function "opens" the
domain,
just use the appropriate field name on the left side of the
equation -
don't
include a reference to the domain. the right side of the equation
must
be a
value that is available to the system at runtime - usually either a
"hard"
value or a reference to a control on an open form. if you're
refering to
a
control on an open form, the syntax depends on where you're running
the
DLookup function from.

also, each And must be inside the double quotes, not outside.
i'll
assume all the following statements are true:

tier_num is a field in CPSADMIN_MV_ANA_TIERS.
tier_num is the value you want the DLookup function to return.
CPSADMIN_MV_ANA_TIERS is the name of a table or query in your
database.
CLI_TYPE, CLAIM_TYPE, BEG_THRSHLD, and END_THRSHLD are all fields in
CPSADMIN_MV_ANA_TIERS.
you're running this function in an open form's VBA module.
cases is the name of the form.
CLI_TYPE, CLAIM_TYPE, and total are the names of fields
Click to expand...
in
form
cases,
and are all numeric data type, not text data type.

assuming the correctness of the above statements, the syntax would
be

DLookUp("tier_num", "CPSADMIN_MV_ANA_TIERS", _
"CLI_TYPE = " & Me!CLI_TYPE & " And CLAIM_TYPE = " _
& Me!CLAIM_TYPE & " And BEG_THRSHLD <= " _
& Me!total & " And END_THRSHLD >= " & Me!total)

Me!CLI_TYPE refers to the CLI_TYPE field in the open form's
RecordSource
property. ditto for Me!CLAIM_TYPE and Me!total.

see the DLookup Function Help topic in Access VBA Help for detailed
information on the function.

hth


I am trying to run a DLooKUp to pull a ranking level. The problem
is
that
there are 4 sets of criteria to match on:

1) CLI_TYPE (txt)
2) CLAIM_TYPE (txt)
3) BEG_THRSHLD (num)
4) END_THRSHLD (num)

My statement is as follows:
Click to expand...
DLookUp("[tier_num]","CPSADMIN_MV_ANA_TIERS","[cases]![CLI_TYPE]=[CPSADMIN_M
V_ANA_TIERS]![CLI_TYPE]"
And "[cases]![CLAIM_TYPE]=[CPSADMIN_MV_ANA_TIERS]![CLAIM_TYPE]"
And
"[total]
=[BEG_THRSHLD]" And "[total]<=[END_THRSHLD]")

I do not get an error, but I do not get the correct
Click to expand...
results.
Any
suggestions?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 

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 problems 4
dlookup criteria 7
multiple issues with access form 1
Trying to Using a Query and a Dlookup function 3
Design and layout for Order Analysis? 5
Help with DLookup Criteria 4
Using Max or DMax in DLookUp criteria? 1
Help with multiple criteria DLookup 4

Top