If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DLookup problem
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 |
#2
|
|||
|
|||
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 -- "CindyK" wrote in message ... 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 |
#3
|
|||
|
|||
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. "Duane Hookom" wrote: 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 -- "CindyK" wrote in message ... 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 |
#4
|
|||
|
|||
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. "CindyK" wrote in message ... 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. "Duane Hookom" wrote: 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 -- "CindyK" wrote in message ... 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 |
#5
|
|||
|
|||
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. "CindyK" wrote: 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. "Duane Hookom" wrote: 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 -- "CindyK" wrote in message ... 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 |
#6
|
|||
|
|||
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? "CindyK" wrote in message ... 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. "CindyK" wrote: 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. "Duane Hookom" wrote: 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 -- "CindyK" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Row Autofit problem Excel 2003 | Matthias Klaey | General Discussion | 0 | January 19th, 2005 05:33 PM |
Office XP Update Problem | Bill | General Discussions | 0 | December 1st, 2004 06:11 PM |
Problem with display of header in Word 2003 | Padser | Page Layout | 0 | October 8th, 2004 07:47 AM |
DLookup Problem | Ed the Redhead | Running & Setting Up Queries | 3 | August 16th, 2004 08:47 PM |
Big Problem. We need the fix | Captain K | Calendar | 0 | July 8th, 2004 04:50 PM |