SLQ IIF Problem

[Camper1]

OS = Microsoft Windows 2000 Professional
I'm querying a mainframe AS400 database to download to an Excel 2002 spreadsheet. The following works fine until I add the IIf statement. Error message in Excel VBA simply says syntax error, however when I run it in Access 2002 (Service Pack 2) gives the error:
[IBM]Client Access Express ODBC Driver(32Bit)[DB2/400 SQL]SQL0199
keyword BETWEEN not expected Valid tokens,.(#199) Substituting any other operator =,< gets an error SQL0104 Token = was not Valid. Any Suggestions?

vQuery = &quot;SELECT PSPSP100.PARNT, PSPSP100.CSEQN, PSPSP100.CHILD, MSPMP100.PRDNO, MSPMP100.DESCP FROM ------------- WHERE MSPMP100.PRDNO = PSPSP100.CHILD AND IIf(PSPSP100.PSPCL Between 'AA' And 'YM',MSPMP100.S2APH='C10301A',MSPMP100.S2APH Like '*') ORDER BY PSPSP100.PARNT, PSPSP100.CSEQN&quot;

 

[Steve101]

(a) VBA does not recognise the BETWEEN operator. Replace its use with >= and <= operators.

(b) The 'Then' part of the IIF function should return a value; Your expression: MSPMP100.S2APH='C10301A' will probably just return a value of false.

(c) Similarly, the else part of the function will simply evaluate to True.

Its unclear exactly what you're attempting to achieve here. I' ve also seen this thread appearing now a number of times. If you're not solving the problem, then its possible that you're going down the wrong track and/or not taking on board the advice that you're receiving.

Suggest you try re-wording the problem next time, so that we can understand what it is you're trying to achieve. Simply re-posting the same thread over and over again is not likely to solve it.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Camper1]

I am trying to capture a multi level Bill of Material.
The following is an example.

Assembly JMW9527DAW Obsolete N Low Level Cd 0 Class YD Type M
U/M EA WO 27 COMBO CONVECT Rev Date 2/13/04 Rev Lvl 0 Sts P
A T
Level C Component Description y Cls UM Qty Req'd Sts
1 8106P111-60 TAG, CARTON B 520 EA 1.0000 P
1 8118P722-60 WIRE LIST, WO UHF/PRO/C P 520 EA 1.0000 P
1 5708A134-60 ASSY, HIDDEN BAKE 27&quot; W P 715 EA 1.0000 P
.2 3818F108-51 CHANNEL, BOTTOM AIR ? M 600 EA 1.0000 P
..3 1451R245-51 .028X19.222X20.386 GALV B 590 EA 1.0000 P
.2 2624F078-51 PANEL, BOTTOM 27&quot; WO? M 602 EA 1.0000 P
..3 1455R024-51 .036X22.021X25.309 GALV B 590 EA 1.0000 P
.2 7101P426-60 200758 SCR 8AB-18X.37 W B 310 EA 6.0000 P
.2 8038P035-60 FOOT, PLASTIC JA WO B 300 EA 4.0000 P

The model JMW9527DAW is of class YD, the field is PSPSP100.PSPCL
All models are tied to a Project Number, in this case
MSPMP100.S2APH='C10301A'. What I'm trying to do is capture all models and all parts in all levels tied to this Project Number with one query.
The model is a Parent (PSPSP100.PARNT) and all parts are the children.(PSPSP100.CHILD)
All top level parts are level 1. Any sub assy's are level 2 etc. A part can be a child to the model, but also be a parent of another part. If the part was new to the project it is also coded C10301A, however if it was an existing part it could be any other Project Number hence the MSPMP100.S2APH Like '*'. I have been successful getting all models with all level 1 parts, but to get the lower levels requires multiple queries. There can be nine levels of parts
Perhaps IIF is not the way to go, I tried a case statement but there is no way to do the Like *
Does this make any more sense?

 

[Steve101]

Camper1, thanks for the explanation, though I'm just not understanding the parent-child linkage mechanism that you're using, so find it difficult to comment along this line.

My technique for Bill of Materials would basically involve two tables:

(a) The Parts master table, where the parts are defined, irrespective of whether they are Finished items, Sub Assemblies or Raw materials (these types would be represented using a PartType field in each record), and

(b) A PartsRelationship table, which identifies the relationships between the Parts; eg. Assuming a car has a Chassis, 4 wheels, an Engine, and a Body, and the Engine has a Carburetor, EngineBlock and ..., and the Body has Door panels and Other Panels, then:
[tt]
------------
Parts Master
------------
Part# Part Description
0 Car
1 Chassis
2 Wheel
3 Body
4 Engine
5 EngineBlock
6 Carburetor
7 Door Panels
8 Other Panels

------------------
PartsRelationships
------------------
Parent Child Quantity
0 1 1
0 4 1
0 2 4
0 3 1
4 5 1
4 6 1
3 7 4
3 8 3

With this simple structure, you're clearly and simply defining the parts linkages.

From this point, you have two options to generate the bill of materials:

(a) Programmatically, using recursive programming to 'climb the tree' - fairly complex, and usually the domain of computer science students, but powerful and flexible, and easy once you understand.

(b) By using a series of outer join based queries, to link the different levels. Probably your better short term course of action.

Tell me if this helps, and if so, we can look closer.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Camper1]

Thanks, I believe your on the right track with the outer joins. Unfortunatly I can't change the structure of the databse, but it is essentially like your example. I tried several joins with your data but have not been able to do what I want.
How but an example SQL statement, I think I can apply that to my data.
I'm looking for output similar to:
1 Car
2 Chassis
2 Engine
3 EngineBlock
3 Carburator
2 Wheel
2 Body
3 Door Panels
3 Other Panels
Appreciate the help.