Combining text and currency fields with possible nulls

  • Thread starter Stumped with Limitations
  • Start date
S

Stumped with Limitations

Hi,

I am trying to avoid SQL or VBA (due to the lack of my knowledge) and I am
trying to create a query for a "Thank you" letter. The table/form has a
dollar amount field, Item 1, Item 2, Item 3 for possible donations. There
could be several scenarios... dollar amount, with 2 items donated or just an
item and so forth. So I need to make one field showing the fields that have
something in them and the currency with $ and appropriate thousands plus text
fields. I.E. Thank you for your donation of "insert field" ($220 and
antique doll). Any clue on how I could do this??? Any help would be much
appreciated.
 
K

KARL DEWEY

Concatenate in a query column using IIF statements.

ThankYou: "Thank you for" & [Field1] & IIf([Field2] Is not Null, " and
"&[Field2], Null) & IIf([DollarField] Is Null,".", &"valued at $"&
[DollarField])

Not tested but I think you can get the idea.
 
S

Stumped with Limitations

Thank you Karl, this helped tremendously, but how do I keep the currency
format, the expression is keeping it in text format. i.e. 1000 for $1,000.00
which is what I think would look much better in the letter. Any help? Thanks.

KARL DEWEY said:
Concatenate in a query column using IIF statements.

ThankYou: "Thank you for" & [Field1] & IIf([Field2] Is not Null, " and
"&[Field2], Null) & IIf([DollarField] Is Null,".", &"valued at $"&
[DollarField])

Not tested but I think you can get the idea.

Stumped with Limitations said:
Hi,

I am trying to avoid SQL or VBA (due to the lack of my knowledge) and I am
trying to create a query for a "Thank you" letter. The table/form has a
dollar amount field, Item 1, Item 2, Item 3 for possible donations. There
could be several scenarios... dollar amount, with 2 items donated or just an
item and so forth. So I need to make one field showing the fields that have
something in them and the currency with $ and appropriate thousands plus text
fields. I.E. Thank you for your donation of "insert field" ($220 and
antique doll). Any clue on how I could do this??? Any help would be much
appreciated.
Click to expand...
Click to expand...
 
J

John Spencer

Use the format function to format the Dollar Field
Format([DollarField],"$#,##0.00")
Thank you Karl, this helped tremendously, but how do I keep the currency
format, the expression is keeping it in text format. i.e. 1000 for $1,000.00
which is what I think would look much better in the letter. Any help? Thanks.

KARL DEWEY said:
Concatenate in a query column using IIF statements.

ThankYou: "Thank you for" & [Field1] & IIf([Field2] Is not Null, " and
"&[Field2], Null) & IIf([DollarField] Is Null,".", &"valued at $"&
[DollarField])

Not tested but I think you can get the idea.

Stumped with Limitations said:
Hi,

I am trying to avoid SQL or VBA (due to the lack of my knowledge) and I am
trying to create a query for a "Thank you" letter. The table/form has a
dollar amount field, Item 1, Item 2, Item 3 for possible donations. There
could be several scenarios... dollar amount, with 2 items donated or just an
item and so forth. So I need to make one field showing the fields that have
something in them and the currency with $ and appropriate thousands plus text
fields. I.E. Thank you for your donation of "insert field" ($220 and
antique doll). Any clue on how I could do this??? Any help would be much
appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Top