Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408

    Column property in query criteria

    hi
    i have this query in my vba and it works fine (with naturally some changes)

    now i made a query with that code that you can see below

    Code:
    INSERT INTO EMList ( CandidatoID, Nome, Azienda, Email, tipocontatto, CC )SELECT ReferentiAziendali.CandidatoID, [nome] & " " & [cognome] AS NomeCognome, Aziende.Azienda, Candidati.[email ufficio], Candidati.tipocontatto, ZoneCandidato.CC
    FROM Comuni INNER JOIN (Aziende INNER JOIN ((ReferentiAziendali INNER JOIN Candidati ON ReferentiAziendali.CandidatoID = Candidati.IDcandidato) INNER JOIN ZoneCandidato ON ReferentiAziendali.CandidatoID = ZoneCandidato.CandidatoID) ON Aziende.IDazienda = Candidati.AziendaID) ON Comuni.IDComune = ZoneCandidato.ComuneID
    WHERE (((ReferentiAziendali.ClienteID)=[Maschere]![Contatti]![Candidati].[Scheda]![TBIDCliente]) AND ((ReferentiAziendali.Competenza)="selezione") AND ((Comuni.Comune)=[Maschere]![Contatti]![Candidati].[Scheda]![ComuneTB]) AND ((ZoneCandidato.ProfiloID)=[Maschere]![Contatti]![Candidati].[Scheda]![CCSceltaTrattativa].column(7)));
    but the part
    Code:
    [Maschere]![Contatti]![Candidati].[Scheda]![CCSceltaTrattativa].column(7)
    doesn't work.


    what's wrong?

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Queries have no idea as to what a column is?
    I presume you concatenated the value in VBA?

    Someone else was trying to do the same the other day. I think the suggestion was a text control set to that column and then refer to that text control in the query.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    thanks.
    i have a combobox with 8 columns, i want to set a criteria based on the 7th column of that textbox.
    i was thinking as suggested to add a text control, but my form is already full of stuff

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Use a tempvar instead? Form control does not even need to be visible?
    Plus 7th column would be (6) as they start from zero
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    sorry, i thought i wrote my answer.

    yes i know, in fact it is the 8th actually...

    tempvar? you mean a global variable?

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    i was thinking as suggested to add a text control, but my form is already full of stuff
    you can hide the control, or make it invisible by setting left, top, width and height to 0

    tempvars - https://docs.microsoft.com/en-us/off...ccess.TempVars

  8. #8
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    thanks, this should work

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IIRC, you cannot simply refer to a combo column in a query - but you can wrap it in the Eval function, which will return the column value from the list. I might have that somewhere in a test db but would have to look for it.

    EDIT - this syntax works in a query criteria field:

    Eval("Forms![frmMyForm].cmbMyCombo.Column(1)")
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    thanks.
    i used:
    Code:
    Eval("[forms]![contatti]![candidati].[form].[CCSceltaTrattativa].column(7)")
    i have no error but not the result that should be; i checked using the value in critera and it works

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure what you're reporting. Combobox and listbox column collection is zero based, so 0 is the first column. Have you allowed for that?
    EDIT - I see that this was covered in post 5, so I don't know what to suggest if you already accounted for this.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    got it!

    Code:
    Eval("Int([forms]![contatti]![candidati].[form].[CCSceltaTrattativa].column(7))")
    i addedd int()

    thanks

  13. #13
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad to be of help.
    What is the largest number that the criteria will likely ever be? The max for Int is 32767 and cannot include fractions.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    it's very small, right know are just 10 i think

  15. #15
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I am trying to get my head around why using int() would work?
    You either look for the value of the column or not? That was the whole idea was it not?, not column - 2, or column + 3 ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-25-2020, 10:39 AM
  2. Replies: 2
    Last Post: 07-03-2017, 09:10 AM
  3. Replies: 4
    Last Post: 06-09-2017, 01:44 PM
  4. Replies: 2
    Last Post: 04-02-2015, 12:45 PM
  5. Replies: 5
    Last Post: 05-01-2013, 11:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums