DLookup problem

[Guest]

I have a query that I am using the Dlookup function to find values from
another table. The criteria of the Dlookup is based on a field in the
external table (dlookup) and one of the tables in the query. I get an error
that the query cannot find the field in the table that is included in the
query

DLookup("[oldfileback]","PMCut",[oldfileback]=[PDAtable]![Fileback])

Please help. I also tried referencing the table name for "oldfileback" and
no success.

Thanks

Cindy

 

[Duane Hookom]

The last argument must be a valid string.
DLookup("[oldfileback]","PMCut","[oldfileback]=" & [PDAtable]![Fileback])
This makes no sense because the value you return should be the same value as
[FileBack].

You need to provide more information.

 

[Guest]

Here is my goal:

I have a query that contains two tables - PDATable and PDAProgram. These
tables are linked by PDANum. I need to lookup a value (field oldfileback) in
an external table (table PMCut). This external table has many values and I
need the value of "oldfileback" to be equal to the "filecutamt" field in
PDATable.

The last argument must be a valid string.
DLookup("[oldfileback]","PMCut","[oldfileback]=" & [PDAtable]![Fileback])
This makes no sense because the value you return should be the same value as
[FileBack].

You need to provide more information.

--
Duane Hookom
MS Access MVP
--

I have a query that I am using the Dlookup function to find values from
another table. The criteria of the Dlookup is based on a field in the
external table (dlookup) and one of the tables in the query. I get an
error
that the query cannot find the field in the table that is included in the
query

DLookup("[oldfileback]","PMCut",[oldfileback]=[PDAtable]![Fileback])

Please help. I also tried referencing the table name for "oldfileback"
and
no success.

Thanks

Cindy

 

[JohnFol]

I agree with Duane's comments, however I think you expect the Where
Condition to look into a table for the value of FileBack. It doesn't, you
need to supply it, and as Duane says, if you return oldfileback where
oldfileback = a known value, you may as well use the known value.

Can I suggest that if this is on a form, you consider the following?

DLookup("[oldfileback]","PMCut","[PDANum]=" & [PDANumFieldOnForm])

If this is in a query, just bring in the PMCut and PDATable tables in and
link on PDANum.

Here is my goal:

I have a query that contains two tables - PDATable and PDAProgram. These
tables are linked by PDANum. I need to lookup a value (field oldfileback)
in
an external table (table PMCut). This external table has many values and
I
need the value of "oldfileback" to be equal to the "filecutamt" field in
PDATable.

The last argument must be a valid string.
DLookup("[oldfileback]","PMCut","[oldfileback]=" & [PDAtable]![Fileback])
This makes no sense because the value you return should be the same value
as
[FileBack].

You need to provide more information.

--
Duane Hookom
MS Access MVP
--

I have a query that I am using the Dlookup function to find values from
another table. The criteria of the Dlookup is based on a field in the
external table (dlookup) and one of the tables in the query. I get an
error
that the query cannot find the field in the table that is included in
the
query

DLookup("[oldfileback]","PMCut",[oldfileback]=[PDAtable]![Fileback])

Please help. I also tried referencing the table name for "oldfileback"
and
no success.

Thanks

Cindy

 

[Guest]

The problem is the PMCut table does not contain PDANum. Let me explain
further.

We have a table of about 10,000 "PDAs" that contain a "fileback" field.
When a price increase happens, the fileback changes based on product and
zone. So, what I did was create a table (PMCut) that contains the product,
zone, oldfileback and newfileback. My thought then was to use this PMCut
table to look through the 10,000 PDAs and update the fileback based on the
value of the "oldfileback".

Any ideas how I can do this? In the past what I had to do is run a series
(many) of update queries and enter the old and new values to update. We have
14 zones, 15 products and about 50 price points.

Here is my goal:

I have a query that contains two tables - PDATable and PDAProgram. These
tables are linked by PDANum. I need to lookup a value (field oldfileback) in
an external table (table PMCut). This external table has many values and I
need the value of "oldfileback" to be equal to the "filecutamt" field in
PDATable.

The last argument must be a valid string.
DLookup("[oldfileback]","PMCut","[oldfileback]=" & [PDAtable]![Fileback])
This makes no sense because the value you return should be the same value as
[FileBack].

You need to provide more information.

--
Duane Hookom
MS Access MVP
--

I have a query that I am using the Dlookup function to find values from
another table. The criteria of the Dlookup is based on a field in the
external table (dlookup) and one of the tables in the query. I get an
error
that the query cannot find the field in the table that is included in the
query

DLookup("[oldfileback]","PMCut",[oldfileback]=[PDAtable]![Fileback])

Please help. I also tried referencing the table name for "oldfileback"
and
no success.

Thanks

Cindy

 

[JohnFol]

If there is a relationship between the PMCut table and the data you are
trying to update, why not use it in the query, link on the appropriate
fields and get rid of the DLookup?

The problem is the PMCut table does not contain PDANum. Let me explain
further.

We have a table of about 10,000 "PDAs" that contain a "fileback" field.
When a price increase happens, the fileback changes based on product and
zone. So, what I did was create a table (PMCut) that contains the
product,
zone, oldfileback and newfileback. My thought then was to use this PMCut
table to look through the 10,000 PDAs and update the fileback based on the
value of the "oldfileback".

Any ideas how I can do this? In the past what I had to do is run a series
(many) of update queries and enter the old and new values to update. We
have
14 zones, 15 products and about 50 price points.

Here is my goal:

I have a query that contains two tables - PDATable and PDAProgram. These
tables are linked by PDANum. I need to lookup a value (field
oldfileback) in
an external table (table PMCut). This external table has many values and
I
need the value of "oldfileback" to be equal to the "filecutamt" field in
PDATable.

The last argument must be a valid string.
DLookup("[oldfileback]","PMCut","[oldfileback]=" &
[PDAtable]![Fileback])
This makes no sense because the value you return should be the same
value as
[FileBack].

You need to provide more information.

--
Duane Hookom
MS Access MVP
--

I have a query that I am using the Dlookup function to find values
from
another table. The criteria of the Dlookup is based on a field in
the
external table (dlookup) and one of the tables in the query. I get
an
error
that the query cannot find the field in the table that is included in
the
query

DLookup("[oldfileback]","PMCut",[oldfileback]=[PDAtable]![Fileback])

Please help. I also tried referencing the table name for
"oldfileback"
and
no success.

Thanks

Cindy