The Microsoft Jet database engine does not recognize '[A2]' as a field name or valid expression."
I am working on a modification to an existing Access Query that has worked well in the past but is now experiencing some issues due to changes made by our accounting dept within our JD Edwards database.
The cost of an item can be made up of numerous components such as material, labor, overhead, these components are represented in J.D. Edwards with a code such as A1, A2, P1, P2, D1, X1, etc. However, items do not necessarily contain every cost component. The code needs to be modified so that when the cost for an item is being calculated it will skip over a missing component instead off crashing. Here is an example of part of the code as it exists now. But, if this item [A2] does not exist, all want to continue on to [B1]. But, now it stops and crashes.
Example: IIf(IsNull([a1]),0,[A1]) AS [A1-Material], IIf(IsNull([A2]),0,[A2]) AS [A2-Scrap], IIf(IsNull([b1]),0,[B1]) AS [B1-DirectLabor],etc.
If an item does NOT have a ‘A2’ cost item, my code crashes and gives me the following error message.
'The Microsoft Jet database engine does not recognize '[A2]' as a field name or valid expression.'
Now, that is because A2 might not exist as a cost type for a particular item. It will exist for some but not all items.So, I want to ignore IIf(IsNull([A2]),0,[A2]) AS [A2-Scrap] and continue on to the IIf(IsNull([b1]),0,[B1]) AS [B1-DirectLabor].
Below is the complete Access Query SQL. Text file is attached.
~~~~~~~~~~~~~~~~~~~~~~~~~~
qryCostSimulated
Input Query: qxtabCost
SELECT qxtabCost.[Item Number], qxtabCost.Description, IIf(IsNull([a1]),0,[A1]) AS [A1-Material], IIf(IsNull([A2]),0,[A2]) AS [A2-Scrap], IIf(IsNull([b1]),0,[B1]) AS [B1-DirectLabor], IIf(IsNull([B2]),0,[B2]) AS [B2-SetupLabor], IIf(IsNull([C4]),0,[C4]) AS [C4-LaborFixedOverhead], IIf(IsNull([D1]),0,[D1]) AS [D1-OutsideOperation], IIf(IsNull([f1]),0,[F1]) AS [F1-Freight], IIf(IsNull([X1]),0,[X1]) AS [X1-PurchasesToAcct4256], ((([B1-DirectLabor]+[B2-Se
Output To Table: ItemSimulatedCost
~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional information: I run three (3) Access Queries in a Access Macro. The code for all these queries is below in case it helps for reference.
Q_Cost1
SELECT PRODDTA_F4105.COLITM, PRODDTA_F4101.IMDSC1, PRODDTA_F30026.IECOST, IIf(IsNull([IEXSCR]),0,[IE
FROM (PRODDTA_F4105 LEFT JOIN PRODDTA_F30026 ON PRODDTA_F4105.COLITM = PRODDTA_F30026.IELITM) LEFT JOIN PRODDTA_F4101 ON PRODDTA_F4105.COLITM = PRODDTA_F4101.IMLITM
WHERE (((PRODDTA_F4105.COLEDG)="
~~~~~~~~~~~~~~~~~~~~~~~~~~
qxtabCost
TRANSFORM Sum([xIEXSCR]/10000) AS SumOfIIEXSCR
SELECT Q_Cost1.COLITM AS [Item Number], Q_Cost1.IMDSC1 AS Description, Sum([xIEXSCR]/10000) AS [Total Of IEXSCR]
FROM Q_Cost1
GROUP BY Q_Cost1.COLITM, Q_Cost1.IMDSC1
PIVOT Q_Cost1.IECOST;
~~~~~~~~~~~~~~~~~~~~~~~~~~
qryCostSimulated
Input: qxtabCost
SELECT qxtabCost.[Item Number], qxtabCost.Description, IIf(IsNull([a1]),0,[A1]) AS [A1-Material], IIf(IsNull([A2]),0,[A2]) AS [A2-Scrap], IIf(IsNull([b1]),0,[B1]) AS [B1-DirectLabor], IIf(IsNull([B2]),0,[B2]) AS [B2-SetupLabor], IIf(IsNull([C4]),0,[C4]) AS [C4-LaborFixedOverhead], IIf(IsNull([D1]),0,[D1]) AS [D1-OutsideOperation], IIf(IsNull([f1]),0,[F1]) AS [F1-Freight], IIf(IsNull([X1]),0,[X1]) AS [X1-PurchasesToAcct4256], ((([B1-DirectLabor]+[B2-Se
FROM qxtabCost;
Output:ItemSimulatedCost
~~~~~~~~~~~~~~~~~~~~~~~~~~
Help Apprciated.
tw
The cost of an item can be made up of numerous components such as material, labor, overhead, these components are represented in J.D. Edwards with a code such as A1, A2, P1, P2, D1, X1, etc. However, items do not necessarily contain every cost component. The code needs to be modified so that when the cost for an item is being calculated it will skip over a missing component instead off crashing. Here is an example of part of the code as it exists now. But, if this item [A2] does not exist, all want to continue on to [B1]. But, now it stops and crashes.
Example: IIf(IsNull([a1]),0,[A1]) AS [A1-Material], IIf(IsNull([A2]),0,[A2]) AS [A2-Scrap], IIf(IsNull([b1]),0,[B1]) AS [B1-DirectLabor],etc.
If an item does NOT have a ‘A2’ cost item, my code crashes and gives me the following error message.
'The Microsoft Jet database engine does not recognize '[A2]' as a field name or valid expression.'
Now, that is because A2 might not exist as a cost type for a particular item. It will exist for some but not all items.So, I want to ignore IIf(IsNull([A2]),0,[A2]) AS [A2-Scrap] and continue on to the IIf(IsNull([b1]),0,[B1]) AS [B1-DirectLabor].
Below is the complete Access Query SQL. Text file is attached.
~~~~~~~~~~~~~~~~~~~~~~~~~~
qryCostSimulated
Input Query: qxtabCost
SELECT qxtabCost.[Item Number], qxtabCost.Description, IIf(IsNull([a1]),0,[A1]) AS [A1-Material], IIf(IsNull([A2]),0,[A2]) AS [A2-Scrap], IIf(IsNull([b1]),0,[B1]) AS [B1-DirectLabor], IIf(IsNull([B2]),0,[B2]) AS [B2-SetupLabor], IIf(IsNull([C4]),0,[C4]) AS [C4-LaborFixedOverhead], IIf(IsNull([D1]),0,[D1]) AS [D1-OutsideOperation], IIf(IsNull([f1]),0,[F1]) AS [F1-Freight], IIf(IsNull([X1]),0,[X1]) AS [X1-PurchasesToAcct4256], ((([B1-DirectLabor]+[B2-Se
Output To Table: ItemSimulatedCost
~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional information: I run three (3) Access Queries in a Access Macro. The code for all these queries is below in case it helps for reference.
Q_Cost1
SELECT PRODDTA_F4105.COLITM, PRODDTA_F4101.IMDSC1, PRODDTA_F30026.IECOST, IIf(IsNull([IEXSCR]),0,[IE
FROM (PRODDTA_F4105 LEFT JOIN PRODDTA_F30026 ON PRODDTA_F4105.COLITM = PRODDTA_F30026.IELITM) LEFT JOIN PRODDTA_F4101 ON PRODDTA_F4105.COLITM = PRODDTA_F4101.IMLITM
WHERE (((PRODDTA_F4105.COLEDG)="
~~~~~~~~~~~~~~~~~~~~~~~~~~
qxtabCost
TRANSFORM Sum([xIEXSCR]/10000) AS SumOfIIEXSCR
SELECT Q_Cost1.COLITM AS [Item Number], Q_Cost1.IMDSC1 AS Description, Sum([xIEXSCR]/10000) AS [Total Of IEXSCR]
FROM Q_Cost1
GROUP BY Q_Cost1.COLITM, Q_Cost1.IMDSC1
PIVOT Q_Cost1.IECOST;
~~~~~~~~~~~~~~~~~~~~~~~~~~
qryCostSimulated
Input: qxtabCost
SELECT qxtabCost.[Item Number], qxtabCost.Description, IIf(IsNull([a1]),0,[A1]) AS [A1-Material], IIf(IsNull([A2]),0,[A2]) AS [A2-Scrap], IIf(IsNull([b1]),0,[B1]) AS [B1-DirectLabor], IIf(IsNull([B2]),0,[B2]) AS [B2-SetupLabor], IIf(IsNull([C4]),0,[C4]) AS [C4-LaborFixedOverhead], IIf(IsNull([D1]),0,[D1]) AS [D1-OutsideOperation], IIf(IsNull([f1]),0,[F1]) AS [F1-Freight], IIf(IsNull([X1]),0,[X1]) AS [X1-PurchasesToAcct4256], ((([B1-DirectLabor]+[B2-Se
FROM qxtabCost;
Output:ItemSimulatedCost
~~~~~~~~~~~~~~~~~~~~~~~~~~
Help Apprciated.
tw
Are you working with linked J.D. Edwards tables? Can you show some sample data? In such cases Transform query is not the best solution. You can get same results with group query.
Look at example. Query1 - your transform query, Query2 - simple group query. If in table you will have only Item with ID 2, you will not have column A2 in your query, but you will have it in Query2.
DB27833203.accdb
Look at example. Query1 - your transform query, Query2 - simple group query. If in table you will have only Item with ID 2, you will not have column A2 in your query, but you will have it in Query2.
DB27833203.accdb
ASKER
As requested, I have uploaded sample data. See attached.
SampleData.xls
SampleData.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.
Yeah, Access cannot handle situational columns in that manner, and also with queries whose data source is a crosstab that has to have data to create those columns.
Your only solution might be to do various INSERTS with the key and these conditional columns, which would guarantee that rows with those columns get inserted without error. Then, do some kind of aggregate query to Sum() all your data so that it's somewhat flat.