Concatenate without Nulls?

[Guest]

I wasn’t sure if this belonged in Forms or Reports. Since my question
involves both I decided to put it in the forms section. Please let me know
if I need to repost it in another section.

Is it possible to Concatenate (or its equivalent) and NOT display in an open
form and/or print fields with Null values?

I’m working in A2k.

I need to be able to display in an open form and/or print on a corresponding
report most of the fields in a particular order (with specific separators).
These fields are included in a subform and subreport , which is linked to the
main form/report via [IataID].

The order and fields that must be displayed and/or printed are: (all fields
are in tblIATAMaterial)

1. [UnIDNo] (Text)
2. [ProperShippingName] (Memo)
3. [ClassOrDivison] (Text)
4. [SubsidiaryRisk] (Text)
5. [PackingGroup] (Text)
6. [QuantityAndTypeOfPacking](Memo)
7. [PackingInst](Text)
8. [Authorization](Memo)

1 thru 5 must be separated by a comma “,†and 6,7 and 8 must be separated by
2 forward slashes “//â€

I have the following expression in a query:

Data: [UNorIDno] & " , " & [ProperShippingName] & " , " & [ClassorDivison] &
" , " & [SubsidiaryRisk] & " , " & [PackingGroup] & " // " &
[QuantyAndTypeOfPacking] & " // " & [PackingInst] & " // " & [Authorization]

Which returns something like:

Where there are No Nulls in fields – Everything looks fine:
[UnIDNo], [ProperShippingName] , [ClassOrDivison] ,[ SubsidiaryRisk],
[PackingGroup] // [QuantityAndTypeOfPacking] // [PackingInst]
//[Authorization]

Where there are Nulls in some fields – I need to skip them and display or
print only the fields with the required separator.

This is wrong because of the blank spaces:
[UnIDNo], [ProperShippingName] , [ClassOrDivison] , , [PackingGroup] //
[QuantityAndTypeOfPacking] // //

How can I prevent the unnecessary spaces, commas or slashes when the fields
that should be between them have Null values?

Any help would be greatly appreciated. I’m not exactly new to Access but
very new to constructing code.

If a solution involves code, kingly reply with full details/keystrokes
(where it belongs and exactly what it should look like).

Thanks in advance

Larry G

 

[Rob Oldfield]

Access rules 1, 2, 7, and 13. Look at the nz function. Works in both
queries and code.

 

[Marshall Barton]

I wasn’t sure if this belonged in Forms or Reports. Since my question
involves both I decided to put it in the forms section. Please let me know
if I need to repost it in another section.

Is it possible to Concatenate (or its equivalent) and NOT display in an open
form and/or print fields with Null values?

I’m working in A2k.

I need to be able to display in an open form and/or print on a corresponding
report most of the fields in a particular order (with specific separators).
These fields are included in a subform and subreport , which is linked to the
main form/report via [IataID].

The order and fields that must be displayed and/or printed are: (all fields
are in tblIATAMaterial)

1. [UnIDNo] (Text)
2. [ProperShippingName] (Memo)
3. [ClassOrDivison] (Text)
4. [SubsidiaryRisk] (Text)
5. [PackingGroup] (Text)
6. [QuantityAndTypeOfPacking](Memo)
7. [PackingInst](Text)
8. [Authorization](Memo)

1 thru 5 must be separated by a comma “,” and 6,7 and 8 must be separated by
2 forward slashes “//”

I have the following expression in a query:

Data: [UNorIDno] & " , " & [ProperShippingName] & " , " & [ClassorDivison] &
" , " & [SubsidiaryRisk] & " , " & [PackingGroup] & " // " &
[QuantyAndTypeOfPacking] & " // " & [PackingInst] & " // " & [Authorization]

Which returns something like:

Where there are No Nulls in fields – Everything looks fine:
[UnIDNo], [ProperShippingName] , [ClassOrDivison] ,[ SubsidiaryRisk],
[PackingGroup] // [QuantityAndTypeOfPacking] // [PackingInst]
//[Authorization]

Where there are Nulls in some fields – I need to skip them and display or
print only the fields with the required separator.

This is wrong because of the blank spaces:
[UnIDNo], [ProperShippingName] , [ClassOrDivison] , , [PackingGroup] //
[QuantityAndTypeOfPacking] // //

How can I prevent the unnecessary spaces, commas or slashes when the fields
that should be between them have Null values?

You can take advantage of the + operator that concatenates
strings as well as propogatimg Null values. For example:

string + Null results in Null
string & Null results in string

So. you can use an expression like:

Data: [UNorIDno] & (" , " + [ProperShippingName]) & (" , " +
[ClassorDivison]) & (" , " + [SubsidiaryRisk]) & (" , " +
[PackingGroup]) & (" // " + [QuantyAndTypeOfPacking]) & ("
// " + [PackingInst]) & (" // " + [Authorization])

 

[Guest]

Marsh,

THANK YOU !!!
Your suggestion seems to be working fine.

I actually thought about using the "+" sign but wasn't sure how.

I wasn’t sure if this belonged in Forms or Reports. Since my question
involves both I decided to put it in the forms section. Please let me know
if I need to repost it in another section.

Is it possible to Concatenate (or its equivalent) and NOT display in an open
form and/or print fields with Null values?

I’m working in A2k.

I need to be able to display in an open form and/or print on a corresponding
report most of the fields in a particular order (with specific separators).
These fields are included in a subform and subreport , which is linked to the
main form/report via [IataID].

The order and fields that must be displayed and/or printed are: (all fields
are in tblIATAMaterial)

1. [UnIDNo] (Text)
2. [ProperShippingName] (Memo)
3. [ClassOrDivison] (Text)
4. [SubsidiaryRisk] (Text)
5. [PackingGroup] (Text)
6. [QuantityAndTypeOfPacking](Memo)
7. [PackingInst](Text)
8. [Authorization](Memo)

1 thru 5 must be separated by a comma “,†and 6,7 and 8 must be separated by
2 forward slashes “//â€

I have the following expression in a query:

Data: [UNorIDno] & " , " & [ProperShippingName] & " , " & [ClassorDivison] &
" , " & [SubsidiaryRisk] & " , " & [PackingGroup] & " // " &
[QuantyAndTypeOfPacking] & " // " & [PackingInst] & " // " & [Authorization]

Which returns something like:

Where there are No Nulls in fields – Everything looks fine:
[UnIDNo], [ProperShippingName] , [ClassOrDivison] ,[ SubsidiaryRisk],
[PackingGroup] // [QuantityAndTypeOfPacking] // [PackingInst]
//[Authorization]

Where there are Nulls in some fields – I need to skip them and display or
print only the fields with the required separator.

This is wrong because of the blank spaces:
[UnIDNo], [ProperShippingName] , [ClassOrDivison] , , [PackingGroup] //
[QuantityAndTypeOfPacking] // //

How can I prevent the unnecessary spaces, commas or slashes when the fields
that should be between them have Null values?

You can take advantage of the + operator that concatenates
strings as well as propogatimg Null values. For example:

string + Null results in Null
string & Null results in string

So. you can use an expression like:

Data: [UNorIDno] & (" , " + [ProperShippingName]) & (" , " +
[ClassorDivison]) & (" , " + [SubsidiaryRisk]) & (" , " +
[PackingGroup]) & (" // " + [QuantyAndTypeOfPacking]) & ("
// " + [PackingInst]) & (" // " + [Authorization])

 

[Guest]

Rob,

Thanks for the suggestion.

Unfortuneatly I didn't quite understand your reference to "Access rules"
(actually where to find them).

In the meantime, Marshal Barton replied and changed my expression
accordingly. Which seems to be working fine.

Thanks again and sorry I didn't get it.

Larry G-NJ

Access rules 1, 2, 7, and 13. Look at the nz function. Works in both
queries and code.

I wasn't sure if this belonged in Forms or Reports. Since my question
involves both I decided to put it in the forms section. Please let me know
if I need to repost it in another section.

Is it possible to Concatenate (or its equivalent) and NOT display in an open
form and/or print fields with Null values?

I'm working in A2k.

I need to be able to display in an open form and/or print on a corresponding
report most of the fields in a particular order (with specific separators).
These fields are included in a subform and subreport , which is linked to the
main form/report via [IataID].

The order and fields that must be displayed and/or printed are: (all fields
are in tblIATAMaterial)

1. [UnIDNo] (Text)
2. [ProperShippingName] (Memo)
3. [ClassOrDivison] (Text)
4. [SubsidiaryRisk] (Text)
5. [PackingGroup] (Text)
6. [QuantityAndTypeOfPacking](Memo)
7. [PackingInst](Text)
8. [Authorization](Memo)

1 thru 5 must be separated by a comma "," and 6,7 and 8 must be separated by
2 forward slashes "//"

I have the following expression in a query:

Data: [UNorIDno] & " , " & [ProperShippingName] & " , " & [ClassorDivison] &
" , " & [SubsidiaryRisk] & " , " & [PackingGroup] & " // " &
[QuantyAndTypeOfPacking] & " // " & [PackingInst] & " // " & [Authorization]

Which returns something like:

Where there are No Nulls in fields - Everything looks fine:
[UnIDNo], [ProperShippingName] , [ClassOrDivison] ,[ SubsidiaryRisk],
[PackingGroup] // [QuantityAndTypeOfPacking] // [PackingInst]
//[Authorization]

Where there are Nulls in some fields - I need to skip them and display or
print only the fields with the required separator.

This is wrong because of the blank spaces:
[UnIDNo], [ProperShippingName] , [ClassOrDivison] , , [PackingGroup] //
[QuantityAndTypeOfPacking] // //

How can I prevent the unnecessary spaces, commas or slashes when the fields
that should be between them have Null values?

Any help would be greatly appreciated. I'm not exactly new to Access but
very new to constructing code.

If a solution involves code, kingly reply with full details/keystrokes
(where it belongs and exactly what it should look like).

Thanks in advance

Larry G

 

[Rob Oldfield]

Sorry. I was joking. The point is that a large chunk of Access problems
are caused by null values - and the nz function can be used to cope with
them.

Rob,

Thanks for the suggestion.

Unfortuneatly I didn't quite understand your reference to "Access rules"
(actually where to find them).

In the meantime, Marshal Barton replied and changed my expression
accordingly. Which seems to be working fine.

Thanks again and sorry I didn't get it.

Larry G-NJ

Access rules 1, 2, 7, and 13. Look at the nz function. Works in both
queries and code.

I wasn't sure if this belonged in Forms or Reports. Since my question
involves both I decided to put it in the forms section. Please let

me
know

if I need to repost it in another section.

Is it possible to Concatenate (or its equivalent) and NOT display in

an
open

form and/or print fields with Null values?

I'm working in A2k.

I need to be able to display in an open form and/or print on a corresponding
report most of the fields in a particular order (with specific separators).
These fields are included in a subform and subreport , which is linked

to
the

main form/report via [IataID].

The order and fields that must be displayed and/or printed are: (all fields
are in tblIATAMaterial)

1. [UnIDNo] (Text)
2. [ProperShippingName] (Memo)
3. [ClassOrDivison] (Text)
4. [SubsidiaryRisk] (Text)
5. [PackingGroup] (Text)
6. [QuantityAndTypeOfPacking](Memo)
7. [PackingInst](Text)
8. [Authorization](Memo)

1 thru 5 must be separated by a comma "," and 6,7 and 8 must be

separated
by

2 forward slashes "//"

I have the following expression in a query:

Data: [UNorIDno] & " , " & [ProperShippingName] & " , " &

[ClassorDivison]
&

" , " & [SubsidiaryRisk] & " , " & [PackingGroup] & " // " &
[QuantyAndTypeOfPacking] & " // " & [PackingInst] & " // " & [Authorization]

Which returns something like:

Where there are No Nulls in fields - Everything looks fine:
[UnIDNo], [ProperShippingName] , [ClassOrDivison] ,[ SubsidiaryRisk],
[PackingGroup] // [QuantityAndTypeOfPacking] // [PackingInst]
//[Authorization]

Where there are Nulls in some fields - I need to skip them and display or
print only the fields with the required separator.

This is wrong because of the blank spaces:
[UnIDNo], [ProperShippingName] , [ClassOrDivison] , , [PackingGroup] //
[QuantityAndTypeOfPacking] // //

How can I prevent the unnecessary spaces, commas or slashes when the fields
that should be between them have Null values?

Any help would be greatly appreciated. I'm not exactly new to Access but
very new to constructing code.

If a solution involves code, kingly reply with full details/keystrokes
(where it belongs and exactly what it should look like).

Thanks in advance

Larry G