If Then Syntax problem

[RandomNeeds]

I am working on a query and need some help with the syntax. currently I am not getting an error, but I am getting a parameter undefined pop-up when I try to run the query. The parameter it doesn't understand is really a field value that I am trying to interrogate.

NewKeyShip is one of 5 choices for the Key Order Type field. I am trying to have the system input a field value in SW Key when NewKeyShip is the selected value. I should mention that the rest of the code works perfectly starting with "If Order Form Details, part Number is like "H40." and through the end. It is only the first part that is not working well.

[/FONT]
[FONT=Calibri][SIZE=3]Sw key: IIf([tbl_OrderForm].[KeyOrderType]=[NEWKEYSHIP],[tbl_OrderForm].[NewKeyNumber],IIf([tbl_OrderFormDetailsNew].[PartNumber] Like "H40*",IIf([tbl_OrderForm].[AlohaKey]>0 And [tbl_OrderForm].[NewKeyNumber] Is Null,[tbl_OrderForm].[AlohaKey],IIf([tbl_OrderForm].[NewKeyNumber]>0,[tbl_OrderForm].[NewKeyNumber],''))))[/SIZE][/FONT]

 

[vbaInet]

Isn't this a follow-up to your other thread?

http://www.access-programmers.co.uk/forums/showthread.php?t=266375

Like I already mentioned, you need to put code in code tags. The link I sent you clearly shows how to do it. You put the first "

" part but you didn't close it. It's easier for us to read code if they formatted properly using that code.

 

[vbaInet]

And show us the full SQL statement as well (enclosed in those tags).

 

[RandomNeeds]

Are you saying that if I put this:

[SIZE=3][FONT=Calibri]Sw key: IIf([tbl_OrderForm.KeyOrderType] like “NEWKEYSHIP”, [tbl_OrderForm.NewKeyNumber], IIf([tbl_OrderForm.KeyOrderType] like “REPLACESHIP”, [tbl_OrderForm.NewKeyNumber, IIf ([tbl_OrderFormDetailsNew.PartNumber] Like "H40*",IIf([tbl_OrderForm.AlohaKey]>0 And [tbl_OrderForm.NewKeyNumber] Is Null,[tbl_OrderForm.AlohaKey],IIf([tbl_OrderForm.NewKeyNumber]>0,[tbl_OrderForm.NewKeyNumber],''))))[/FONT][/SIZE]
[SIZE=3][FONT=Calibri][\code][/FONT][/SIZE]

[FONT=Calibri][SIZE=3]that you would have a better answer for me? I get it that my question wasn't formatted exactly right, but how does that help with this problem? [/SIZE][/FONT]

[FONT=Calibri][SIZE=3]THis is the entire SQL statement, but I am only having problems with that one area that I am trying to edit. [/SIZE][/FONT]

[FONT=Calibri][SIZE=3][code][/SIZE][/FONT]
INSERT INTO [Order Export Table] ( intfc_id, intfc_line_num, TRANS_TYPE_BI, TRANS_TYPE_BI_SEQ, BUSINESS_UNIT, SOLD_TO_CUST_ID, SOLD_TO_ADDR_NUM, BILL_TO_CUST_ID, ADDRESS_SEQ_NUM, SHIP_TO_CUST_ID, SHIP_TO_ADDR_NUM, DELIVER_TO_CUST_ID, BILL_TYPE_ID, PO_REF, PO_DATE, REFERENCE_ORDER_NUMBER, LINE_SEQ_NUM, BUNDLE_NAME, IDENTIFIER, DESCR, QTY, BI_CURRENCY_CD, UNIT_AMT, ANNUAL_NET_PRICE, SW_KEY, [ENTITLE_START_DATE(MM/DD/YY)], [ENTITLE_END_DATE(MM/DD/YY)], FROM_DT, TO_DT, NOTES_SEQ_NUM, NOTE_TYPE, INTERNAL_FLAG, HARDWARE_ROLE, TEXT254, BILL_SOURCE_ID, BILLING_FREQUENCY, BILL_CYCLE_ID, PYMNT_TERMS_CD, ORDER_GRP, BILL_BY_ID, VANTATIVE_CASE_ID, RMA_ID, REQUESTED_ARRIVAL_DATE )
SELECT Add1ToINTFCID([tbl_OrderForm.OrderID]) AS intfc_id, '' AS intfc_line_num, tbl_OrderForm.TransType AS trans_type_bi, '' AS trans_type_bi_seq, '' AS business_unit, tbl_Account.AccountPSID AS sold_to_Cust_ID, '' AS sold_to_addr_num, tbl_OrderForm.BillToID AS bill_to_cust_id, '' AS address_seq_num, IIf([tbl_OrderForm.AltShipTo]=True,tbl_OrderForm.AltID,tbl_OrderForm.SitePSID) AS ship_to_cust_id, '' AS ship_to_addr_num, tbl_OrderForm.SitePSID AS deliver_to_cust_ID, '' AS bill_type_id, tbl_OrderForm.ClientPO AS po_ref, tbl_OrderForm.PO_DATE AS PO_DATE, '' AS [Reference Order Number], '' AS line_seq_num, IIf([tbl_OrderFormDetailsNew.bundle]=True,[tbl_OrderFormDetailsNew.PartNumber],'') AS bundle_name, IIf([tbl_OrderFormDetailsNew.bundle]=False,[tbl_OrderFormDetailsNew.PartNumber],'') AS identifier, '' AS descr, tbl_OrderFormDetailsNew.QuantOrTermCount AS qty, '' AS bi_currency_cd, tbl_OrderFormDetailsNew.Price AS unit_amt, IIf([tbl_OrderFormDetailsNew.Type]='HWM',[tbl_OrderFormDetailsNew.OrderHWMPrice],IIf(tbl_OrderFormDetailsNew.Type='SWM',[tbl_OrderFormDetailsNew.OrderMaintenancePPY],0)) AS ANNUAL_NET_PRICE, IIf([tbl_OrderForm].[KeyOrderType]=[NEWKEYSHIP],[tbl_OrderForm].[NewKeyNumber],IIf([tbl_OrderFormDetailsNew].[PartNumber] Like "H40*",IIf([tbl_OrderForm].[AlohaKey]>0 And [tbl_OrderForm].[NewKeyNumber] Is Null,[tbl_OrderForm].[AlohaKey],IIf([tbl_OrderForm].[NewKeyNumber]>0,[tbl_OrderForm].[NewKeyNumber],'')))) AS [Sw key], IIf([tbl_OrderFormDetailsNew.Type]='HWM',[tbl_OrderFormDetailsNew.OrderHWMStartDate],IIf([tbl_OrderFormDetailsNew.Type]='SWM',[tbl_OrderFormDetailsNew.OrderMaintenanceStartDt],[tbl_OrderFormDetailsNew.OrderMaintenanceStartDt])) AS [ENTITLE_START_DATE(MM/DD/YY)], tbl_OrderFormDetailsNew.OrderMaintenanceExpDate, '' AS from_dt, '' AS to_dt, '' AS notes_seq_num, tbl_OrderFormDetailsNew.NoteType, '' AS internal_flag, '' AS HARDWARE_ROLE, tbl_OrderFormDetailsNew.Notes, '' AS bill_source_id, '' AS billing_frequency, '' AS bill_cycle_id, '' AS pymnt_terms_cd, "" AS ORDER_GRP, '' AS bill_by_id, '' AS Vantive_Case_ID, '' AS [RMA ID], tbl_OrderForm.DueDate AS [Requested Arrival Date]
FROM (tbl_Account INNER JOIN tbl_OrderForm ON tbl_Account.AccountID = tbl_OrderForm.AccountID) INNER JOIN (tbl_OrderFormDetailsNew LEFT JOIN tbl_OrderFormDetailsNewNotes ON tbl_OrderFormDetailsNew.OrderFormDetailsNewID = tbl_OrderFormDetailsNewNotes.OrderDetailsNewID) ON tbl_OrderForm.OrderID = tbl_OrderFormDetailsNew.OrderID
WHERE (((tbl_OrderForm.FinalizeOrder)=Yes) AND ((tbl_OrderForm.stagetoken)=4))
ORDER BY tbl_OrderForm.OrderID, tbl_OrderFormDetailsNew.OrderFormDetailsNewID, tbl_OrderFormDetailsNewNotes.OrderDetailsNotesID, tbl_OrderForm.ClientPO;

[\code}

 

[vbaInet]

The tag preserves the formatting of code for legibility. If we can't read the code how can we help you? Now looking at the chunk of SQL statement you've just pasted there are multiple open and close parentheses, without formatted code we can't tell which line uses which opening and which closing parentheses. All technical forums adopt this format.

The last code tag is a backslash (/), not forward slash (\).

Here's an example of a formatted SQL statement:

SELECT *
FROM SomeTable
WHERE FIELDNAME = "Sometext";

 

[vbaInet]

Simply edit your post, re-copy from Access and past it here. That way we can see the code clearly. If you don't re-copy it won't work.