Extract the right field from recordsource

[diegomarino]

hi, i have this recordsource (cutted of the part after FROM)

SELECT Trattative.IDtrattativa, Trattative.CandidatoID, Aziende_1.Azienda AS [Banca candidato], Candidati.nomecognome, Comuni.Comune, Aziende.Azienda, Trattative.StatoTrattativa, Trattative.Fase, Trattative.Manager, Trattative.Nostra, Trattative.[Data inizio trattativa], Trattative.[Data fine trattativa], Trattative.[Head hunter], Trattative.[Ral nuova], Trattative.NumeroCandidatoCliente, Trattative.Note, Candidati.[ptf trasferibile personale]

i have a combobox that has as source the field list of this query; as you can say i have two similar fields, "[Banca candidato] that is Aziende_1.Aziende" and Aziende.Azienda.

since i use this combo to filter the query, how can i get the correct name field from combobox?

If i choose [banca candidato] i can't use this value to filter, cause the real name is "Aziende_1.azienda", if i choose "azienda" i'm not able to find the correct name that is "aziende.azienda" and not "aziende_1.Azienda"

thanks

[ranman256]

you can rename the field in the query to stop duplicate confusion:

..Comuni.Comune, Az1: Aziende.Azienda, ...

then use Az1 on the form

[diegomarino]

you can rename the field in the query to stop duplicate confusion:

..Comuni.Comune, Az1: Aziende.Azienda, ...

then use Az1 on the form

and then extract the string after the ":"..right

or better, extract the part before the "as"

[CJ_London]

You reference a combo by its name and column number

My combo.column(1)

References the second column

[Micron]

IIRC, you cannot use .Column(x) in a query?

[CJ_London]

Agreed - thought op was building a filter in vba

[Micron]

You are probably correct, as usual. I try to go first by the post title then the forum area. So much gets posted in "Access" that it's hard to tell. At first I had recordset in mind, figuring if a field was in your recordsource what's there to extract? Then I thought this was about an alias for a field because of
"the real name is "Aziende_1.azienda", if i choose "azienda" i'm not able to find the correct name"

I thought you were suggesting using Column(x) in the posted sql.

[diegomarino]

sorry, i got a bit confused.

i tried when in after update event of the combobox to get the value through a msgbox, i used "msgbox(me.mycombo.column(1))", changing the column number to 0 to 3, but for "[banca candidato]" field which the real value is Aziende_1.Azienda, i get only the name [banca candidato], not Aziende_1.Azienda

[CJ_London]

Really struggling to understand what you are trying to achieve. You wrote the rowsource, you know what each column means, why does it matter if a column has been aliased?

[CJ_London]

Implication is your field list is based on a query based on multiple tables.

So what are you trying to filter? A form recordsource based on this query? Something else?

[diegomarino]

Well, i made a form to filter forms, both based on table (easy) and query.

in this case, i'm trying to filter this recordsource

SELECT Trattative.IDtrattativa, Trattative.CandidatoID, Aziende_1.Azienda AS [Banca candidato], Candidati.nomecognome, Comuni.Comune, Aziende.Azienda, Trattative.StatoTrattativa, Trattative.Fase, Trattative.Manager, Trattative.Nostra, Trattative.[Data inizio trattativa], Trattative.[Data fine trattativa], Trattative.[Head hunter], Trattative.[Ral nuova], Trattative.NumeroCandidatoCliente, Trattative.Note, Candidati.[ptf trasferibile personale] FROM Comuni RIGHT JOIN (Aziende AS Aziende_1 RIGHT JOIN ((Clienti RIGHT JOIN (Candidati INNER JOIN Trattative ON Candidati.IDcandidato = Trattative.CandidatoID) ON Clienti.IDCliente = Trattative.ClienteID) LEFT JOIN Aziende ON Clienti.AziendaID = Aziende.IDazienda) ON Aziende_1.IDazienda = Candidati.AziendaID) ON Comuni.IDComune = Candidati.ComuneID;

i create a sql string to filter the recordsource above, but when i got those duplicates (aziende.azienda and aziende_1.azienda) i'm not able to extract correctly the name of the field

i cannot use the text of the combobox that has fields as rowsource, cause it is not "Aziende.azienda" but i get just "azienda", and sql string do not work

[ArviLaanemets]

Maybe you have to set form's Filter property (or add WHERE clause to your query) as

Aziende_1.Azienda = FilterValueForBancaCandidato AND Azienda.Azienda = FilterValueForAzienda

Depending on on your data and what you want to achive, both filter values may be same, or they may differ.

[CJ_London]

So much missing from what you are telling us. If the combo is supposed to be listing the fields in the recordsource the user can select from to filter why would they be different? You would use the same sql

If you are applying a filter, you would apply it to the aliased name so I still don’t see what the issue is.

Suggest you step back, explain how your forms/combo are intended to work

[diegomarino]

EDIT I remebered:

the problem is that i have three combobox

Combo 1: in which i select the name of the field that i want to use to filter
Combo 2: in which i have the operator (=,<>, startwith etc etc)
Combo 3: the value that the recordsource has to matche

e.g.
combo 1: Name
Combo 2: =
Combo 3: "Mickey mouse"


the real problem is that when i use the "=" or "<>" operator i want to limit the rowsource of the combobox 3 to the values present in the recordsource
i use this code

  If Me.combo1= "multivalue" Then            Set ValuesRs = db.OpenRecordset("SELECT DISTINCT " & FldVar & ".value" & " " & FldOrigin)
            Set Me.Value1CB.Recordset = ValuesRs
         Else
           Set ValuesRs = db.OpenRecordset("SELECT DISTINCT " & FldVar & " " & FldOrigin & " WHERE " & FldVar & " is not null")
           Set Me.Value1CB.Recordset = ValuesRs
            'MsgBox ("SELECT DISTINCT " & FldVar & " " & FldOrigin & " WHERE " & FldVar & " is not null")
         End If

fldvar is the name of the field in combo1
fldorigin is the recordsource cutted from the "FROM" of the sql

as you can see, i cannot use the aliased name.
what should i do? filter the rowsource of combo 3 in another way?

[CJ_London]

seen to have moved the goalposts - your sql build does not include a FROM