Append Query Syntax error

[RandomNeeds]

Hi,

I am trying to create an append query and one of my field needs is quite complex.

When I run this, I get prompted to enter the parameter for NEWKEYSHIP. What I expect should happen, since I am running this on an order that is using that Key Order Type, that the output should include the value from "New key Number" in the "SW Key" Field.

It is not understanding that I am trying to interrogate the KeyOrderType field value.


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],''))))


The latter part of they query works exactly as advertised. 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],''))))

My goal is to add in the extra parameter of checking the KeyOrderType.

 

[vbaInet]

At quick glance, remove the double quotes from ["NEWKEYSHIP"]
And in the future you can use code tags to make code more readable. Here's how:
http://www.access-programmers.co.uk/forums/showthread.php?t=200247

 

[vbaInet]

And sort out the other square brackets. It should be in this form:

[TableName]![FieldName]

 

[RandomNeeds]

Thanks, I removed the quotes and it still prompted- any other ideas? I will also take a look at the other link, too.

 

[RandomNeeds]

I probably stated this wrong. I am not actually getting a syntax error- the query is letting me enter the way I have it. But when I run the VB script that calls the append query, it prompts me NEWKEYSHIP as a missing parameter. The exact message is: Enter Parameter Value: NEWKEYSHIP and it gives me a space to type. If I type any character, the rest of the code progresses and the output file is created. It just leaves the targeted SW Key field blank.

 

[vbaInet]

You must have missed my last post.

 

[RandomNeeds]

[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]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],''))))[/FONT][/SIZE]

 

[vbaInet]

Still not quite got it. Please see the link again.

 

[GinaWhipp]

Try this...

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],''))))

Side note: You can also use Code Tags by selecting the Advanced button then highlightling your code and clicking the # button on the menu bar.

If that doesn't work then please post the entire query because I see two different tables but have no idea how they are joined.

 

[vbaInet]

Try this...

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],''))))

Side note: You can also use Code Tags by selecting the Advanced button then highlightling your code and clicking the # button on the menu bar.

If that doesn't work then please post the entire query because I see two different tables but have no idea how they are joined.

I must be losing the plot Gina. Why I thought "NEWKEYSHIP" was a field is beyond me. Goes to show it's been too long I touched this stuff

 

[RandomNeeds]

You are very helpful. I am going to try your suggestion and post the code. That last edit didn't quite do the trick. It is still prompting for the parameter: NewKeyShip. This is inherited code. I am trying to add this one parameter to it- to check the value of the Key order type before completing the rest. The entire rest of this code does exactly what it is supposed to do, so even though the syntax may not be perfect, I am not skilled enough to want to change what is already working.

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;

 

[vbaInet]

This one calls for a function instead. The nested IIF() are becoming complex.

Do you know how to call a function from within a query?

 

[RandomNeeds]

I don't think so, but I could try

 

[GinaWhipp]

vbaInet...

I had to take it and put in some line breaks before I could see it.

 

[GinaWhipp]

How is this entered in the Table... NEWKEYSHIP or NEW KEY SHIP or some other way?

 

[RandomNeeds]

Yes, it is listed as NewKeyShip.

 

[vbaInet]

1. Hold Alt + F11 - this will take you to the VBA IDE
2. Just below the File menu click the icon that reads "Insert Module" or do it from the Insert menu followed by 'Module'. Not 'Class Module', just Module.
3. Paste the following code where the cursor is blinking:

Public Function GetSwKey(varKeyOrderType As Variant, _
                         varNewKeyNumber As Variant, _
                         varPartNumber As Variant, _
                         varAlohaKey As Variant) As Variant

    If varKeyOrderType = "NEWKEYSHIP" Then
        GetSwKey = varNewKeyNumber
    ElseIf varPartNumber Like "H40*" Then
        If varAlohaKey > 0 And IsNull(varNewKeyNumber) = True Then
            GetSwKey = varAlohaKey
        ElseIf varkeynumber > 0 Then
            GetSwKey = varNewKeyNumber
        End If
    End If
End Function

4. In your query, copy and paste the following line as a field:

Sw Key: GetSwKey([tbl_OrderForm]![KeyOrderType], [tbl_OrderForm]![NewKeyNumber], [tbl_OrderFormDetailsNew]![PartNumber], [tbl_OrderForm]![AlohaKey])

 

[GinaWhipp]

Try vbaInet did, it's cleaner and should do the trick!

 

[RandomNeeds]

This worked!!! I now need to add one more element to the new module for the last variable, which is ReplaceShip. I think I would do that by adding this to the module:

If varKeyOrderType = "ReplaceSHIP" Then
GetSwKey = varNewKeyNumber

 

[vbaInet]

Good to hear.

At which point of the IF tree do you want that condition added? I think you understand the logic behind the IIF() function, it's pretty much the same as what I've done. Go ahead and play around with it and see how you get on.

We're always here to point you in the right direction if you need us.