Results 1 to 5 of 5
  1. #1
    mara9642 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2018
    Posts
    13

    How to configure Access so that a linked Excel table correctly populates a yes/no field

    Good afternoon all,



    I'm trying to build a database that pulls data from several different linked excel files and combines them with data entered directly into Access. I have the tables linked successfully; my quandary is how to handle yes/no fields so that they populate properly in Access (I'm using the Office 2010 suite - it's a company system, so updating is not an option.)

    I'm tracking activities for several departments within our organizations and one of the fields is called "Completed". In Access, I have this as a yes/no column. When I export the Access tables into Excel it populates this column as True/False. The problem with this is the group using the Excel spreadsheet that I want to link from puts "Y" in the Completed column for "yes" and leave it blank for "no". Due to the way that the data is displayed on the source spreadsheet forcing the users to use True/False makes the columns wide enough that the entire spreadsheet doesn't display on the monitor at once - this is not an insurmountable issue, but does detract from the usability of the source spreadsheet (there are several columns that use this convention).

    What would be the best way to handle this information so that I can combine it with a yes/no column in Access? I would like to generate reports where the items marked Completed from all sources populate in one area. I would prefer to handle the data conversion in Access to minimize changes for the user; however, I'm not opposed to making the changes in Excel. I'm not skilled with VBA or Macros so I would need help to automate it.

    Thank you in advance for the help.

    Theresa

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if the excel column has 'yes' and no 'No'
    then you can make a query that judges this column:
    IIF([field]='Yes',1,0)

    use this function during import, or as a live query.

  3. #3
    mara9642 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2018
    Posts
    13
    Thank you for the quick response. I tried the method you suggested and am getting the following error "Data type mismatch in criteria expression." The field in question is currently a test field so I'm not sure what's causing the error.

    Thanks again!

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Instead of exporting a table, you should be using a query.



    Here is a table:
    Click image for larger version. 

Name:	Table1.png 
Views:	10 
Size:	39.5 KB 
ID:	35557


    If you create a query and add a calculation, you would have:
    Click image for larger version. 

Name:	Query1.png 
Views:	10 
Size:	33.9 KB 
ID:	35558

    This is the calculation: IsComplete: IIf([Completed],"Y","N")
    Of course, you do not have to have the field "Completed" as a column in the query. And you can name the column whatever you want.
    I used "IsCompleted", but you can use whatever name you want (Done, Ended, Rat, Fini, Wahoo,...)


    Executing the query results in
    Click image for larger version. 

Name:	Results1.png 
Views:	10 
Size:	45.5 KB 
ID:	35559

  5. #5
    mara9642 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2018
    Posts
    13
    ​That's perfect! Thank you so much for your help!

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

Similar Threads

  1. Replies: 2
    Last Post: 12-15-2017, 08:09 PM
  2. Access table linked to excel - field size problem
    By tzmyo in forum Import/Export Data
    Replies: 1
    Last Post: 10-02-2013, 01:03 AM
  3. Replies: 15
    Last Post: 12-10-2012, 06:37 PM
  4. Replies: 5
    Last Post: 06-13-2012, 09:34 AM
  5. Replies: 3
    Last Post: 01-03-2012, 12:28 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