Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Expression to combine text strings (XP/2002 SR-2)

    Hello All:

    I've started a new database project (not an Access Project) and it's all coming back to me a little slowly. First things first, I'm trying to write an expression to insert in a Default Value property that combines two other fields. This will seem very straight-forward once you hear the field names:

    FirstName
    Surname

    I'd like to automatically combine these fields for input into a third field that I'm calling LookupName--for use in a Lookup. The previous fields will be used in a mail merge, etc.

    I've tried writing the expression in several different ways. The Access help files seems to have a different suggestion depending on where you look and then throws in all that business about brackets and quotations to boot. It's not as straightforward as one would think.

    By the end of the day, I've settled on:

    =[FirstName]&" "&[Surname]

    This, of course, brings up the error that Access cannot locate the FirstName field (I've tried a complete reference as well) nor can it acknowledge the default value in the table, etc.

    Your help would be greatly appreciated. Thanks!

    S.O.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Expression to combine text strings (XP/2002 SR-2)

    You shouldn't have the LookupName in the table - it is redundant information, and even if you could fill it correctly usung the Default Value property (which you can't), it wouldn't be kept up-to-date if you changed the first name of surname.
    What you must do is create a query based on your table, and create LookupName as a calculated field:

    LookupName:[FirstName]&" "&[Surname]

    If you want to avoid the leading or trailing space you get if either of the names is missing, use this instead:

    LookupName:Trim([FirstName]&" "&[Surname])

    Since this is a calculated field, it will be updated each time you change FirstName or Surname. You can use the query as record source for a form or report, and as data source for a mail merge in Word.

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Expression to combine text strings (XP/2002 SR-2)

    Hans:

    Thanks for your reply. I recently spent some time trying to do some development in Excel and now that I've returned to Access, I'm a little confused. I've had more success grasping your theory than actually being able to apply it but in the course of doing so, I've realised that I don't need to be pursuing this combination of fields. Which is probably why I was having so much trouble with it in the first place.

    Again, thanks for your help!

    S.O.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •