InfoQube IM - Community

Submitted by Armando on 2016/05/04 18:55

In a field (Phase), I'm trying to have an auto list concatenating values from 2 other fields.

I'm using the pop-up list editor (fields properties dialog).

The fields I created:

- Phases : contain the phases names

- Order : contains a number for each phase (fort sorting purposes)

- Phase : this is where I'm trying my SQL request. Its popup list should contain *Order* & *Phases*

Here's what I've tried (among other things) in the list editor**:

SELECT Phases FROM Phases, CONCAT(Order, ' ',Phases)

This doesn't work.

What partially works (obviously) :

SELECT Order,Phases FROM Phases

But... I don't get the concatenation of both values in my Phase field when I make a selection in the list. Only the number or the phase name.

Any idea?

Thanks!

**(Of course, I've created a "Phases" grid containing Phases + orders, and the grid has been made visible)

Comments

The JET database engine incorporated in InfoQube – which is broadly speaking the database software which underlies earlier versions of Microsoft Access – supports only a Microsoft-specific subset of the full ANSI SQL syntax. The CONCAT operator does not form a part of that subset. The syntax for string concatenation which is supported is discussed in a post at http://stackoverflow.com/questions/20403870/concat-equivalent-in-ms-acc…. Quoting from there:

There are two concatenation operators available in Access: +; and &. They differ in how they deal with Null.

"foo" + Null returns Null

"foo" & Null returns "foo"

So if you want to update Null [My Column] fields to contain "Prefix " afterwards, use ...

SET [My Column] = "Prefix " & [My Column]

But if you prefer to leave it as Null, you could use the + operator instead ...

SET [My Column] = "Prefix " + [My Column]

However, in the second case, you could revise the WHERE clause to ignore rows where [My Column] contains Null.

WHERE [Different Column]='someValue' AND [My Column] Is Not Null

Pierre would add: HTH.

Mark GREGORY, Redon, France - GMT +1/+2; EST +6

Many thanks for those detailed explanations, Mark. Much appreciated!

I wonder if that's going to work with I'm trying to do though (will try later).

IQ geek

Windows 8.1

CPU: Intel i5 2.6ghz