Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Data (MS Access 2000 )

    I need to import data into an Access database, and when there is a NULL value in a field I need to convert this to and Empty String. Is there a way to do this with a setting at import or do I need to import to a temp table and fill the fields with Empty Strings (and how would I do that for each record - not all fields in each record are NULL) and then append to the table that I need to fill. The program that I am using, that uses Access as the backend does not allow NULL values and requires Empty Strings instead.

    Thanks

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

    Re: Importing Data (MS Access 2000 )

    You can import the table first, then run an update query:
    - Create a new query in design view, based on the table.
    - Add all fields to the query grid.
    - Select Query | Update Query.
    - Enter the following (with the appropriate field name substituted) in the Update to line for each field:

    Nz([NameOfField],"")

    - Select Query | Run.
    - If you need to do this more than once, save the query for later reuse.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Data (MS Access 2000 )

    Thanks worked great!! I am having a problem though with the table after I appended the information. When I go to use the frontend application to add Contacts which uses a drop down list box based on the Company table (which I have now 3100 companies listed), I get the following error: "The Company List is too long for the list box, which has a limit of 64KB. The contents has been truncated." Is this an Access problem or the frontend application problem? Thanks again

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

    Re: Importing Data (MS Access 2000 )

    There is a limit to the contents of combo boxes in Access, but it's 64K items, not 64KB size, so the problem is probably in the frontend. A list with 3100 entries is not very manageable anyway, can you do anything about the frontend?

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Data (MS Access 2000 )

    When you say 64K items - is that 64K lines or records, or 64K characters, or what exactly? There are two tables, one with the list of companies and their information, the other Contacts, which gets the Company information from the Company table.

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

    Re: Importing Data (MS Access 2000 )

    Im meant 2^16 = 65,536 items.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Data (MS Access 2000 )

    Thanks for your help, I will contact the software developer on this error.

    shihalud

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Importing Data (MS Access 2000 )

    What is the SQL behind the Combo box? Would you post this for us to have a look at.

    If you have no join behind the 2 tables you may be creating a cartesian product which could be causing the problem.

    How many records are there in both tables, please supply the counts of the individually tables concerned.

Posting Permissions

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