Joining 2 tables in a query

[silversubey]

Hi, I am new to access so this may seem simple.

I am joining 2 columns of data into 1 column in a query. My expression looks like the following.

Field: ([column1] & ":" & [column2])

the Query column looks correct if there is data in both columns.

column1:column2

But when there is no data in column1 it looks like the following

:column2

How do I remove the : if there is no data in column1 so it looks like

column2

[silversubey]

Ok, I figured it out. I'll post it if someone else needs.

Field: IIf([Column1]<>"",([Column1] & ":" & [Column2]),([Column1] & [Column2]))

[silversubey]

Correction from my earlier reply. This does the same result but with less expression.

Field: IIf([Column1]<>"",([Column1] & ":" & [Column2]),([Column2]))

[phytorion]

You could try using a Switch function to test if there is data in column1. It looks like this:

Field: (Switch(Test1,[Column2],999,[Column1] & ":" & [Column2]))

It works like an IF statement Test1 is your test and if its true it returns expression2. Otherwise it returns your original expression(the 999 acts like a else).

Eric

[phytorion]

That works too