Concatenate - but do it even if there is no value?

[snewton]

Hi all,



I'm trying to concatenate 4 values. So, for instance, I have:

PartNumberLong: [BuildingName]&"."&[DivisionName]&"."&[PartName]&"."&[FinishName]

The query returns records just fine. However, in some cases, I have not yet defined the [FinishName] for that particular part. In which case, the query returns no results. I do get results if I leave [PartName] blank though.

Is there something I can stick into the criteria to return all parts regardless of the blank fields? Not sure where to go with this.

Thanks,
Stephen

[June7]

Could try wildcard.

Why are you filtering on concatenated value?

[snewton]

Could try wildcard.

Why are you filtering on concatenated value?

I'm not sure what you mean by filtering? I am trying to create a composite part number based on four separate tables.

Database structure is like so https://www.accessforums.net/attachm...9&d=1331323901

Thanks,
Stephen

[June7]

You are concatenating fields and then applying a filter criteria to that compound value - right?

I don't understand why you are constructing a part number from those 4 values. That is one heck of a long identifier. Why do you need this? The data structure in that ERD is probably using Autonumber fields as primary keys.

[snewton]

You are concatenating fields and then applying a filter criteria to that compound value - right?

I don't understand why you are constructing a part number from those 4 values. That is one heck of a long identifier. Why do you need this? The data structure in that ERD is probably using Autonumber fields as primary keys.

June,

Thank you for your reply. I am not filtering the result. I just want to add the fields together, regardless of their contents (blank or not)

To answer your other question - We've decided that each element we produce for a VERY large job (think 20 million dollars), must have a unique identifier. But, if we are going to uniquely identify each part, why wouldn't we make it a smart number? So the elements 252.TR.001.195 and 296.TR.001.189 will be the same fabrication method and dimensions, but different paint, and different building.

In case it is important, I have since updated the relationships according to this discussion https://www.accessforums.net/showthr...atabase-Design and the updated relationships look like this now https://www.accessforums.net/attachm...7&d=1331664284

Thanks,
Stephen

[June7]

What had me confused was the statement in your first post that 'the query returns no results' so I thought filtering was issue. However, it is quite possible because of the jointype in the query. Show query statement for analysis.

The use of 'smart' identifiers has its arguments on both sides. One is the very issue you encountered - one of the pieces of the compound ID doesn't exist. If all components will always be (eventually) available, then possibly this will serve your requirements.