Results 1 to 9 of 9
  1. #1
    dotcanada is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44

    Option Values when exporting to Excel


    Good day.

    My main form has an Option Group (3 options) which stores the values into a table called tblPlayerInfo. I created a second table (tblAppointments) which cross-references the numerical option values to a text value which gets displayed in the datasheet of tblPlayerInfo.

    My jam is that I have an excel file that is linked to the access database that displays the table, however the table in excel displays the numerical values of the option group rather than the text value.

    Is there a way to display the text values in the excel table once it gets exported?

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Try creating a query that does the same thing, i.e. links to the tblAppointments to get the text value, then export the results of the query.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Option values ARE numeric. If you joined to the cross table, then bring down the text value of the option into the query.

  4. #4
    dotcanada is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44
    Apologies.

    The table that is in excel is from a query in the database. In the query, it also shows the text values in datasheet view. But still not the excel table. However, if I add the tblAppointments to my query, and then try viewing the datasheet, it states Type mismatch in expression.

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Are you using a lookup field in your table? If so, that is your issue. Lookup fields cause all kinds of problems, and are best avoided.

  6. #6
    dotcanada is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44
    Which table are you referring to? The Access or excel?

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    The Access table. Lookup fields cause problems because what you see is not what is in the table "behind the scenes"

  8. #8
    dotcanada is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44
    Hi John.

    That would be it then. Appreciate your help. Cheers.

  9. #9
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    The easiest fix is to change that lookup field to a numeric integer, and use it as a FK to the tblAppointments to get the equivalent text value. Then you can make a query with the two related tables and containing the text value and export the query.

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

Similar Threads

  1. Storing Values from an Option Group
    By Bentley in forum Forms
    Replies: 11
    Last Post: 07-06-2016, 07:45 AM
  2. Replies: 10
    Last Post: 12-28-2012, 02:06 PM
  3. Resetting Option Group Values
    By oleBucky in forum Forms
    Replies: 4
    Last Post: 03-31-2011, 05:19 PM
  4. option group default values
    By wlumpkin in forum Access
    Replies: 3
    Last Post: 02-15-2011, 03:30 PM
  5. Combo Box and Option Group Values
    By Desstro in forum Queries
    Replies: 8
    Last Post: 09-06-2010, 11:40 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