I am using an IIF to say if the STORER is an Internal Storer (signified by [STYPE] = 1) then to look at the qryIntCusts and if not look at WMS_W3_NAME. The syntax looks correct but it keeps telling me it is wrong. Here is the Full SQL that I have:

SELECT Outgoing_Orders_Table.REF, Outgoing_Orders_Table.appt_ID, wms_pko_hd.storer, tblstatus.status, wms_pko_hd.SHIPPED_BY, wms_pko_hd.STYPE
FROM WMS_W3_NAME, qryIntCusts, (Outgoing_Orders_Table LEFT JOIN wms_pko_hd ON Outgoing_Orders_Table.ref = wms_pko_hd.ref) LEFT JOIN tblstatus ON wms_pko_hd.flag = tblstatus.flag IIF([STYPE]=1,LEFT JOIN qryIntCusts ON wms_pko_hd.SHIPTO = qryIntCusts.CODE,LEFT JOIN WMS_W3_NAME ON wms_pko_hd.SHIPTO = WMS_W3_NAME.CODE);

And for a Quick look here is the IIF by itself:

IIF([STYPE]=1,LEFT JOIN qryIntCusts ON wms_pko_hd.SHIPTO = qryIntCusts.CODE,LEFT JOIN WMS_W3_NAME ON wms_pko_hd.SHIPTO = WMS_W3_NAME.CODE

You cannot use IIF in a join construction.... period...

Left join both tables then IIF the display result, not the join itself.

Ok I did both LEFT JOINS, now how do I IIF the display result?

SELECT Outgoing_Orders_Table.REF, Outgoing_Orders_Table.appt_ID, wms_pko_hd.storer, tblstatus.status, wms_pko_hd.SHIPPED_BY, wms_pko_hd.STYPE
FROM (((Outgoing_Orders_Table LEFT JOIN wms_pko_hd ON Outgoing_Orders_Table.ref = wms_pko_hd.ref) LEFT JOIN tblstatus ON wms_pko_hd.flag = tblstatus.flag) LEFT JOIN WMS_W3_NAME ON wms_pko_hd.SHIPTO = WMS_W3_NAME.CODE) LEFT JOIN qryIntCusts ON wms_pko_hd.SHIPTO = qryIntCusts.CUST_NAME;

You mean like this?

SELECT Outgoing_Orders_Table.REF, Outgoing_Orders_Table.appt_ID, wms_pko_hd.storer, tblstatus.status, wms_pko_hd.SHIPPED_BY, wms_pko_hd.STYPE, IIf([STYPE]=1,[qryIntCusts].[CUST_NAME],[WMS_W3_NAME].[NDESC]) AS CUST
FROM (((Outgoing_Orders_Table LEFT JOIN wms_pko_hd ON Outgoing_Orders_Table.ref = wms_pko_hd.ref) LEFT JOIN tblstatus ON wms_pko_hd.flag = tblstatus.flag) LEFT JOIN WMS_W3_NAME ON wms_pko_hd.SHIPTO = WMS_W3_NAME.CODE) LEFT JOIN qryIntCusts ON wms_pko_hd.SHIPTO = qryIntCusts.CODE;

This is what I have now and it doesn't give me any errors but it isn't working correctly. Even when [STYPE] = 1 it still is looking in the WMS_W3_NAME table.

SELECT Outgoing_Orders_Table.REF, Outgoing_Orders_Table.appt_ID, wms_pko_hd.storer, tblstatus.status, wms_pko_hd.SHIPPED_BY, wms_pko_hd.STYPE, IIf([STYPE]=1,[qryIntCusts].[CUST_NAME],[WMS_W3_NAME].[NDESC]) AS CUST
FROM (((Outgoing_Orders_Table LEFT JOIN wms_pko_hd ON Outgoing_Orders_Table.ref = wms_pko_hd.ref) LEFT JOIN tblstatus ON wms_pko_hd.flag = tblstatus.flag) LEFT JOIN WMS_W3_NAME ON wms_pko_hd.SHIPTO = WMS_W3_NAME.CODE) LEFT JOIN qryIntCusts ON wms_pko_hd.SHIPTO = qryIntCusts.CODE;

This is what I have now and it doesn't give me any errors but it isn't working correctly. Even when [STYPE] = 1 it still is looking in the WMS_W3_NAME table.

SELECT Outgoing_Orders_Table.REF, Outgoing_Orders_Table.appt_ID, wms_pko_hd.storer, tblstatus.status, wms_pko_hd.SHIPPED_BY, wms_pko_hd.STYPE, IIf([STYPE]=1,[qryIntCusts].[CUST_NAME],[WMS_W3_NAME].[NDESC]) AS CUST
FROM (((Outgoing_Orders_Table LEFT JOIN wms_pko_hd ON Outgoing_Orders_Table.ref = wms_pko_hd.ref) LEFT JOIN tblstatus ON wms_pko_hd.flag = tblstatus.flag) LEFT JOIN WMS_W3_NAME ON wms_pko_hd.SHIPTO = WMS_W3_NAME.CODE) LEFT JOIN qryIntCusts ON wms_pko_hd.SHIPTO = qryIntCusts.CODE;

Actually, if I load the Query itself (not in the form) it shows the results as if it searched both tables but it shouldn't. It should only search one of the tables. Please help.

I'm having trouble understanding your problem, of course the tables are being searched, that should not matter, if STYPE=1 Cust will be the value from 1 table , if not then from the other.

Brian

Yes I agree that is what it is supposed to do but when I run the query it shows one line with the result from table 1 and one result from table 2.

Then you must have two records that satisfy the conditions, without seeing your database i don't see how we can know why.

Brian

without seeing your database i don't see how we can know why.

And here I thought you could just "see" everything Brian. :D I am so disillusioned now. ;)

Sorry about that Bob, but I think you need Six jelloes to qualify for that skill. :D

Brian

Sorry about that Bob, but I think you need Six jelloes to qualify for that skill. :D

Well, I'll educate you on that one. You need a WHOLE LOT more than 6 do do that. :eek:

This is what I have now and it doesn't give me any errors but it isn't working correctly. Even when [STYPE] = 1 it still is looking in the WMS_W3_NAME table.

[Re-written-readable-code]
SELECT Outgoing_Orders_Table.REF
, Outgoing_Orders_Table.appt_ID
, wms_pko_hd.storer
, tblstatus.status
, wms_pko_hd.SHIPPED_BY
, wms_pko_hd.STYPE
, IIf([STYPE]=1 ,[qryIntCusts].[CUST_NAME]
,[WMS_W3_NAME].[NDESC] ) AS CUST
FROM (((Outgoing_Orders_Table
LEFT JOIN wms_pko_hd ON Outgoing_Orders_Table.ref = wms_pko_hd.ref)
LEFT JOIN tblstatus ON wms_pko_hd.flag = tblstatus.flag )
LEFT JOIN WMS_W3_NAME ON wms_pko_hd.SHIPTO = WMS_W3_NAME.CODE)
LEFT JOIN qryIntCusts ON wms_pko_hd.SHIPTO = qryIntCusts.CODE;

Yes exactly like that but its broken accoording to you?? How is it broken?

It is broken because:

1) in the Table WMS_PKO_HD [REF] is the Primary key so there can be no duplicates
2) [STYPE] is set up as a one relationship. So each [REF] can only have one [STYPE]. [STYPE] can only be a 1 (Internal Storer) or a 2 (External Storer).
3) Some Carrier codes are duplicated between Internal Storers and External Storers therefore I need to tell the program which one it is looking at.
4) When I run my query it is showing me two lines for each order, one with a carrier from the Internal storers and one from the external storers.

How about doing a union query instead?

Select...
from ...
Where [STYPE]=1
Union
Select...
from ...
Where [STYPE]<>1

That could work as well :)

Thank you for your help. I just tried the UNION and I am having the same issue as before it shows me the results from both tables. Here is the UNION that I just created:

SELECT Outgoing_Orders_Table.REF, Outgoing_Orders_Table.appt_ID, wms_pko_hd.storer, tblstatus.status, wms_pko_hd.SHIPPED_BY, wms_pko_hd.STYPE, qryIntCusts.CUST_NAME
FROM (((Outgoing_Orders_Table LEFT JOIN wms_pko_hd ON Outgoing_Orders_Table.ref = wms_pko_hd.ref) LEFT JOIN tblstatus ON wms_pko_hd.flag = tblstatus.flag) LEFT JOIN WMS_W3_NAME ON wms_pko_hd.SHIPTO = WMS_W3_NAME.CODE) LEFT JOIN qryIntCusts ON wms_pko_hd.SHIPTO = qryIntCusts.CODE
WHERE [STYPE] = 1
UNION
SELECT Outgoing_Orders_Table.REF, Outgoing_Orders_Table.appt_ID, wms_pko_hd.storer, tblstatus.status, wms_pko_hd.SHIPPED_BY, wms_pko_hd.STYPE, WMS_W3_NAME.NDESC
FROM (((Outgoing_Orders_Table LEFT JOIN wms_pko_hd ON Outgoing_Orders_Table.ref = wms_pko_hd.ref) LEFT JOIN tblstatus ON wms_pko_hd.flag = tblstatus.flag) LEFT JOIN WMS_W3_NAME ON wms_pko_hd.SHIPTO = WMS_W3_NAME.CODE) LEFT JOIN qryIntCusts ON wms_pko_hd.SHIPTO = qryIntCusts.CODE
WHERE [STYPE] = 2;

I figured it out - My company is dumber then I thought!!!!!! They have two customers in the internal Customers table with the same number. Now I have to test them using the [STORER] instead of [STYPE]. Only thing is how do I test a string in SQL? Do I have to put quotes around the string? or what?

Well if your union goes bad, the only reason can be your data is bad, like you found out.

what do you mean "Only thing is how do I test a string in SQL?"

Something like:
where Name = "namliaM"

would search only for my name... is that what you mean?

I would rather not use the UNION because I found out in the past then I cannot update the data in a Form that has a UNION query attached. So I am using 1 IIF and a SWITCH. I would SWITCH to just a SWITCH if I can do something like SWITCH ELSE. Here is the SQL I am testing now but it says that "Join expression is not supported"

SELECT Outgoing_Orders_Table.REF, Outgoing_Orders_Table.appt_ID, wms_pko_hd.storer, tblstatus.status, wms_pko_hd.SHIPPED_BY, wms_pko_hd.STYPE, IIf([wms_pko_hd].[STYPE]=1,Switch([STORER]="ATLN",[(ATLN)WAVE3_ADD_REC].[NAME],[STORER]="DEME",[(DEMED)WAVE3_ADD_REC].[NAME]),[WMS_W3_NAME].[NDESC]) AS CUST
FROM ((((Outgoing_Orders_Table LEFT JOIN wms_pko_hd ON Outgoing_Orders_Table.ref = wms_pko_hd.ref) LEFT JOIN tblstatus ON wms_pko_hd.flag = tblstatus.flag) LEFT JOIN WMS_W3_NAME ON wms_pko_hd.SHIPTO = WMS_W3_NAME.CODE) INNER JOIN [(ATLN)WAVE3_ADD_REC] ON wms_pko_hd.SHIPTO = [(ATLN)WAVE3_ADD_REC].CODE) LEFT JOIN [(DEMED)WAVE3_ADD_REC] ON wms_pko_hd.SHIPTO = [(DEMED)WAVE3_ADD_REC].CODE;

Ok I am an idiot I have it working now. I just had to change the INNER JOINS to two LEFT JOINS. The last thing I want to do is see If I can remove the IIF and use just the SWITCH. Is that possible?

Your right, Unions are non-updateable :(

Atleast teh IIF field wont be editable either :(

I dont use switch a lot (kinda stuck with the old IIF structure.
Cannot you do a "and" in the switch? Something like
Switch([STORER]="ATLN" and [wms_pko_hd].[STYPE]=1,[(ATLN)WAVE3_ADD_REC].[NAME],
[STORER]="DEME" and [wms_pko_hd].[STYPE]=1,[(DEMED)WAVE3_ADD_REC].[NAME])
,[WMS_W3_NAME].[NDESC])

The IIF works so much better than the Union. When I had the Union in there I could not add new records in the form that was attached to a Query that was attached to a UNION Query. I removed the UNION and put in a IIF instead and now that form is update able. Thank you for all your help.