Results 1 to 6 of 6
  1. #1
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067

    Building a field to link data from different systems

    I have a database I'm building for several Departments. The data includes personal information for people that attend events my company puts on throughout the year. We also have a commercial Database for Members. The events include both members and non-members. I'm trying to create a field called personkey that will be used to link our custom Database to the Members in the commercial Database. I've created a field that takes the first 4 characters of the lastname, the first 4 of the firstname and the DOB in the format mmddyyyy to create that field. The data is in SQL Server 2008 R2. I want to create a view that included the primary key field of the commercial members table and builds the personkey from the commercial members table so I can link from our database to the commercial members database. Just not sure how I can convert the Birth date field to the mmddyyyy string I need to complete the personkey field. Looked at Cast but it doesn't allow you to put in a specific format as far as I can tell. Anybody have an idea how I can complete this?

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    wouldn't it be easier and safer to just store the primary key in the sql server table in your other db?

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    2012 has a FORMAT() function, but I don't think 2008 does. You'll probably have to concatenate using DATEPART()
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    As I stated the other db is a commercial Database that we purchased and we don't have the option of storing that field in the other db.

  5. #5
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    Thanks pbaldy that's what I needed.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 08-22-2014, 05:10 PM
  2. Form field to append data and link
    By joeuser in forum Access
    Replies: 1
    Last Post: 12-15-2011, 12:41 PM
  3. Replies: 1
    Last Post: 07-26-2011, 05:18 PM
  4. Link Master Field and Link Child Field
    By evander in forum Forms
    Replies: 2
    Last Post: 05-25-2010, 09:13 PM
  5. Replies: 7
    Last Post: 02-08-2010, 12:14 PM

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