searching a table using multiple criteria

G

Guest

I have a table with 3 fields (Field1, Field2, Field3). On a form, there are
2 fields, Val1 and Val2. I need to be able to search the table, returning
the value in Fields 3 where Val1 = Field1 and Val2 < Field2. The table is
sorted by Field1 ascending and Field2 descending.

Thanks in advance, Dan
 
G

Guest

The DLookup should do it for you:

DLookup("[Field3]", "MyTable", "[Field1] = " & Me.Val1 & " And [Field2] <
" & Me.Val2])

A couple of notes:
The syntax above assumes Field1 and Field2 are both numeric. If they are
not, you will need to modify the code to include the delimiters for other
data types.

DLookup will return Null if no match is found.
 
G

Guest

Thanks Dave,
I'll give it a shot....
Dan

Klatuu said:
The DLookup should do it for you:

DLookup("[Field3]", "MyTable", "[Field1] = " & Me.Val1 & " And [Field2] <
" & Me.Val2])

A couple of notes:
The syntax above assumes Field1 and Field2 are both numeric. If they are
not, you will need to modify the code to include the delimiters for other
data types.

DLookup will return Null if no match is found.
--
Dave Hargis, Microsoft Access MVP


dfeigen115 said:
I have a table with 3 fields (Field1, Field2, Field3). On a form, there are
2 fields, Val1 and Val2. I need to be able to search the table, returning
the value in Fields 3 where Val1 = Field1 and Val2 < Field2. The table is
sorted by Field1 ascending and Field2 descending.

Thanks in advance, Dan
Click to expand...
Click to expand...
 
G

Guest

Doug, a followup question. If I needed to do something similar during the
generation of a report, woudl the dllokup work? In the report val1 & val2
are populated, but would be used inm the dlookup against the same table.
thanks,Dan

Klatuu said:
The DLookup should do it for you:

DLookup("[Field3]", "MyTable", "[Field1] = " & Me.Val1 & " And [Field2] <
" & Me.Val2])

A couple of notes:
The syntax above assumes Field1 and Field2 are both numeric. If they are
not, you will need to modify the code to include the delimiters for other
data types.

DLookup will return Null if no match is found.
--
Dave Hargis, Microsoft Access MVP


dfeigen115 said:
I have a table with 3 fields (Field1, Field2, Field3). On a form, there are
2 fields, Val1 and Val2. I need to be able to search the table, returning
the value in Fields 3 where Val1 = Field1 and Val2 < Field2. The table is
sorted by Field1 ascending and Field2 descending.

Thanks in advance, Dan
Click to expand...
Click to expand...
 
G

Guest

Doug?
If you are confusing me with Douglas J. Steele, I am honored and he is
probably upset :)

You can use the same DLookup in the report. You would put it in the control
source of a text box. Instead of referencing the control names on the report
(val1, val2), you would reference the corresponding controls on the report.
--
Dave Hargis, Microsoft Access MVP


dfeigen115 said:
Doug, a followup question. If I needed to do something similar during the
generation of a report, woudl the dllokup work? In the report val1 & val2
are populated, but would be used inm the dlookup against the same table.
thanks,Dan

Klatuu said:
The DLookup should do it for you:

DLookup("[Field3]", "MyTable", "[Field1] = " & Me.Val1 & " And [Field2] <
" & Me.Val2])

A couple of notes:
The syntax above assumes Field1 and Field2 are both numeric. If they are
not, you will need to modify the code to include the delimiters for other
data types.

DLookup will return Null if no match is found.
--
Dave Hargis, Microsoft Access MVP


dfeigen115 said:
I have a table with 3 fields (Field1, Field2, Field3). On a form, there are
2 fields, Val1 and Val2. I need to be able to search the table, returning
the value in Fields 3 where Val1 = Field1 and Val2 < Field2. The table is
sorted by Field1 ascending and Field2 descending.

Thanks in advance, Dan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Oops, no really meant you Dave (some one named Doug stepped into my ioffice
as I started to compose the question). Multi-tasking will be the end of me
soon enough....
Dan

Klatuu said:
Doug?
If you are confusing me with Douglas J. Steele, I am honored and he is
probably upset :)

You can use the same DLookup in the report. You would put it in the control
source of a text box. Instead of referencing the control names on the report
(val1, val2), you would reference the corresponding controls on the report.
--
Dave Hargis, Microsoft Access MVP


dfeigen115 said:
Doug, a followup question. If I needed to do something similar during the
generation of a report, woudl the dllokup work? In the report val1 & val2
are populated, but would be used inm the dlookup against the same table.
thanks,Dan

Klatuu said:
The DLookup should do it for you:

DLookup("[Field3]", "MyTable", "[Field1] = " & Me.Val1 & " And [Field2] <
" & Me.Val2])

A couple of notes:
The syntax above assumes Field1 and Field2 are both numeric. If they are
not, you will need to modify the code to include the delimiters for other
data types.

DLookup will return Null if no match is found.
--
Dave Hargis, Microsoft Access MVP


:

I have a table with 3 fields (Field1, Field2, Field3). On a form, there are
2 fields, Val1 and Val2. I need to be able to search the table, returning
the value in Fields 3 where Val1 = Field1 and Val2 < Field2. The table is
sorted by Field1 ascending and Field2 descending.

Thanks in advance, Dan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

LOL
I have the same problem.
There is a frequent MVP poster named Douglas J. Steele whom I consider to be
not only the most knowledgable person I know when it comes to Access, but
also has the best approach to most problems.
--
Dave Hargis, Microsoft Access MVP


dfeigen115 said:
Oops, no really meant you Dave (some one named Doug stepped into my ioffice
as I started to compose the question). Multi-tasking will be the end of me
soon enough....
Dan

Klatuu said:
Doug?
If you are confusing me with Douglas J. Steele, I am honored and he is
probably upset :)

You can use the same DLookup in the report. You would put it in the control
source of a text box. Instead of referencing the control names on the report
(val1, val2), you would reference the corresponding controls on the report.
--
Dave Hargis, Microsoft Access MVP


dfeigen115 said:
Doug, a followup question. If I needed to do something similar during the
generation of a report, woudl the dllokup work? In the report val1 & val2
are populated, but would be used inm the dlookup against the same table.
thanks,Dan

:

The DLookup should do it for you:

DLookup("[Field3]", "MyTable", "[Field1] = " & Me.Val1 & " And [Field2] <
" & Me.Val2])

A couple of notes:
The syntax above assumes Field1 and Field2 are both numeric. If they are
not, you will need to modify the code to include the delimiters for other
data types.

DLookup will return Null if no match is found.
--
Dave Hargis, Microsoft Access MVP


:

I have a table with 3 fields (Field1, Field2, Field3). On a form, there are
2 fields, Val1 and Val2. I need to be able to search the table, returning
the value in Fields 3 where Val1 = Field1 and Val2 < Field2. The table is
sorted by Field1 ascending and Field2 descending.

Thanks in advance, Dan
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

<blush>

But why does everyone refuse to notice that it's Doug among friends? <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Klatuu said:
LOL
I have the same problem.
There is a frequent MVP poster named Douglas J. Steele whom I consider to
be
not only the most knowledgable person I know when it comes to Access, but
also has the best approach to most problems.
--
Dave Hargis, Microsoft Access MVP


dfeigen115 said:
Oops, no really meant you Dave (some one named Doug stepped into my
ioffice
as I started to compose the question). Multi-tasking will be the end of
me
soon enough....
Dan

Klatuu said:
Doug?
If you are confusing me with Douglas J. Steele, I am honored and he is
probably upset :)

You can use the same DLookup in the report. You would put it in the
control
source of a text box. Instead of referencing the control names on the
report
(val1, val2), you would reference the corresponding controls on the
report.
--
Dave Hargis, Microsoft Access MVP


:

Doug, a followup question. If I needed to do something similar during
the
generation of a report, woudl the dllokup work? In the report val1 &
val2
are populated, but would be used inm the dlookup against the same
table.
thanks,Dan

:

The DLookup should do it for you:

DLookup("[Field3]", "MyTable", "[Field1] = " & Me.Val1 & " And
[Field2] <
" & Me.Val2])

A couple of notes:
The syntax above assumes Field1 and Field2 are both numeric. If
they are
not, you will need to modify the code to include the delimiters for
other
data types.

DLookup will return Null if no match is found.
--
Dave Hargis, Microsoft Access MVP


:

I have a table with 3 fields (Field1, Field2, Field3). On a
form, there are
2 fields, Val1 and Val2. I need to be able to search the table,
returning
the value in Fields 3 where Val1 = Field1 and Val2 < Field2. The
table is
sorted by Field1 ascending and Field2 descending.

Thanks in advance, Dan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Had not noticed you use Doug.

I do really respect your knowledge of Access and your ability to cut through
the clutter. I have learned a lot from you and you keep me on my toes. I
always thing "If I screw this up, I will hear from Doug" :)
--
Dave Hargis, Microsoft Access MVP


Douglas J. Steele said:
<blush>

But why does everyone refuse to notice that it's Doug among friends? <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Klatuu said:
LOL
I have the same problem.
There is a frequent MVP poster named Douglas J. Steele whom I consider to
be
not only the most knowledgable person I know when it comes to Access, but
also has the best approach to most problems.
--
Dave Hargis, Microsoft Access MVP


dfeigen115 said:
Oops, no really meant you Dave (some one named Doug stepped into my
ioffice
as I started to compose the question). Multi-tasking will be the end of
me
soon enough....
Dan

:

Doug?
If you are confusing me with Douglas J. Steele, I am honored and he is
probably upset :)

You can use the same DLookup in the report. You would put it in the
control
source of a text box. Instead of referencing the control names on the
report
(val1, val2), you would reference the corresponding controls on the
report.
--
Dave Hargis, Microsoft Access MVP


:

Doug, a followup question. If I needed to do something similar during
the
generation of a report, woudl the dllokup work? In the report val1 &
val2
are populated, but would be used inm the dlookup against the same
table.
thanks,Dan

:

The DLookup should do it for you:

DLookup("[Field3]", "MyTable", "[Field1] = " & Me.Val1 & " And
[Field2] <
" & Me.Val2])

A couple of notes:
The syntax above assumes Field1 and Field2 are both numeric. If
they are
not, you will need to modify the code to include the delimiters for
other
data types.

DLookup will return Null if no match is found.
--
Dave Hargis, Microsoft Access MVP


:

I have a table with 3 fields (Field1, Field2, Field3). On a
form, there are
2 fields, Val1 and Val2. I need to be able to search the table,
returning
the value in Fields 3 where Val1 = Field1 and Val2 < Field2. The
table is
sorted by Field1 ascending and Field2 descending.

Thanks in advance, Dan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Dave (and or Doug),

I'm still working to get the kinks out, so I've simplified my dlookup to
include just 1 criteria. The statement in the datasource for the control on
the report reads:

=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin]
& ")")

where: AFPercentage and CLIN are fields in the table
CostPerformanceAwardFee. The field "caclin" is the name of the control in the
report that I'm trying to find the corresponding AFPercentage for. The
variant between the statement above and the sample you provided is I dropped
the "me." from the reference to "caclin" since it kept changes the reference
to from me.caclin to [me].[caclin] and wnating a value for "me". When I run
the report, the result is #error. Your thoughts?

Thanks, Dan

Klatuu said:
Had not noticed you use Doug.

I do really respect your knowledge of Access and your ability to cut through
the clutter. I have learned a lot from you and you keep me on my toes. I
always thing "If I screw this up, I will hear from Doug" :)
--
Dave Hargis, Microsoft Access MVP


Douglas J. Steele said:
<blush>

But why does everyone refuse to notice that it's Doug among friends? <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Klatuu said:
LOL
I have the same problem.
There is a frequent MVP poster named Douglas J. Steele whom I consider to
be
not only the most knowledgable person I know when it comes to Access, but
also has the best approach to most problems.
--
Dave Hargis, Microsoft Access MVP


:

Oops, no really meant you Dave (some one named Doug stepped into my
ioffice
as I started to compose the question). Multi-tasking will be the end of
me
soon enough....
Dan

:

Doug?
If you are confusing me with Douglas J. Steele, I am honored and he is
probably upset :)

You can use the same DLookup in the report. You would put it in the
control
source of a text box. Instead of referencing the control names on the
report
(val1, val2), you would reference the corresponding controls on the
report.
--
Dave Hargis, Microsoft Access MVP


:

Doug, a followup question. If I needed to do something similar during
the
generation of a report, woudl the dllokup work? In the report val1 &
val2
are populated, but would be used inm the dlookup against the same
table.
thanks,Dan

:

The DLookup should do it for you:

DLookup("[Field3]", "MyTable", "[Field1] = " & Me.Val1 & " And
[Field2] <
" & Me.Val2])

A couple of notes:
The syntax above assumes Field1 and Field2 are both numeric. If
they are
not, you will need to modify the code to include the delimiters for
other
data types.

DLookup will return Null if no match is found.
--
Dave Hargis, Microsoft Access MVP


:

I have a table with 3 fields (Field1, Field2, Field3). On a
form, there are
2 fields, Val1 and Val2. I need to be able to search the table,
returning
the value in Fields 3 where Val1 = Field1 and Val2 < Field2. The
table is
sorted by Field1 ascending and Field2 descending.

Thanks in advance, Dan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I don't rememer if you said earlier it was for the control source of a
control, but the reason the Me doesn't work in that context is that the
DLookup goes to Jet as an SQL statement. Jet will not understand Me, so you
have to drop it or fully qualify it with [Forms]![FormName]![ControlName].

As to running the report and getting the error, How are you opening the
report? It may be it is not understanding the reference to the control on
the form.
--
Dave Hargis, Microsoft Access MVP


dfeigen115 said:
Dave (and or Doug),

I'm still working to get the kinks out, so I've simplified my dlookup to
include just 1 criteria. The statement in the datasource for the control on
the report reads:

=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin]
& ")")

where: AFPercentage and CLIN are fields in the table
CostPerformanceAwardFee. The field "caclin" is the name of the control in the
report that I'm trying to find the corresponding AFPercentage for. The
variant between the statement above and the sample you provided is I dropped
the "me." from the reference to "caclin" since it kept changes the reference
to from me.caclin to [me].[caclin] and wnating a value for "me". When I run
the report, the result is #error. Your thoughts?

Thanks, Dan

Klatuu said:
Had not noticed you use Doug.

I do really respect your knowledge of Access and your ability to cut through
the clutter. I have learned a lot from you and you keep me on my toes. I
always thing "If I screw this up, I will hear from Doug" :)
--
Dave Hargis, Microsoft Access MVP


Douglas J. Steele said:
<blush>

But why does everyone refuse to notice that it's Doug among friends? <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LOL
I have the same problem.
There is a frequent MVP poster named Douglas J. Steele whom I consider to
be
not only the most knowledgable person I know when it comes to Access, but
also has the best approach to most problems.
--
Dave Hargis, Microsoft Access MVP


:

Oops, no really meant you Dave (some one named Doug stepped into my
ioffice
as I started to compose the question). Multi-tasking will be the end of
me
soon enough....
Dan

:

Doug?
If you are confusing me with Douglas J. Steele, I am honored and he is
probably upset :)

You can use the same DLookup in the report. You would put it in the
control
source of a text box. Instead of referencing the control names on the
report
(val1, val2), you would reference the corresponding controls on the
report.
--
Dave Hargis, Microsoft Access MVP


:

Doug, a followup question. If I needed to do something similar during
the
generation of a report, woudl the dllokup work? In the report val1 &
val2
are populated, but would be used inm the dlookup against the same
table.
thanks,Dan

:

The DLookup should do it for you:

DLookup("[Field3]", "MyTable", "[Field1] = " & Me.Val1 & " And
[Field2] <
" & Me.Val2])

A couple of notes:
The syntax above assumes Field1 and Field2 are both numeric. If
they are
not, you will need to modify the code to include the delimiters for
other
data types.

DLookup will return Null if no match is found.
--
Dave Hargis, Microsoft Access MVP


:

I have a table with 3 fields (Field1, Field2, Field3). On a
form, there are
2 fields, Val1 and Val2. I need to be able to search the table,
returning
the value in Fields 3 where Val1 = Field1 and Val2 < Field2. The
table is
sorted by Field1 ascending and Field2 descending.

Thanks in advance, Dan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Dave,
the report is run adhoc and summarizes/groups data based on the field CACLIN
(data source is a query). The report (hopefully) will be able to lookup the
AFPercentage associated with the current grouping of CACLIN and display a
calculated value using the AFpercentage returned from the dlookup.
Dan

Klatuu said:
I don't rememer if you said earlier it was for the control source of a
control, but the reason the Me doesn't work in that context is that the
DLookup goes to Jet as an SQL statement. Jet will not understand Me, so you
have to drop it or fully qualify it with [Forms]![FormName]![ControlName].

As to running the report and getting the error, How are you opening the
report? It may be it is not understanding the reference to the control on
the form.
--
Dave Hargis, Microsoft Access MVP


dfeigen115 said:
Dave (and or Doug),

I'm still working to get the kinks out, so I've simplified my dlookup to
include just 1 criteria. The statement in the datasource for the control on
the report reads:

=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin]
& ")")

where: AFPercentage and CLIN are fields in the table
CostPerformanceAwardFee. The field "caclin" is the name of the control in the
report that I'm trying to find the corresponding AFPercentage for. The
variant between the statement above and the sample you provided is I dropped
the "me." from the reference to "caclin" since it kept changes the reference
to from me.caclin to [me].[caclin] and wnating a value for "me". When I run
the report, the result is #error. Your thoughts?

Thanks, Dan

Klatuu said:
Had not noticed you use Doug.

I do really respect your knowledge of Access and your ability to cut through
the clutter. I have learned a lot from you and you keep me on my toes. I
always thing "If I screw this up, I will hear from Doug" :)
--
Dave Hargis, Microsoft Access MVP


:

<blush>

But why does everyone refuse to notice that it's Doug among friends? <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LOL
I have the same problem.
There is a frequent MVP poster named Douglas J. Steele whom I consider to
be
not only the most knowledgable person I know when it comes to Access, but
also has the best approach to most problems.
--
Dave Hargis, Microsoft Access MVP


:

Oops, no really meant you Dave (some one named Doug stepped into my
ioffice
as I started to compose the question). Multi-tasking will be the end of
me
soon enough....
Dan

:

Doug?
If you are confusing me with Douglas J. Steele, I am honored and he is
probably upset :)

You can use the same DLookup in the report. You would put it in the
control
source of a text box. Instead of referencing the control names on the
report
(val1, val2), you would reference the corresponding controls on the
report.
--
Dave Hargis, Microsoft Access MVP


:

Doug, a followup question. If I needed to do something similar during
the
generation of a report, woudl the dllokup work? In the report val1 &
val2
are populated, but would be used inm the dlookup against the same
table.
thanks,Dan

:

The DLookup should do it for you:

DLookup("[Field3]", "MyTable", "[Field1] = " & Me.Val1 & " And
[Field2] <
" & Me.Val2])

A couple of notes:
The syntax above assumes Field1 and Field2 are both numeric. If
they are
not, you will need to modify the code to include the delimiters for
other
data types.

DLookup will return Null if no match is found.
--
Dave Hargis, Microsoft Access MVP


:

I have a table with 3 fields (Field1, Field2, Field3). On a
form, there are
2 fields, Val1 and Val2. I need to be able to search the table,
returning
the value in Fields 3 where Val1 = Field1 and Val2 < Field2. The
table is
sorted by Field1 ascending and Field2 descending.

Thanks in advance, Dan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Sorry to ask so many questions, but I am a little confused.
Is the DLookup in the form or in the report?

--
Dave Hargis, Microsoft Access MVP


dfeigen115 said:
Dave,
the report is run adhoc and summarizes/groups data based on the field CACLIN
(data source is a query). The report (hopefully) will be able to lookup the
AFPercentage associated with the current grouping of CACLIN and display a
calculated value using the AFpercentage returned from the dlookup.
Dan

Klatuu said:
I don't rememer if you said earlier it was for the control source of a
control, but the reason the Me doesn't work in that context is that the
DLookup goes to Jet as an SQL statement. Jet will not understand Me, so you
have to drop it or fully qualify it with [Forms]![FormName]![ControlName].

As to running the report and getting the error, How are you opening the
report? It may be it is not understanding the reference to the control on
the form.
--
Dave Hargis, Microsoft Access MVP


dfeigen115 said:
Dave (and or Doug),

I'm still working to get the kinks out, so I've simplified my dlookup to
include just 1 criteria. The statement in the datasource for the control on
the report reads:

=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin]
& ")")

where: AFPercentage and CLIN are fields in the table
CostPerformanceAwardFee. The field "caclin" is the name of the control in the
report that I'm trying to find the corresponding AFPercentage for. The
variant between the statement above and the sample you provided is I dropped
the "me." from the reference to "caclin" since it kept changes the reference
to from me.caclin to [me].[caclin] and wnating a value for "me". When I run
the report, the result is #error. Your thoughts?

Thanks, Dan

:

Had not noticed you use Doug.

I do really respect your knowledge of Access and your ability to cut through
the clutter. I have learned a lot from you and you keep me on my toes. I
always thing "If I screw this up, I will hear from Doug" :)
--
Dave Hargis, Microsoft Access MVP


:

<blush>

But why does everyone refuse to notice that it's Doug among friends? <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LOL
I have the same problem.
There is a frequent MVP poster named Douglas J. Steele whom I consider to
be
not only the most knowledgable person I know when it comes to Access, but
also has the best approach to most problems.
--
Dave Hargis, Microsoft Access MVP


:

Oops, no really meant you Dave (some one named Doug stepped into my
ioffice
as I started to compose the question). Multi-tasking will be the end of
me
soon enough....
Dan

:

Doug?
If you are confusing me with Douglas J. Steele, I am honored and he is
probably upset :)

You can use the same DLookup in the report. You would put it in the
control
source of a text box. Instead of referencing the control names on the
report
(val1, val2), you would reference the corresponding controls on the
report.
--
Dave Hargis, Microsoft Access MVP


:

Doug, a followup question. If I needed to do something similar during
the
generation of a report, woudl the dllokup work? In the report val1 &
val2
are populated, but would be used inm the dlookup against the same
table.
thanks,Dan

:

The DLookup should do it for you:

DLookup("[Field3]", "MyTable", "[Field1] = " & Me.Val1 & " And
[Field2] <
" & Me.Val2])

A couple of notes:
The syntax above assumes Field1 and Field2 are both numeric. If
they are
not, you will need to modify the code to include the delimiters for
other
data types.

DLookup will return Null if no match is found.
--
Dave Hargis, Microsoft Access MVP


:

I have a table with 3 fields (Field1, Field2, Field3). On a
form, there are
2 fields, Val1 and Val2. I need to be able to search the table,
returning
the value in Fields 3 where Val1 = Field1 and Val2 < Field2. The
table is
sorted by Field1 ascending and Field2 descending.

Thanks in advance, Dan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

No problem, questiosn are good - especially if they lead to answers.... For
this particular dlookup, it is in the report.

Klatuu said:
Sorry to ask so many questions, but I am a little confused.
Is the DLookup in the form or in the report?

--
Dave Hargis, Microsoft Access MVP


dfeigen115 said:
Dave,
the report is run adhoc and summarizes/groups data based on the field CACLIN
(data source is a query). The report (hopefully) will be able to lookup the
AFPercentage associated with the current grouping of CACLIN and display a
calculated value using the AFpercentage returned from the dlookup.
Dan

Klatuu said:
I don't rememer if you said earlier it was for the control source of a
control, but the reason the Me doesn't work in that context is that the
DLookup goes to Jet as an SQL statement. Jet will not understand Me, so you
have to drop it or fully qualify it with [Forms]![FormName]![ControlName].

As to running the report and getting the error, How are you opening the
report? It may be it is not understanding the reference to the control on
the form.
--
Dave Hargis, Microsoft Access MVP


:

Dave (and or Doug),

I'm still working to get the kinks out, so I've simplified my dlookup to
include just 1 criteria. The statement in the datasource for the control on
the report reads:

=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin]
& ")")

where: AFPercentage and CLIN are fields in the table
CostPerformanceAwardFee. The field "caclin" is the name of the control in the
report that I'm trying to find the corresponding AFPercentage for. The
variant between the statement above and the sample you provided is I dropped
the "me." from the reference to "caclin" since it kept changes the reference
to from me.caclin to [me].[caclin] and wnating a value for "me". When I run
the report, the result is #error. Your thoughts?

Thanks, Dan

:

Had not noticed you use Doug.

I do really respect your knowledge of Access and your ability to cut through
the clutter. I have learned a lot from you and you keep me on my toes. I
always thing "If I screw this up, I will hear from Doug" :)
--
Dave Hargis, Microsoft Access MVP


:

<blush>

But why does everyone refuse to notice that it's Doug among friends? <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LOL
I have the same problem.
There is a frequent MVP poster named Douglas J. Steele whom I consider to
be
not only the most knowledgable person I know when it comes to Access, but
also has the best approach to most problems.
--
Dave Hargis, Microsoft Access MVP


:

Oops, no really meant you Dave (some one named Doug stepped into my
ioffice
as I started to compose the question). Multi-tasking will be the end of
me
soon enough....
Dan

:

Doug?
If you are confusing me with Douglas J. Steele, I am honored and he is
probably upset :)

You can use the same DLookup in the report. You would put it in the
control
source of a text box. Instead of referencing the control names on the
report
(val1, val2), you would reference the corresponding controls on the
report.
--
Dave Hargis, Microsoft Access MVP


:

Doug, a followup question. If I needed to do something similar during
the
generation of a report, woudl the dllokup work? In the report val1 &
val2
are populated, but would be used inm the dlookup against the same
table.
thanks,Dan

:

The DLookup should do it for you:

DLookup("[Field3]", "MyTable", "[Field1] = " & Me.Val1 & " And
[Field2] <
" & Me.Val2])

A couple of notes:
The syntax above assumes Field1 and Field2 are both numeric. If
they are
not, you will need to modify the code to include the delimiters for
other
data types.

DLookup will return Null if no match is found.
--
Dave Hargis, Microsoft Access MVP


:

I have a table with 3 fields (Field1, Field2, Field3). On a
form, there are
2 fields, Val1 and Val2. I need to be able to search the table,
returning
the value in Fields 3 where Val1 = Field1 and Val2 < Field2. The
table is
sorted by Field1 ascending and Field2 descending.

Thanks in advance, Dan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

This is the last version I saw. I see a syntax error
=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin]
& ")")

Should be
=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin]
& ")

Now to clearify:
[AFPercentage] and [CLIN] should be fields in the table/query
CostPerformanceAwardFee.
[calcin] should be the name of a control in your report.
The syntax assumes [CLIN] is a numeric field.
The code should be in the control source of a control on the form.
Is this all correct?


--
Dave Hargis, Microsoft Access MVP


dfeigen115 said:
No problem, questiosn are good - especially if they lead to answers.... For
this particular dlookup, it is in the report.

Klatuu said:
Sorry to ask so many questions, but I am a little confused.
Is the DLookup in the form or in the report?

--
Dave Hargis, Microsoft Access MVP


dfeigen115 said:
Dave,
the report is run adhoc and summarizes/groups data based on the field CACLIN
(data source is a query). The report (hopefully) will be able to lookup the
AFPercentage associated with the current grouping of CACLIN and display a
calculated value using the AFpercentage returned from the dlookup.
Dan

:

I don't rememer if you said earlier it was for the control source of a
control, but the reason the Me doesn't work in that context is that the
DLookup goes to Jet as an SQL statement. Jet will not understand Me, so you
have to drop it or fully qualify it with [Forms]![FormName]![ControlName].

As to running the report and getting the error, How are you opening the
report? It may be it is not understanding the reference to the control on
the form.
--
Dave Hargis, Microsoft Access MVP


:

Dave (and or Doug),

I'm still working to get the kinks out, so I've simplified my dlookup to
include just 1 criteria. The statement in the datasource for the control on
the report reads:

=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin]
& ")")

where: AFPercentage and CLIN are fields in the table
CostPerformanceAwardFee. The field "caclin" is the name of the control in the
report that I'm trying to find the corresponding AFPercentage for. The
variant between the statement above and the sample you provided is I dropped
the "me." from the reference to "caclin" since it kept changes the reference
to from me.caclin to [me].[caclin] and wnating a value for "me". When I run
the report, the result is #error. Your thoughts?

Thanks, Dan

:

Had not noticed you use Doug.

I do really respect your knowledge of Access and your ability to cut through
the clutter. I have learned a lot from you and you keep me on my toes. I
always thing "If I screw this up, I will hear from Doug" :)
--
Dave Hargis, Microsoft Access MVP


:

<blush>

But why does everyone refuse to notice that it's Doug among friends? <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LOL
I have the same problem.
There is a frequent MVP poster named Douglas J. Steele whom I consider to
be
not only the most knowledgable person I know when it comes to Access, but
also has the best approach to most problems.
--
Dave Hargis, Microsoft Access MVP


:

Oops, no really meant you Dave (some one named Doug stepped into my
ioffice
as I started to compose the question). Multi-tasking will be the end of
me
soon enough....
Dan

:

Doug?
If you are confusing me with Douglas J. Steele, I am honored and he is
probably upset :)

You can use the same DLookup in the report. You would put it in the
control
source of a text box. Instead of referencing the control names on the
report
(val1, val2), you would reference the corresponding controls on the
report.
--
Dave Hargis, Microsoft Access MVP


:

Doug, a followup question. If I needed to do something similar during
the
generation of a report, woudl the dllokup work? In the report val1 &
val2
are populated, but would be used inm the dlookup against the same
table.
thanks,Dan

:

The DLookup should do it for you:

DLookup("[Field3]", "MyTable", "[Field1] = " & Me.Val1 & " And
[Field2] <
" & Me.Val2])

A couple of notes:
The syntax above assumes Field1 and Field2 are both numeric. If
they are
not, you will need to modify the code to include the delimiters for
other
data types.

DLookup will return Null if no match is found.
--
Dave Hargis, Microsoft Access MVP


:

I have a table with 3 fields (Field1, Field2, Field3). On a
form, there are
2 fields, Val1 and Val2. I need to be able to search the table,
returning
the value in Fields 3 where Val1 = Field1 and Val2 < Field2. The
table is
sorted by Field1 ascending and Field2 descending.

Thanks in advance, Dan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Dave, thought I'd posted a reply - but looks like I hit the wrong key. As to
the syntax erro, when I try the "should be" version it says I'm missing a
closing double quote ("). When I dadd the (") at the end, it says I'm missing
a closing paren, when I add the paren, I get the same #%$#%$#% error (no
reflection on you). As to the clarifications you asked the answer is YES to
all (I've also double checked the spelling for typos and everything looks
good.

This one is making me feel like I'm glad it's TGIF.
Dan

Klatuu said:
This is the last version I saw. I see a syntax error
=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin]
& ")")

Should be
=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin]
& ")

Now to clearify:
[AFPercentage] and [CLIN] should be fields in the table/query
CostPerformanceAwardFee.
[calcin] should be the name of a control in your report.
The syntax assumes [CLIN] is a numeric field.
The code should be in the control source of a control on the form.
Is this all correct?


--
Dave Hargis, Microsoft Access MVP


dfeigen115 said:
No problem, questiosn are good - especially if they lead to answers.... For
this particular dlookup, it is in the report.

Klatuu said:
Sorry to ask so many questions, but I am a little confused.
Is the DLookup in the form or in the report?

--
Dave Hargis, Microsoft Access MVP


:

Dave,
the report is run adhoc and summarizes/groups data based on the field CACLIN
(data source is a query). The report (hopefully) will be able to lookup the
AFPercentage associated with the current grouping of CACLIN and display a
calculated value using the AFpercentage returned from the dlookup.
Dan

:

I don't rememer if you said earlier it was for the control source of a
control, but the reason the Me doesn't work in that context is that the
DLookup goes to Jet as an SQL statement. Jet will not understand Me, so you
have to drop it or fully qualify it with [Forms]![FormName]![ControlName].

As to running the report and getting the error, How are you opening the
report? It may be it is not understanding the reference to the control on
the form.
--
Dave Hargis, Microsoft Access MVP


:

Dave (and or Doug),

I'm still working to get the kinks out, so I've simplified my dlookup to
include just 1 criteria. The statement in the datasource for the control on
the report reads:

=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin]
& ")")

where: AFPercentage and CLIN are fields in the table
CostPerformanceAwardFee. The field "caclin" is the name of the control in the
report that I'm trying to find the corresponding AFPercentage for. The
variant between the statement above and the sample you provided is I dropped
the "me." from the reference to "caclin" since it kept changes the reference
to from me.caclin to [me].[caclin] and wnating a value for "me". When I run
the report, the result is #error. Your thoughts?

Thanks, Dan

:

Had not noticed you use Doug.

I do really respect your knowledge of Access and your ability to cut through
the clutter. I have learned a lot from you and you keep me on my toes. I
always thing "If I screw this up, I will hear from Doug" :)
--
Dave Hargis, Microsoft Access MVP


:

<blush>

But why does everyone refuse to notice that it's Doug among friends? <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LOL
I have the same problem.
There is a frequent MVP poster named Douglas J. Steele whom I consider to
be
not only the most knowledgable person I know when it comes to Access, but
also has the best approach to most problems.
--
Dave Hargis, Microsoft Access MVP


:

Oops, no really meant you Dave (some one named Doug stepped into my
ioffice
as I started to compose the question). Multi-tasking will be the end of
me
soon enough....
Dan

:

Doug?
If you are confusing me with Douglas J. Steele, I am honored and he is
probably upset :)

You can use the same DLookup in the report. You would put it in the
control
source of a text box. Instead of referencing the control names on the
report
(val1, val2), you would reference the corresponding controls on the
report.
--
Dave Hargis, Microsoft Access MVP


:

Doug, a followup question. If I needed to do something similar during
the
generation of a report, woudl the dllokup work? In the report val1 &
val2
are populated, but would be used inm the dlookup against the same
table.
thanks,Dan

:

The DLookup should do it for you:

DLookup("[Field3]", "MyTable", "[Field1] = " & Me.Val1 & " And
[Field2] <
" & Me.Val2])

A couple of notes:
The syntax above assumes Field1 and Field2 are both numeric. If
they are
not, you will need to modify the code to include the delimiters for
other
data types.

DLookup will return Null if no match is found.
--
Dave Hargis, Microsoft Access MVP


:

I have a table with 3 fields (Field1, Field2, Field3). On a
form, there are
2 fields, Val1 and Val2. I need to be able to search the table,
returning
the value in Fields 3 where Val1 = Field1 and Val2 < Field2. The
table is
sorted by Field1 ascending and Field2 descending.

Thanks in advance, Dan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Boy did I bugger that one up.
=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin] )

Sorry about that.

Dave Hargis, Microsoft Access MVP


dfeigen115 said:
Dave, thought I'd posted a reply - but looks like I hit the wrong key. As to
the syntax erro, when I try the "should be" version it says I'm missing a
closing double quote ("). When I dadd the (") at the end, it says I'm missing
a closing paren, when I add the paren, I get the same #%$#%$#% error (no
reflection on you). As to the clarifications you asked the answer is YES to
all (I've also double checked the spelling for typos and everything looks
good.

This one is making me feel like I'm glad it's TGIF.
Dan

Klatuu said:
This is the last version I saw. I see a syntax error
=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin]
& ")")

Should be
=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin]
& ")

Now to clearify:
[AFPercentage] and [CLIN] should be fields in the table/query
CostPerformanceAwardFee.
[calcin] should be the name of a control in your report.
The syntax assumes [CLIN] is a numeric field.
The code should be in the control source of a control on the form.
Is this all correct?


--
Dave Hargis, Microsoft Access MVP


dfeigen115 said:
No problem, questiosn are good - especially if they lead to answers.... For
this particular dlookup, it is in the report.

:

Sorry to ask so many questions, but I am a little confused.
Is the DLookup in the form or in the report?

--
Dave Hargis, Microsoft Access MVP


:

Dave,
the report is run adhoc and summarizes/groups data based on the field CACLIN
(data source is a query). The report (hopefully) will be able to lookup the
AFPercentage associated with the current grouping of CACLIN and display a
calculated value using the AFpercentage returned from the dlookup.
Dan

:

I don't rememer if you said earlier it was for the control source of a
control, but the reason the Me doesn't work in that context is that the
DLookup goes to Jet as an SQL statement. Jet will not understand Me, so you
have to drop it or fully qualify it with [Forms]![FormName]![ControlName].

As to running the report and getting the error, How are you opening the
report? It may be it is not understanding the reference to the control on
the form.
--
Dave Hargis, Microsoft Access MVP


:

Dave (and or Doug),

I'm still working to get the kinks out, so I've simplified my dlookup to
include just 1 criteria. The statement in the datasource for the control on
the report reads:

=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin]
& ")")

where: AFPercentage and CLIN are fields in the table
CostPerformanceAwardFee. The field "caclin" is the name of the control in the
report that I'm trying to find the corresponding AFPercentage for. The
variant between the statement above and the sample you provided is I dropped
the "me." from the reference to "caclin" since it kept changes the reference
to from me.caclin to [me].[caclin] and wnating a value for "me". When I run
the report, the result is #error. Your thoughts?

Thanks, Dan

:

Had not noticed you use Doug.

I do really respect your knowledge of Access and your ability to cut through
the clutter. I have learned a lot from you and you keep me on my toes. I
always thing "If I screw this up, I will hear from Doug" :)
--
Dave Hargis, Microsoft Access MVP


:

<blush>

But why does everyone refuse to notice that it's Doug among friends? <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LOL
I have the same problem.
There is a frequent MVP poster named Douglas J. Steele whom I consider to
be
not only the most knowledgable person I know when it comes to Access, but
also has the best approach to most problems.
--
Dave Hargis, Microsoft Access MVP


:

Oops, no really meant you Dave (some one named Doug stepped into my
ioffice
as I started to compose the question). Multi-tasking will be the end of
me
soon enough....
Dan

:

Doug?
If you are confusing me with Douglas J. Steele, I am honored and he is
probably upset :)

You can use the same DLookup in the report. You would put it in the
control
source of a text box. Instead of referencing the control names on the
report
(val1, val2), you would reference the corresponding controls on the
report.
--
Dave Hargis, Microsoft Access MVP


:

Doug, a followup question. If I needed to do something similar during
the
generation of a report, woudl the dllokup work? In the report val1 &
val2
are populated, but would be used inm the dlookup against the same
table.
thanks,Dan

:

The DLookup should do it for you:

DLookup("[Field3]", "MyTable", "[Field1] = " & Me.Val1 & " And
[Field2] <
" & Me.Val2])

A couple of notes:
The syntax above assumes Field1 and Field2 are both numeric. If
they are
not, you will need to modify the code to include the delimiters for
other
data types.

DLookup will return Null if no match is found.
--
Dave Hargis, Microsoft Access MVP


:

I have a table with 3 fields (Field1, Field2, Field3). On a
form, there are
2 fields, Val1 and Val2. I need to be able to search the table,
returning
the value in Fields 3 where Val1 = Field1 and Val2 < Field2. The
table is
sorted by Field1 ascending and Field2 descending.

Thanks in advance, Dan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

It WORKS. I should be able to add the 2nd set of criteria without problems
(I hope). Thanks for bearing with me...
Dan

Klatuu said:
Boy did I bugger that one up.
=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin] )

Sorry about that.

Dave Hargis, Microsoft Access MVP


dfeigen115 said:
Dave, thought I'd posted a reply - but looks like I hit the wrong key. As to
the syntax erro, when I try the "should be" version it says I'm missing a
closing double quote ("). When I dadd the (") at the end, it says I'm missing
a closing paren, when I add the paren, I get the same #%$#%$#% error (no
reflection on you). As to the clarifications you asked the answer is YES to
all (I've also double checked the spelling for typos and everything looks
good.

This one is making me feel like I'm glad it's TGIF.
Dan

Klatuu said:
This is the last version I saw. I see a syntax error
=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin]
& ")")

Should be
=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin]
& ")

Now to clearify:
[AFPercentage] and [CLIN] should be fields in the table/query
CostPerformanceAwardFee.
[calcin] should be the name of a control in your report.
The syntax assumes [CLIN] is a numeric field.
The code should be in the control source of a control on the form.
Is this all correct?


--
Dave Hargis, Microsoft Access MVP


:

No problem, questiosn are good - especially if they lead to answers.... For
this particular dlookup, it is in the report.

:

Sorry to ask so many questions, but I am a little confused.
Is the DLookup in the form or in the report?

--
Dave Hargis, Microsoft Access MVP


:

Dave,
the report is run adhoc and summarizes/groups data based on the field CACLIN
(data source is a query). The report (hopefully) will be able to lookup the
AFPercentage associated with the current grouping of CACLIN and display a
calculated value using the AFpercentage returned from the dlookup.
Dan

:

I don't rememer if you said earlier it was for the control source of a
control, but the reason the Me doesn't work in that context is that the
DLookup goes to Jet as an SQL statement. Jet will not understand Me, so you
have to drop it or fully qualify it with [Forms]![FormName]![ControlName].

As to running the report and getting the error, How are you opening the
report? It may be it is not understanding the reference to the control on
the form.
--
Dave Hargis, Microsoft Access MVP


:

Dave (and or Doug),

I'm still working to get the kinks out, so I've simplified my dlookup to
include just 1 criteria. The statement in the datasource for the control on
the report reads:

=DLookUp("[AFPercentage]","CostPerformanceAwardFee", "[CLIN] = " & [caclin]
& ")")

where: AFPercentage and CLIN are fields in the table
CostPerformanceAwardFee. The field "caclin" is the name of the control in the
report that I'm trying to find the corresponding AFPercentage for. The
variant between the statement above and the sample you provided is I dropped
the "me." from the reference to "caclin" since it kept changes the reference
to from me.caclin to [me].[caclin] and wnating a value for "me". When I run
the report, the result is #error. Your thoughts?

Thanks, Dan

:

Had not noticed you use Doug.

I do really respect your knowledge of Access and your ability to cut through
the clutter. I have learned a lot from you and you keep me on my toes. I
always thing "If I screw this up, I will hear from Doug" :)
--
Dave Hargis, Microsoft Access MVP


:

<blush>

But why does everyone refuse to notice that it's Doug among friends? <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


LOL
I have the same problem.
There is a frequent MVP poster named Douglas J. Steele whom I consider to
be
not only the most knowledgable person I know when it comes to Access, but
also has the best approach to most problems.
--
Dave Hargis, Microsoft Access MVP


:

Oops, no really meant you Dave (some one named Doug stepped into my
ioffice
as I started to compose the question). Multi-tasking will be the end of
me
soon enough....
Dan

:

Doug?
If you are confusing me with Douglas J. Steele, I am honored and he is
probably upset :)

You can use the same DLookup in the report. You would put it in the
control
source of a text box. Instead of referencing the control names on the
report
(val1, val2), you would reference the corresponding controls on the
report.
--
Dave Hargis, Microsoft Access MVP


:

Doug, a followup question. If I needed to do something similar during
the
generation of a report, woudl the dllokup work? In the report val1 &
val2
are populated, but would be used inm the dlookup against the same
table.
thanks,Dan

:

The DLookup should do it for you:

DLookup("[Field3]", "MyTable", "[Field1] = " & Me.Val1 & " And
[Field2] <
" & Me.Val2])

A couple of notes:
The syntax above assumes Field1 and Field2 are both numeric. If
they are
not, you will need to modify the code to include the delimiters for
other
data types.

DLookup will return Null if no match is found.
--
Dave Hargis, Microsoft Access MVP


:

I have a table with 3 fields (Field1, Field2, Field3). On a
form, there are
2 fields, Val1 and Val2. I need to be able to search the table,
returning
the value in Fields 3 where Val1 = Field1 and Val2 < Field2. The
table is
sorted by Field1 ascending and Field2 descending.

Thanks in advance, Dan
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

new table from existing table 1
Customized display order for fields 1
Update Query question 2
flip this query 3
RE: Insert Into with Where Clause 10
Concatenate data from Imported Excel Spreadsheet 1
Select Query, from Form fields that are not null 1
Update Set Values 3

Top