none
qyery invvalid argument to the function RRS feed

  • Question

  • i have this union query

    SELECT "AL" AS pakhuis, AL_Orders.[ID], AL_Orders.[Title], AL_Orders.[Compliance Asset Id], AL_Orders.[Ordernr], AL_Orders.[destid], AL_Orders.[orderdate], AL_Orders.[_OldID], AL_Orders.[Content Type], AL_Orders.[App Created By], AL_Orders.[App Modified By], AL_Orders.[Workflow Instance ID], AL_Orders.[File Type], AL_Orders.[Modified], AL_Orders.[Created], AL_Orders.[Created By], AL_Orders.[Modified By], AL_Orders.[URL Path], AL_Orders.[Path], AL_Orders.[Item Type], AL_Orders.[Encoded Absolute URL],Al_Orders.[tekst]

    FROM AL_Orders

    UNION SELECT "OF" AS pakhuis, OF_Orders.[ID], OF_Orders.[Title], OF_Orders.[Compliance Asset Id], OF_Orders.[Ordernr], OF_Orders.[destid], OF_Orders.[orderdate], OF_Orders.[_OldID], OF_Orders.[Content Type], OF_Orders.[App Created By], OF_Orders.[App Modified By], OF_Orders.[Workflow Instance ID], OF_Orders.[File Type], OF_Orders.[Modified], OF_Orders.[Created], OF_Orders.[Created By], OF_Orders.[Modified By], OF_Orders.[URL Path], OF_Orders.[Path], OF_Orders.[Item Type], OF_Orders.[Encoded Absolute URL],OF_Orders.[tekst]

    FROM OF_Orders;

    and on this query another selection query

    SELECT proformadet.pakhuis, Orders.Ordernr, proformadet.oms, proformadet.Maat, proformadet.aantalkiwi, proformadet.afmetingenkist, proformadet.kgkarton, proformadet.kartonpalet, proformadet.produkt, proformadet.maatid, proformadet.proformaid, proformadet.proformanr, Sum(proformadet.palletsontruck) AS SumOfpalletsontruck, Sum(proformadet.cartonsontruck) AS SumOfcartonsontruck
    FROM proformadet LEFT JOIN Orders ON (proformadet.Orderid = Orders.ID) AND (proformadet.pakhuis = Orders.pakhuis)
    GROUP BY proformadet.pakhuis, Orders.Ordernr, proformadet.oms, proformadet.Maat, proformadet.aantalkiwi, proformadet.afmetingenkist, proformadet.kgkarton, proformadet.kartonpalet, proformadet.produkt, proformadet.maatid, proformadet.proformaid, proformadet.proformanr
    HAVING (((proformadet.pakhuis)=[Forms]![proformaform]![pakhuis]))
    ORDER BY proformadet.pakhuis;

    this always worked perfect.

    now i want to change the union query by omitting 1 file into 

    SELECT "AL" AS pakhuis, AL_Orders.[ID], AL_Orders.[Title], AL_Orders.[Compliance Asset Id], AL_Orders.[Ordernr], AL_Orders.[destid], AL_Orders.[orderdate], AL_Orders.[_OldID], AL_Orders.[Content Type], AL_Orders.[App Created By], AL_Orders.[App Modified By], AL_Orders.[Workflow Instance ID], AL_Orders.[File Type], AL_Orders.[Modified], AL_Orders.[Created], AL_Orders.[Created By], AL_Orders.[Modified By], AL_Orders.[URL Path], AL_Orders.[Path], AL_Orders.[Item Type], AL_Orders.[Encoded Absolute URL],Al_Orders.[tekst]

    FROM AL_Orders

    and then i get the error 

    "invalid argument to function"

    who can explain this to me please ?

    Saturday, September 21, 2019 3:39 AM

All replies

  • the error comes in the selection query
    Saturday, September 21, 2019 3:40 AM
  • SELECT "AL" AS pakhuis, AL_Orders.[ID], AL_Orders.[Title], AL_Orders.[Compliance Asset Id], AL_Orders.[Ordernr], AL_Orders.[destid], AL_Orders.[orderdate], AL_Orders.[_OldID], AL_Orders.[Content Type], AL_Orders.[App Created By], AL_Orders.[App Modified By], AL_Orders.[Workflow Instance ID], AL_Orders.[File Type], AL_Orders.[Modified], AL_Orders.[Created], AL_Orders.[Created By], AL_Orders.[Modified By], AL_Orders.[URL Path], AL_Orders.[Path], AL_Orders.[Item Type], AL_Orders.[Encoded Absolute URL],Al_Orders.[tekst]

    FROM AL_Orders

    Hi tekoko,

    What an amount of tekst, hardly to work through.

    Finally I discovered something: AI_Orders.[tekst], is this "al" of "ai"?

    But another question. The tables AL_Orders and OF_Orders are (almost?) the same. Why not collect them in one table  Orders, with an additional field to indicate whether the Order is "AL" or "OR". It makes things much simpler.

    Imb.

    Saturday, September 21, 2019 8:06 AM
  • Thanks for the reply, 
    • it was lowercase L, i changed it , but it  didn't solve the error
    • the problem is that the union query combined two sharepointlists. it is psooble in the future that i hav to go back to a union query, so i thought i keep it that way and just omit 1 file . i can not understand why i get this error all of a sudden
    Saturday, September 21, 2019 9:01 AM
  • Hello Imb-hb,

    probably it has something to do with the sharepoint lists , i totally created the selection query again and now the form works ,very strange ....

    Saturday, September 21, 2019 9:18 AM