Query Question

[ingalla]

I have the following query:-

Trim([Address1] & ", " & [Address2] & ", " & [Address3] & ", " & [County] &
", " & [Country] & ", " & [PostCode])

However, if any of the fields are blank how can i stop getting two commas
together? ie if the address3 field is empty.

Address1, Address2,, County, County, PostCode

Thanks

Andy

 

[Ofer]

Try this

Trim([Address1] & iif([Address2] not is null, ", " & [Address2],"") &
iif([Address3] not is null,", " & [Address3],"") & iif([County] not is
null,", " & [County],"") &
iif([PostCode] not is null, ", " & [PostCode],""))

 

[John Spencer (MVP)]

Use the + Operator in combination with the & operator. Parentheses help to make
sure that you are getting the desired results. This is not Standard SQL but
works well in Access.


Trim([Address1] & (", " + [Address2]) & (", " + [Address3]) & (", " + [County])
& (", " + [Country] & (", " + [PostCode]))

This works because the & operator combines Null and a String as if the Null was
"". The + operator propagates the null, returns null.

This will fail if you have zero-length strings in the fields and not null
values. If that is the case you need more complicated code to handle the problem.

 

[Ofer]

I like that, its so much better, thanks.

Missing one bracket.

Trim([Address1] & (", " + [Address2]) & (", " + [Address3]) & (", " +
[County])
& (", " + [Country]) & (", " + [PostCode]))

Use the + Operator in combination with the & operator. Parentheses help to make
sure that you are getting the desired results. This is not Standard SQL but
works well in Access.


Trim([Address1] & (", " + [Address2]) & (", " + [Address3]) & (", " + [County])
& (", " + [Country] & (", " + [PostCode]))

This works because the & operator combines Null and a String as if the Null was
"". The + operator propagates the null, returns null.

This will fail if you have zero-length strings in the fields and not null
values. If that is the case you need more complicated code to handle the problem.

I have the following query:-

Trim([Address1] & ", " & [Address2] & ", " & [Address3] & ", " & [County] &
", " & [Country] & ", " & [PostCode])

However, if any of the fields are blank how can i stop getting two commas
together? ie if the address3 field is empty.

Address1, Address2,, County, County, PostCode

Thanks

Andy

 

[ingalla]

Thanks to you both so much....

I like that, its so much better, thanks.

Missing one bracket.

Trim([Address1] & (", " + [Address2]) & (", " + [Address3]) & (", " +
[County])
& (", " + [Country]) & (", " + [PostCode]))

Use the + Operator in combination with the & operator. Parentheses help to make
sure that you are getting the desired results. This is not Standard SQL but
works well in Access.


Trim([Address1] & (", " + [Address2]) & (", " + [Address3]) & (", " + [County])
& (", " + [Country] & (", " + [PostCode]))

This works because the & operator combines Null and a String as if the Null was
"". The + operator propagates the null, returns null.

This will fail if you have zero-length strings in the fields and not null
values. If that is the case you need more complicated code to handle the problem.

I have the following query:-

Trim([Address1] & ", " & [Address2] & ", " & [Address3] & ", " & [County] &
", " & [Country] & ", " & [PostCode])

However, if any of the fields are blank how can i stop getting two commas
together? ie if the address3 field is empty.

Address1, Address2,, County, County, PostCode

Thanks

Andy