I do apologise if this thread has been started in the wrong section. - Wasn’t sure where to put it...
________________________________________________________________

I am using Microsoft Access with a select query to combine 4 separate fields into 1 field for each record using the following expression:..

Expr1: ([prod_group_name] & " - " & [model] & " - " & & " - " & [amps])

This part works fine, and gives me an output that looks something like this:..

"Amazon low voltage submersible pumps - Amazon 12v - 4.5"

Now some records have empty fields, and in which case nothing is shown. I.E. "RS sub pumps" have no size or amperage, so it comes out like this...

"RS submersible pumps - RS-150 - -" - (with the prevailing dashes and spaces which are not needed.)

So basically what I want to do is make it automatically put the spaces and dashes in ONLY if they are needed... Is this actually possible in Access? And if so, how could my expression be modified to do so?

Thanks for your time, any help greatly appreciated...

NuGG

Hi,

Use the inbuilt function nz
Expr1: (nz([prod_group_name],"") & " - " & nz([model],"") ....

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.