Need help with IIf and Trim$ function in a query

[Nancy]

Hi,

I have a query where I want to concatenate a field called CAS to a field
called Product Description if the CAS field is not empty. I also need to
trim both fields and add brackets around [CAS] if it is not empty. The trim
and concatenation work fine if the CAS field is not empty. If it is empty,
the brackets are still concatenated to the Product Description field but with
no data between ([]). Please see my function code below. I will appreciate
any help you can provide.


Product Description Concat: IIf(([tbl_Quote Line Item]![Product
Description])="",Trim$([tbl_Quote Line Item]![Product
Description]),Trim$([tbl_Quote Line Item]![Product Description]) & " " & "["
& Trim$([tbl_Quote Line Item]![CAS#]) & "]")

Thanks,

 

[Duane Hookom]

Access doesn't store trailing spaces and you would rarely have leading spaces
so Trim() is generally not needed.

I expect Product Description might be Null which can't be compared with "".
Assuming you don't have multiple fields with the same name in your query, you
might be able to use only:

Product Description Concat: [Product Description] + " [" + [CAS#] + "]"

 

[Nancy]

Thank you for your response. Unfortunately, the product description and CAS
fields come from a linked fox pro table and do contain extraneous spaces at
the end. I do need to trim these fields. Your response does not handle the
situation where there is no data in the CAS field. Empty brackets will still
be added after the product description.

Do you have any other ideas?
--
Nancy

Access doesn't store trailing spaces and you would rarely have leading spaces
so Trim() is generally not needed.

I expect Product Description might be Null which can't be compared with "".
Assuming you don't have multiple fields with the same name in your query, you
might be able to use only:

Product Description Concat: [Product Description] + " [" + [CAS#] + "]"


--
Duane Hookom
Microsoft Access MVP

Hi,

I have a query where I want to concatenate a field called CAS to a field
called Product Description if the CAS field is not empty. I also need to
trim both fields and add brackets around [CAS] if it is not empty. The trim
and concatenation work fine if the CAS field is not empty. If it is empty,
the brackets are still concatenated to the Product Description field but with
no data between ([]). Please see my function code below. I will appreciate
any help you can provide.


Product Description Concat: IIf(([tbl_Quote Line Item]![Product
Description])="",Trim$([tbl_Quote Line Item]![Product
Description]),Trim$([tbl_Quote Line Item]![Product Description]) & " " & "["
& Trim$([tbl_Quote Line Item]![CAS#]) & "]")

Thanks,

 

[John W. Vinson]

I have a query where I want to concatenate a field called CAS to a field
called Product Description if the CAS field is not empty. I also need to
trim both fields and add brackets around [CAS] if it is not empty. The trim
and concatenation work fine if the CAS field is not empty. If it is empty,
the brackets are still concatenated to the Product Description field but with
no data between ([]). Please see my function code below. I will appreciate
any help you can provide.

If the CAS field is in fact NULL for "blank" records, you can use the fact
that both the + and & operators concatenate strings, but the + operator
propagates NULLS whereas & treats NULL as if it were a zero length string:

Trim([Product Description]) & ("[" + Trim([CAS]) + "]")

If CAS can contain blanks or a zero length string instead, you'll need to use
IIF:

Trim([Product Description]) & IIF(Len(Trim([CAS])) = 0, NULL,
"[" & [CAS] & "]")

 

[Nancy]

Thank you John. This worked very well for most results. Once in a while, I
will get the result for an product that has no CAS where the product
description is displayed plus empty brackets ([]). I cannot understand why
this happens. I have check the table where the CAS originates and it is
blank.

Can you help me with this question too?
I appreciate your help!
--
Nancy

I have a query where I want to concatenate a field called CAS to a field
called Product Description if the CAS field is not empty. I also need to
trim both fields and add brackets around [CAS] if it is not empty. The trim
and concatenation work fine if the CAS field is not empty. If it is empty,
the brackets are still concatenated to the Product Description field but with
no data between ([]). Please see my function code below. I will appreciate
any help you can provide.

If the CAS field is in fact NULL for "blank" records, you can use the fact
that both the + and & operators concatenate strings, but the + operator
propagates NULLS whereas & treats NULL as if it were a zero length string:

Trim([Product Description]) & ("[" + Trim([CAS]) + "]")

If CAS can contain blanks or a zero length string instead, you'll need to use
IIF:

Trim([Product Description]) & IIF(Len(Trim([CAS])) = 0, NULL,
"[" & [CAS] & "]")

 

[Duane Hookom]

It would have been best if you would have stated something abou the link to
FoxPro in your first posting since this is important to the answer.

You might want to try:
Trim([Product Description]) & IIF(Len(Trim([CAS] & "")) = 0, NULL,
"[" & [CAS] & "]")

--
Duane Hookom
Microsoft Access MVP

Thank you John. This worked very well for most results. Once in a while, I
will get the result for an product that has no CAS where the product
description is displayed plus empty brackets ([]). I cannot understand why
this happens. I have check the table where the CAS originates and it is
blank.

Can you help me with this question too?
I appreciate your help!
--
Nancy

I have a query where I want to concatenate a field called CAS to a field
called Product Description if the CAS field is not empty. I also need to
trim both fields and add brackets around [CAS] if it is not empty. The trim
and concatenation work fine if the CAS field is not empty. If it is empty,
the brackets are still concatenated to the Product Description field but with
no data between ([]). Please see my function code below. I will appreciate
any help you can provide.

If the CAS field is in fact NULL for "blank" records, you can use the fact
that both the + and & operators concatenate strings, but the + operator
propagates NULLS whereas & treats NULL as if it were a zero length string:

Trim([Product Description]) & ("[" + Trim([CAS]) + "]")

If CAS can contain blanks or a zero length string instead, you'll need to use
IIF:

Trim([Product Description]) & IIF(Len(Trim([CAS])) = 0, NULL,
"[" & [CAS] & "]")

 

[Nancy]

Thank you Duane. It worked like a charm! I will remember to mention any
links in future help requests.
--
Nancy

It would have been best if you would have stated something abou the link to
FoxPro in your first posting since this is important to the answer.

You might want to try:
Trim([Product Description]) & IIF(Len(Trim([CAS] & "")) = 0, NULL,
"[" & [CAS] & "]")

--
Duane Hookom
Microsoft Access MVP

Thank you John. This worked very well for most results. Once in a while, I
will get the result for an product that has no CAS where the product
description is displayed plus empty brackets ([]). I cannot understand why
this happens. I have check the table where the CAS originates and it is
blank.

Can you help me with this question too?
I appreciate your help!
--
Nancy

I have a query where I want to concatenate a field called CAS to a field
called Product Description if the CAS field is not empty. I also need to
trim both fields and add brackets around [CAS] if it is not empty. The trim
and concatenation work fine if the CAS field is not empty. If it is empty,
the brackets are still concatenated to the Product Description field but with
no data between ([]). Please see my function code below. I will appreciate
any help you can provide.

If the CAS field is in fact NULL for "blank" records, you can use the fact
that both the + and & operators concatenate strings, but the + operator
propagates NULLS whereas & treats NULL as if it were a zero length string:

Trim([Product Description]) & ("[" + Trim([CAS]) + "]")

If CAS can contain blanks or a zero length string instead, you'll need to use
IIF:

Trim([Product Description]) & IIF(Len(Trim([CAS])) = 0, NULL,
"[" & [CAS] & "]")