Page 1 of 5 12345 LastLast
Results 1 to 15 of 68
  1. #1
    diane802 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    92

    Concatenation of Text and Number

    I have two separate fields: one is text, one is a dollar amount (i.e. 5,000)

    Is it possible to combine the two and have the number show with teh comma?

    Right now - I did the formulas as



    = [Text] & " " & [Number]

    and it shows up as say : Text 5000 instead of Text 5,000

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    = [Text] & " " & Format([Number],"Currency")
    ...and there are tons of other Format formats.

  3. #3
    diane802 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    92
    thanks!

    i am getting this error though bc i guess my formula is too long:

    it says the expression you entered has an invalid strong. A strong can be up to 2048 characters long, including opening and closing quotation marks.

    I am writing an iif statement, where the false parts is the concatenation.... any ideas of getting around this error?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I tend to use Public functions in a standard module rather than a long IIF statement. Much easier to understand and maintain.

  5. #5
    diane802 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    92
    ... .. . . . .

    what is a Public Function?

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It is a Procedure that you place in a Standard Module that can be invoked from anywhere in your application.

  7. #7
    diane802 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    92
    do you have any reference links you can provide so I can read up on how to write standard IF type functions using a public function?

    I am new to VBA.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    How about posting your IIF function, or at least what is supposed to happen, and we'll come up with a Global function that does the same thing?

  9. #9
    diane802 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    92
    Thank you!! You are such a great teacher to me! If this was karate, I would call you sensei.

    IIf ([Record Type]="n/a","n/a",iif([Record Type] Is Null,"n/a",[Record Type] & " " & Format([Record Cost USD],"Currency")))

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Let's try this change first:
    IIf ([Record Type]="n/a" OR IsNull([Record Type]),"n/a",[Record Type] & " " & Format([Record Cost USD],"Currency"))

  11. #11
    diane802 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    92
    that works!!! thank you!

    can i ask another long IIF statement question?

    I have three pieces of data:
    1) Field 1 = Yes or No
    2) Field 2 = Yes or No
    3) Field 3 = Yes or No

    Each field has a corresponding field, where if the Field = Yes, it has text that says say "1 month" or "2 months".

    I am trying to build a field that combines this information, so there are actually 8 possible results:

    (F1, F2, F3):
    Y, Y, Y
    Y, N, Y ... and so on

    and the result would be, if F1 = Yes, it would just show the relevant field text (call it F1A... if F2/F3 also say yes, it would be [F1A] & "; " & [F2A] & " F2; " & [F3A] & " F3"

    When i wrote out hte 8 differention options, given its so many IIFs.. it came out as too many characters as well...

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Besides the three fields F1, F2 and F3, how many other fields are we dealing with here?

  13. #13
    diane802 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    92
    It would be F1A, F2A, F3A

    and then I would want to write text after F2A and F3A to designate what its related to (ie. 3 months in debt)

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Diane,
    Would you care to share the structure of this table with us. Are you in control of the structure or does it come from some other source over which you have no control?

  15. #15
    diane802 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    92
    The strucutre of the table is basically:

    Below three are Yes/No drop-downs
    F1
    F2
    F3

    Then there are three related fields that are free form text. They are null if the above corresponding field is No.
    F1A
    F2A
    F3A

    I tried to write the IIF statement as follows:

    = IIF ([F1] = "No" And [F2] = "No" And [F3] = "No" ,"n/a",IIF ([F1] = "Yes" And [F2] = "No" And [F3] = "No",[F1A],IIF ([F1] = "Yes" And [F2] = "Yes" And [F3] = "No",[F1A] & "; " & [F2A] & "F2",IIF ([F1] = "Yes" And [F2] = "Yes" And [F3] = "Yes",[F1A] & "; " & [F2A] & "F2; " & [F3A] & "F3", IIF ([F1] = "Yes" And [F2] = "nO" And [F3] = "Yes", F1A] & "; " & [F3A] & "F3"

    and so on for every different option there after. There are 8 in total, so hence i am hitting the maximum character string limit.

Page 1 of 5 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Field switching from Number format to Text
    By COforlife in forum Queries
    Replies: 1
    Last Post: 11-10-2009, 03:23 PM
  2. Replies: 3
    Last Post: 10-23-2009, 05:03 PM
  3. Replies: 1
    Last Post: 10-09-2008, 04:48 AM
  4. exporting text produces a number
    By greend in forum Import/Export Data
    Replies: 0
    Last Post: 07-12-2006, 03:55 PM
  5. Concatenate two fields (text & number) for key field
    By Larry Elfenbein in forum Forms
    Replies: 2
    Last Post: 11-10-2005, 07:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums