[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

The Microsoft Jet database engine does not recognize '[A2]' as a field name or valid expression."

Posted on 2012-08-17
5
Medium Priority
?
703 Views
Last Modified: 2012-08-26
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
0
Comment
Question by:Tom Winslow
  • 2
  • 2
5 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38306334
>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
 
LVL 40

Expert Comment

by:als315
ID: 38307371
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
 

Author Comment

by:Tom Winslow
ID: 38311337
As requested, I have uploaded sample data. See attached.
SampleData.xls
0
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 38311651
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
 

Author Closing Comment

by:Tom Winslow
ID: 38334982
Thanks.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question