Using Column() in a Query?

[davesmith202]

I have a query where one of the fields is:

Inv: Inv1 & "/" & Inv2 & "/" & Inv3

This works fine and displays 3 numbers like this: 234/215/235

However, the Inv1, Inv2 and Inv3 fields are actually comboboxes which store the ID field of the lookup table.

So, instead I want to do show this:

Inv: Company1 & "/" & Company2 & "/" & Company3

i.e. I display the Company field rather than just the Inv no.

Can I use Column() somehow or do I need an alternative approach?

Thanks,

Dave

 

[Beetle]

However, the Inv1, Inv2 and Inv3 fields are actually comboboxes which store the ID field of the lookup table.

I would recommend not using lookup fields in tables, for several reasons. Combo boxes (aka lookup fields) are appropriate in forms, but not in tables.

Can I use Column() somehow or do I need an alternative approach?

You cannot reference the Column property of a combo box in a query field, but you could use an unbound control (i.e. a text box) on a form;

=[Combo1].Column(x) & "/" & [Combo2].Column(x)

where (x) is the index value of the column that holds the data you want. It's a zero based index, so the first column is Column(0), etc.

 

[davesmith202]

Unfortunately, I can't change the tables because they are linked in to so many things, so I have to work with what I have got.

There are no related Forms open when I launch the query. I can bring in the lookup table into the query and set the join correctly. But if I have that lookup table in the same query, I am still not sure how to display the same effect of column(1).

Any ideas?

 

[Beetle]

If the lookup table is joined in the query then you can get the values you want directly from there. If the field names are the same in both tables then you'll have to reference the table name first, i.e.

Inv: [LookupTable].[Company1] & "/" & [LookupTable].[Company2]

 

[davesmith202]

Its not quite that simple! There are 4 fields within tblInvoices that lookup values in the Lookup table tblEvents. Lets call them say Event1, Event2, Event3 and Event4.

Each of these fields looks up an ID within tblEvents. I want to use the EventName field but can only get the EventID.

Does that make sense?

 

[Beetle]

Its not quite that simple! There are 4 fields within tblInvoices that lookup values in the Lookup table tblEvents. Lets call them say Event1, Event2, Event3 and Event4.

Since you said you can't change the tables, I guess I won't mention that you shouldn't have repeating fields like Event1, Event2, etc. (oops, I guess I just did)

Having said that, if you have 4 separate Event fields in tblInvoices, then you can add tblEvents to the query 4 times (the QBE will alias them like tblEvents, tblEvents_1, tblEvents_2, etc.), then create a relationship between each Event field to each instance of tblEvents. Then, do as I suggested before but use the appropriate table alias name;

Inv: [tblEvents].[EventName] & "/" & [tblEvents_1].[EventName]

This is, of course, a kludge, but with your improper table structure you're probably going to be stuck with some kludges to get the data you want from this app.

 

[davesmith202]

The difficulty I have is that the client already has a complex database with many things bolted on a bit like a Frankensteins monster. So I am trying to find a bolt on solution.

Is it possible for me to use a Dlookup function as part of the query column formula? i.e. Dlookup the value in the lookup table using the Event1-4 values?

Edit: Beetle, I posted before seeing your answer. Yes, it is a kludge but I like the idea, even if a bit messy! Do you think its better using that than a Dlookup?

 

[Beetle]

Beetle, I posted before seeing your answer. Yes, it is a kludge but I like the idea, even if a bit messy! Do you think its better using that than a Dlookup?

I'm not even completely sure it will return what you want, I'm just throwing out the idea. If it does return what you want then it may be a bit more efficient than 4 DLookups.

 

[davesmith202]

I'm pleased to say your solution worked Beetle, thank you.