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-SetupLabor]+[C4-LaborFixedOverhead])*1)+[A1-Material]+[A2-Scrap]+[D1-OutsideOperation]+[F1-Freight]+[X1-PurchasesToAcct4256]) AS TotalCost INTO ItemSimulatedCost FROM qxtabCost;

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,[IEXSCR]) AS xIEXSCR, PRODDTA_F4101.IMUPMJ, PRODDTA_F4101.IMUSER
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)="07") AND ((PRODDTA_F4105.COMCU)="       31101"));

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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-SetupLabor]+[C4-LaborFixedOverhead])*1)+[A1-Material]+[A2-Scrap]+[D1-OutsideOperation]+[F1-Freight]+[X1-PurchasesToAcct4256]) AS TotalCost INTO ItemSimulatedCost
FROM qxtabCost;

Output:ItemSimulatedCost

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Help Apprciated.

tw
Tom WinslowAsked:
Who is Participating?
 
als315Connect With a Mentor Commented:
Query:
SELECT Q_Cost1.COLITM AS [Item Number], Q_Cost1.IMDSC1 AS Description, Sum([xIEXSCR]/10000) AS [Total Of IEXSCR], Sum(IIf([IECOST]="A1",[xIEXSCR]/10000,0)) AS A1, Sum(IIf([IECOST]="A2",[xIEXSCR]/10000,0)) AS A2, Sum(IIf([IECOST]="B1",[xIEXSCR]/10000,0)) AS B1, Sum(IIf([IECOST]="B2",[xIEXSCR]/10000,0)) AS B2, Sum(IIf([IECOST]="C4",[xIEXSCR]/10000,0)) AS C4, Sum(IIf([IECOST]="F1",[xIEXSCR]/10000,0)) AS F1
FROM Q_Cost1
GROUP BY Q_Cost1.COLITM, Q_Cost1.IMDSC1;

Open in new window

working fine with your example. You should add columns for all possible values (now there are: A1, A2, B1, B2, C4, F1)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>However, items do not necessarily contain every cost component.
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.
0
 
als315Commented:
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
0
 
Tom WinslowAuthor Commented:
As requested, I have uploaded sample data. See attached.
SampleData.xls
0
 
Tom WinslowAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.