Column property in query criteria

[diegomarino]

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

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

[Maschere]![Contatti]![Candidati].[Scheda]![CCSceltaTrattativa].column(7)

doesn't work.
what's wrong?

[Welshgasman]

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.

[orange]

I suggest you tell us what you want to do in simple terms.

[diegomarino]

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

[Welshgasman]

Use a tempvar instead? Form control does not even need to be visible?
Plus 7th column would be (6) as they start from zero

[diegomarino]

sorry, i thought i wrote my answer.

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

tempvar? you mean a global variable?

[CJ_London]

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

[diegomarino]

thanks, this should work

[Micron]

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)")

[diegomarino]

thanks.
i used:

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

[Micron]

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.

[diegomarino]

got it!

Eval("Int([forms]![contatti]![candidati].[form].[CCSceltaTrattativa].column(7))")

i addedd int()

thanks

[Micron]

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.

[diegomarino]

it's very small, right know are just 10 i think

[Welshgasman]

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 ?