Results 1 to 7 of 7
  1. 05-08-2020, 06:00 AM #1
    diegomarino
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    434

    very long sql in vba module

    Hi,


    i'm trying to set the recordsource of a subform
    this is the code
    HTML Code:
    Me.[portafoglio trasferibile].form.recordsource= "SELECT Portafoglio.CandidatoID, Portafoglio.TrattativaID, Aziende.Azienda, Portafoglio.Cliente, Portafoglio.[Tipo di cliente], Portafoglio.Gestito, Portafoglio.Assicurativo, Portafoglio.[Gestioni patrimoniali], Portafoglio.Amministrato, Portafoglio.Certificati, Portafoglio.Liquidità, Portafoglio.[Anno inizio rapporto], Portafoglio.Fidelizzazione, Portafoglio.Impieghi, Portafoglio.Trasferibile, Switch([trasferibile]="Sicuro",1,[trasferibile]="probabile",2,[trasferibile]="possibile",3,[trasferibile]="improbabile",4,[trasferibile]="negativo",5) AS [Trasferibile numerico], Trattative.[Livello minimo trasferibile], Switch([livello minimo trasferibile]="Sicuro",1,[livello minimo trasferibile]="probabile",2,[livello minimo trasferibile]="possibile",3,[livello minimo trasferibile]="improbabile",4,[livello minimo trasferibile]="negativo",5) AS [livello minimo trasferibile numerico], IIf([livello minimo trasferibile numerico]>=[trasferibile numerico],True,False) AS [In linea], Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([liquidità]) AS Totale, Switch([totale]<100000,"<100k",[totale] Between 100000 And 299999,"100k-300k",[totale] Between 300000 And 499999,"300k-500k",[totale] Between 500000 And 699999,"500k-700k",[totale] Between 700000 And 1000000,"700k-1mln",[totale] Between 1000000 And 1999999,"1mln-2mln",[totale] Between 2000000 And 4999999,"2mln-5mln",[totale] Between 5000000 And 9999999,"5mln-10mln",[totale]>=10000000,">=10mln") AS [Taglio cliente]FROM (Aziende INNER JOIN Clienti ON Aziende.IDazienda = Clienti.AziendaID) INNER JOIN (Trattative INNER JOIN Portafoglio ON Trattative.IDtrattativa = Portafoglio.TrattativaID) ON Clienti.IDCliente = Trattative.ClienteID;"
    but i'm not able.
    I substitue the " with ' in the code to avoid the problem of text string qualifier, but it tells me "end of instruction needed" or something similar.

    i know is a very big piece of code, i copied from a working query

    if is not to complex i'll appreciate some help
    Reply With Quote Reply With Quote
  2. 05-08-2020, 06:43 AM #2
    kd2017
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,165
    I don't see where you've substituted double quotes for single quotes.

    Anyway, try this:
    Code:
    Me.[portafoglio trasferibile].form.recordsource = "SELECT Portafoglio.CandidatoID, Portafoglio.TrattativaID, Aziende.Azienda, Portafoglio.Cliente, Portafoglio.[Tipo di cliente], Portafoglio.Gestito, Portafoglio.Assicurativo, Portafoglio.[Gestioni patrimoniali], Portafoglio.Amministrato, Portafoglio.Certificati, Portafoglio.Liquidità, Portafoglio.[Anno inizio rapporto], Portafoglio.Fidelizzazione, Portafoglio.Impieghi, Portafoglio.Trasferibile, Switch([trasferibile]=""Sicuro"",1,[trasferibile]=""probabile"",2,[trasferibile]=""possibile"",3,[trasferibile]=""improbabile"",4,[trasferibile]=""negativo"",5) AS [Trasferibile numerico], Trattative.[Livello minimo trasferibile], Switch([livello minimo trasferibile]=""Sicuro"",1,[livello minimo trasferibile]=""probabile"",2,[livello minimo trasferibile]=""possibile"",3,[livello minimo trasferibile]=""improbabile"",4,[livello minimo trasferibile]=""negativo"",5) AS [livello minimo trasferibile numerico], IIf([livello minimo trasferibile numerico]>=[trasferibile numerico],True,False) AS [In linea], Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([liquidità]) AS Totale, Switch([totale]<100000,""<100k"",[totale] Between 100000 And 299999,""100k-300k"",[totale] Between 300000 And 499999,""300k-500k"",[totale] Between 500000 And 699999,""500k-700k"",[totale] Between 700000 And 1000000,""700k-1mln"",[totale] Between 1000000 And 1999999,""1mln-2mln"",[totale] Between 2000000 And 4999999,""2mln-5mln"",[totale] Between 5000000 And 9999999,""5mln-10mln"",[totale]>=10000000,"">=10mln"") AS [Taglio cliente]FROM (Aziende INNER JOIN Clienti ON Aziende.IDazienda = Clienti.AziendaID) INNER JOIN (Trattative INNER JOIN Portafoglio ON Trattative.IDtrattativa = Portafoglio.TrattativaID) ON Clienti.IDCliente = Trattative.ClienteID;"
    But something like this is more readable and easier to modify later:
    Code:
    Dim qry As String
qry = "SELECT Portafoglio.CandidatoID" & vbcrlf
qry = qry & "       , Portafoglio.TrattativaID" & vbcrlf
qry = qry & "       , Aziende.Azienda" & vbcrlf
qry = qry & "       , Portafoglio.Cliente" & vbcrlf
qry = qry & "       , Portafoglio.[Tipo di cliente]" & vbcrlf
qry = qry & "       , Portafoglio.Gestito" & vbcrlf
qry = qry & "       , Portafoglio.Assicurativo" & vbcrlf
qry = qry & "       , Portafoglio.[Gestioni patrimoniali]" & vbcrlf
qry = qry & "       , Portafoglio.Amministrato" & vbcrlf
qry = qry & "       , Portafoglio.Certificati" & vbcrlf
qry = qry & "       , Portafoglio.Liquidità" & vbcrlf
qry = qry & "       , Portafoglio.[Anno inizio rapporto]" & vbcrlf
qry = qry & "       , Portafoglio.Fidelizzazione" & vbcrlf
qry = qry & "       , Portafoglio.Impieghi" & vbcrlf
qry = qry & "       , Portafoglio.Trasferibile" & vbcrlf
qry = qry & "       , SWITCH([trasferibile] = ""Sicuro"", 1, [trasferibile] = ""probabile"", 2, [trasferibile] = ""possibile"", 3, [trasferibile] = ""improbabile"", 4, [trasferibile] = ""negativo"", 5) AS [Trasferibile numerico]" & vbcrlf
qry = qry & "       , Trattative.[Livello minimo trasferibile]" & vbcrlf
qry = qry & "       , SWITCH([livello minimo trasferibile] = ""Sicuro"", 1, [livello minimo trasferibile] = ""probabile"", 2, [livello minimo trasferibile] = ""possibile"", 3, [livello minimo trasferibile] = ""improbabile"", 4, [livello minimo trasferibile] = ""negativo"", 5) AS [livello minimo trasferibile numerico]" & vbcrlf
qry = qry & "       , IIF([livello minimo trasferibile numerico] >= [trasferibile numerico], True, False) AS [In linea]" & vbcrlf
qry = qry & "       , NZ([gestito]) + NZ([assicurativo])" & vbcrlf
qry = qry & "         + NZ([gestioni patrimoniali])" & vbcrlf
qry = qry & "         + NZ([amministrato]) + NZ([certificati])" & vbcrlf
qry = qry & "         + NZ([liquidità]) AS Totale" & vbcrlf
qry = qry & "       , SWITCH([totale] < 100000, ""<100k"", [totale] BETWEEN 100000 AND 299999, ""100k-300k"", [totale] BETWEEN 300000 AND 499999, ""300k-500k"", [totale] BETWEEN 500000 AND 699999, ""500k-700k"", [totale] BETWEEN 700000 AND 1000000, ""700k-1mln"", [totale] BETWEEN 1000000 AND 1999999, ""1mln-2mln"", [totale] BETWEEN 2000000 AND 4999999, ""2mln-5mln"", [totale] BETWEEN 5000000 AND 9999999, ""5mln-10mln"", [totale] >= 10000000, "">=10mln"") AS [Taglio cliente]" & vbcrlf
qry = qry & " FROM   (Aziende" & vbcrlf
qry = qry & "        INNER JOIN Clienti" & vbcrlf
qry = qry & "                ON Aziende.IDazienda = Clienti.AziendaID)" & vbcrlf
qry = qry & "       INNER JOIN (Trattative" & vbcrlf
qry = qry & "                   INNER JOIN Portafoglio" & vbcrlf
qry = qry & "                           ON Trattative.IDtrattativa = Portafoglio.TrattativaID)" & vbcrlf
qry = qry & "               ON Clienti.IDCliente = Trattative.ClienteID;"

Me.[portafoglio trasferibile].form.recordsource = qry
    This is a handy tool to convert sql code to a vba string: http://allenbrowne.com/ser-71.html

    But I used this tool and changed the output option to VB: http://www.dpriver.com/pp/sqlformat.htm
    Reply With Quote Reply With Quote
  3. 05-08-2020, 07:35 AM #3
    diegomarino
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    434
    that's wonderful, this is the best forum i ever found!
    sorry for messy code, but till 2 week ago i didn't know what vba means.
    this is a great solution and a great tool (the sql one)
    thanks

    ps just one more question if you want to answer. How can i put a filter in the [in linea] filed to returno only true values? in my query i tried to add that filter but went up a prompt asking me the "livello minimo trasferibile" value

    Quote Originally Posted by kd2017 View Post
    I don't see where you've substituted double quotes for single quotes.

    Anyway, try this:
    Code:
    Me.[portafoglio trasferibile].form.recordsource = "SELECT Portafoglio.CandidatoID, Portafoglio.TrattativaID, Aziende.Azienda, Portafoglio.Cliente, Portafoglio.[Tipo di cliente], Portafoglio.Gestito, Portafoglio.Assicurativo, Portafoglio.[Gestioni patrimoniali], Portafoglio.Amministrato, Portafoglio.Certificati, Portafoglio.Liquidità, Portafoglio.[Anno inizio rapporto], Portafoglio.Fidelizzazione, Portafoglio.Impieghi, Portafoglio.Trasferibile, Switch([trasferibile]=""Sicuro"",1,[trasferibile]=""probabile"",2,[trasferibile]=""possibile"",3,[trasferibile]=""improbabile"",4,[trasferibile]=""negativo"",5) AS [Trasferibile numerico], Trattative.[Livello minimo trasferibile], Switch([livello minimo trasferibile]=""Sicuro"",1,[livello minimo trasferibile]=""probabile"",2,[livello minimo trasferibile]=""possibile"",3,[livello minimo trasferibile]=""improbabile"",4,[livello minimo trasferibile]=""negativo"",5) AS [livello minimo trasferibile numerico], IIf([livello minimo trasferibile numerico]>=[trasferibile numerico],True,False) AS [In linea], Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([liquidità]) AS Totale, Switch([totale]<100000,""<100k"",[totale] Between 100000 And 299999,""100k-300k"",[totale] Between 300000 And 499999,""300k-500k"",[totale] Between 500000 And 699999,""500k-700k"",[totale] Between 700000 And 1000000,""700k-1mln"",[totale] Between 1000000 And 1999999,""1mln-2mln"",[totale] Between 2000000 And 4999999,""2mln-5mln"",[totale] Between 5000000 And 9999999,""5mln-10mln"",[totale]>=10000000,"">=10mln"") AS [Taglio cliente]FROM (Aziende INNER JOIN Clienti ON Aziende.IDazienda = Clienti.AziendaID) INNER JOIN (Trattative INNER JOIN Portafoglio ON Trattative.IDtrattativa = Portafoglio.TrattativaID) ON Clienti.IDCliente = Trattative.ClienteID;"
    But something like this is more readable and easier to modify later:
    Code:
    Dim qry As String
qry = "SELECT Portafoglio.CandidatoID" & vbcrlf
qry = qry & "       , Portafoglio.TrattativaID" & vbcrlf
qry = qry & "       , Aziende.Azienda" & vbcrlf
qry = qry & "       , Portafoglio.Cliente" & vbcrlf
qry = qry & "       , Portafoglio.[Tipo di cliente]" & vbcrlf
qry = qry & "       , Portafoglio.Gestito" & vbcrlf
qry = qry & "       , Portafoglio.Assicurativo" & vbcrlf
qry = qry & "       , Portafoglio.[Gestioni patrimoniali]" & vbcrlf
qry = qry & "       , Portafoglio.Amministrato" & vbcrlf
qry = qry & "       , Portafoglio.Certificati" & vbcrlf
qry = qry & "       , Portafoglio.Liquidità" & vbcrlf
qry = qry & "       , Portafoglio.[Anno inizio rapporto]" & vbcrlf
qry = qry & "       , Portafoglio.Fidelizzazione" & vbcrlf
qry = qry & "       , Portafoglio.Impieghi" & vbcrlf
qry = qry & "       , Portafoglio.Trasferibile" & vbcrlf
qry = qry & "       , SWITCH([trasferibile] = ""Sicuro"", 1, [trasferibile] = ""probabile"", 2, [trasferibile] = ""possibile"", 3, [trasferibile] = ""improbabile"", 4, [trasferibile] = ""negativo"", 5) AS [Trasferibile numerico]" & vbcrlf
qry = qry & "       , Trattative.[Livello minimo trasferibile]" & vbcrlf
qry = qry & "       , SWITCH([livello minimo trasferibile] = ""Sicuro"", 1, [livello minimo trasferibile] = ""probabile"", 2, [livello minimo trasferibile] = ""possibile"", 3, [livello minimo trasferibile] = ""improbabile"", 4, [livello minimo trasferibile] = ""negativo"", 5) AS [livello minimo trasferibile numerico]" & vbcrlf
qry = qry & "       , IIF([livello minimo trasferibile numerico] >= [trasferibile numerico], True, False) AS [In linea]" & vbcrlf
qry = qry & "       , NZ([gestito]) + NZ([assicurativo])" & vbcrlf
qry = qry & "         + NZ([gestioni patrimoniali])" & vbcrlf
qry = qry & "         + NZ([amministrato]) + NZ([certificati])" & vbcrlf
qry = qry & "         + NZ([liquidità]) AS Totale" & vbcrlf
qry = qry & "       , SWITCH([totale] < 100000, ""<100k"", [totale] BETWEEN 100000 AND 299999, ""100k-300k"", [totale] BETWEEN 300000 AND 499999, ""300k-500k"", [totale] BETWEEN 500000 AND 699999, ""500k-700k"", [totale] BETWEEN 700000 AND 1000000, ""700k-1mln"", [totale] BETWEEN 1000000 AND 1999999, ""1mln-2mln"", [totale] BETWEEN 2000000 AND 4999999, ""2mln-5mln"", [totale] BETWEEN 5000000 AND 9999999, ""5mln-10mln"", [totale] >= 10000000, "">=10mln"") AS [Taglio cliente]" & vbcrlf
qry = qry & " FROM   (Aziende" & vbcrlf
qry = qry & "        INNER JOIN Clienti" & vbcrlf
qry = qry & "                ON Aziende.IDazienda = Clienti.AziendaID)" & vbcrlf
qry = qry & "       INNER JOIN (Trattative" & vbcrlf
qry = qry & "                   INNER JOIN Portafoglio" & vbcrlf
qry = qry & "                           ON Trattative.IDtrattativa = Portafoglio.TrattativaID)" & vbcrlf
qry = qry & "               ON Clienti.IDCliente = Trattative.ClienteID;"

Me.[portafoglio trasferibile].form.recordsource = qry
    This is a handy tool to convert sql code to a vba string: http://allenbrowne.com/ser-71.html

    But I used this tool and changed the output option to VB: http://www.dpriver.com/pp/sqlformat.htm
    Reply With Quote Reply With Quote
  4. 05-08-2020, 08:50 AM #4
    kd2017
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,165
    Quote Originally Posted by diegomarino View Post
    ps just one more question if you want to answer. How can i put a filter in the [in linea] filed to returno only true values? in my query i tried to add that filter but went up a prompt asking me the "livello minimo trasferibile" value

    [EDITED BECAUSE I HAD A BRAIN FART AND GAVE A BAD ANSWER]

    There are a couple ways to do it but I don't know the context of your application.

    I suppose the easiest way would be to apply a filter to the form like so
    Code:
    Me.[portafoglio trasferibile].form.Filter = "[In linea]=True"
Me.[portafoglio trasferibile].form.FilterOn = True
    Reply With Quote Reply With Quote
  5. 05-08-2020, 09:20 AM #5
    diegomarino
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    434
    thanks, it's just what i did, i was wondering if possible to make in the statement, but i think it's ok

    ps do ot worry about the fart, i wear a surgery mask for the covid

    Quote Originally Posted by kd2017 View Post
    [EDITED BECAUSE I HAD A BRAIN FART AND GAVE A BAD ANSWER]

    There are a couple ways to do it but I don't know the context of your application.

    I suppose the easiest way would be to apply a filter to the form like so
    Code:
    Me.[portafoglio trasferibile].form.Filter = "[In linea]=True"
Me.[portafoglio trasferibile].form.FilterOn = True
    Reply With Quote Reply With Quote
  6. 05-08-2020, 10:14 AM #6
    kd2017
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,165
    Quote Originally Posted by diegomarino View Post
    thanks, it's just what i did, i was wondering if possible to make in the statement, but i think it's ok
    Since [In linea] is the calculated result of a bunch of switches and if statements I would just wrap up your query in another select statement as a subquery and filter it like so

    Code:
    Dim qry As String
qry = "SELECT * FROM (" & vbcrlf
qry = qry & "SELECT Portafoglio.CandidatoID" & vbcrlf
qry = qry & "       , Portafoglio.TrattativaID" & vbcrlf
qry = qry & "       , Aziende.Azienda" & vbcrlf
qry = qry & "       , Portafoglio.Cliente" & vbcrlf
qry = qry & "       , Portafoglio.[Tipo di cliente]" & vbcrlf
qry = qry & "       , Portafoglio.Gestito" & vbcrlf
qry = qry & "       , Portafoglio.Assicurativo" & vbcrlf
qry = qry & "       , Portafoglio.[Gestioni patrimoniali]" & vbcrlf
qry = qry & "       , Portafoglio.Amministrato" & vbcrlf
qry = qry & "       , Portafoglio.Certificati" & vbcrlf
qry = qry & "       , Portafoglio.Liquidità" & vbcrlf
qry = qry & "       , Portafoglio.[Anno inizio rapporto]" & vbcrlf
qry = qry & "       , Portafoglio.Fidelizzazione" & vbcrlf
qry = qry & "       , Portafoglio.Impieghi" & vbcrlf
qry = qry & "       , Portafoglio.Trasferibile" & vbcrlf
qry = qry & "       , SWITCH([trasferibile] = ""Sicuro"", 1, [trasferibile] = ""probabile"", 2, [trasferibile] = ""possibile"", 3, [trasferibile] = ""improbabile"", 4, [trasferibile] = ""negativo"", 5) AS [Trasferibile numerico]" & vbcrlf
qry = qry & "       , Trattative.[Livello minimo trasferibile]" & vbcrlf
qry = qry & "       , SWITCH([livello minimo trasferibile] = ""Sicuro"", 1, [livello minimo trasferibile] = ""probabile"", 2, [livello minimo trasferibile] = ""possibile"", 3, [livello minimo trasferibile] = ""improbabile"", 4, [livello minimo trasferibile] = ""negativo"", 5) AS [livello minimo trasferibile numerico]" & vbcrlf
qry = qry & "       , IIF([livello minimo trasferibile numerico] >= [trasferibile numerico], True, False) AS [In linea]" & vbcrlf
qry = qry & "       , NZ([gestito]) + NZ([assicurativo])" & vbcrlf
qry = qry & "         + NZ([gestioni patrimoniali])" & vbcrlf
qry = qry & "         + NZ([amministrato]) + NZ([certificati])" & vbcrlf
qry = qry & "         + NZ([liquidità]) AS Totale" & vbcrlf
qry = qry & "       , SWITCH([totale] < 100000, ""<100k"", [totale] BETWEEN 100000 AND 299999, ""100k-300k"", [totale] BETWEEN 300000 AND 499999, ""300k-500k"", [totale] BETWEEN 500000 AND 699999, ""500k-700k"", [totale] BETWEEN 700000 AND 1000000, ""700k-1mln"", [totale] BETWEEN 1000000 AND 1999999, ""1mln-2mln"", [totale] BETWEEN 2000000 AND 4999999, ""2mln-5mln"", [totale] BETWEEN 5000000 AND 9999999, ""5mln-10mln"", [totale] >= 10000000, "">=10mln"") AS [Taglio cliente]" & vbcrlf
qry = qry & " FROM   (Aziende" & vbcrlf
qry = qry & "        INNER JOIN Clienti" & vbcrlf
qry = qry & "                ON Aziende.IDazienda = Clienti.AziendaID)" & vbcrlf
qry = qry & "       INNER JOIN (Trattative" & vbcrlf
qry = qry & "                   INNER JOIN Portafoglio" & vbcrlf
qry = qry & "                           ON Trattative.IDtrattativa = Portafoglio.TrattativaID)" & vbcrlf
qry = qry & "               ON Clienti.IDCliente = Trattative.ClienteID" '<----- Note that  I removed the semicolon, not necessary
qry = qry & ") AS sbqry WHERE sbqry.[In linea]=True"

Me.[portafoglio trasferibile].form.recordsource = qry
    ps do ot worry about the fart, i wear a surgery mask for the covid
    Safety first!
    Reply With Quote Reply With Quote
  7. 05-08-2020, 10:58 AM #7
    diegomarino
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    434
    thank you very much
    Reply With Quote Reply With Quote
Please reply to this thread with any new information or opinions.
« Previous Thread | Next Thread »

Similar Threads

  1. Long Text Box
    By data808 in forum Access
    Replies: 5
    Last Post: 09-24-2014, 12:26 AM
  2. class module vs regular module
    By Madmax in forum Modules
    Replies: 1
    Last Post: 05-01-2012, 03:44 PM
  3. Referencing and Changing A Form Module Variable From A Module
    By orcinus in forum Modules
    Replies: 4
    Last Post: 05-16-2011, 04:58 PM
  4. how long
    By maintt in forum Access
    Replies: 1
    Last Post: 07-23-2010, 01:59 PM
  5. how long as this been around for.?
    By SOniC in forum Access
    Replies: 0
    Last Post: 04-12-2007, 11:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules

Other Forums: Microsoft Office Forums