Likes Likes:  0
Results 1 to 9 of 9

Thread: Problems importing Access data into Excel

  1. #1
    Board Regular JazzSP8's Avatar
    Join Date
    Sep 2005
    Location
    Chorley
    Posts
    1,206
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Problems importing Access data into Excel

    Hey All

    I've added some Calculated Fields to a Query I've got in Access, but when I try and import (or export) it into Excel I get an Error Message whereas before I didn't.

    Importing (from Excel: 'Data' > 'Access') I get;

    "Data could not be retrieved from the Database. Check the database server or contact..." (etc)


    Exporting (from Access: Export to Excel Workbook) I get;

    "The contents of fiels in 321863 record(s) were delete, and 0 record(s) were lost due to key violations.
    * If data was deleted, the data you pased or imported doesn't match the field data types or the FieldSize property in the destination table.
    * if records were lost, either the records you pasted contain primary key values that already exist in the destination table, or they violate referential integrity rules for relationship defined between tables."


    The calculations are all along the lines of;

    Code:
    Current Margin: ([End User 1]-[Previous LTC])/[End User 1]
    Now, some of these do give DIV/0 errors, and I believe Access doesn't like exporting a Table with errors (?), so, with an Excel head on I used;

    Code:
    Current Margin: IIf(IsError(([End User 1]-[Previous LTC])/[End User 1]),"",([End User 1]-[Previous LTC])/[End User 1])
    But, that doesn't seem to have done anything useful - The DIV/0 errors still appear and the query still won't Import or Export.

    Can someone please help as to where I should go next as those Error messages don't seem to relate to me putting in Calculated Fields but I don't see what else it can be on something that has been working fine till this point?

    Indeed they do seem to work (apart from the ISERROR) when I view the results in Access?

    Thanks
    The harder you try, the dumber you look.


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  2. #2
    Board Regular
    Join Date
    Jul 2010
    Posts
    381
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problems importing Data into Excel

    try checking if the field is 0 (or null) first:

    Code:
    Current Margin: IIf(NZ([End User 1],0)= 0,0,([End User 1]-[Previous LTC])/[End User 1])
    Last edited by stumac; Apr 23rd, 2019 at 10:56 AM. Reason: formatting

  3. #3
    Board Regular JazzSP8's Avatar
    Join Date
    Sep 2005
    Location
    Chorley
    Posts
    1,206
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Problems importing Data into Excel

    Oh, good call stumac - I did think of that at one point but didn't know how to phrase it in Access and then distracted myself with the Error thing.

    Shall give that a go when I'm back in the Office tomorrow, thanks for the help
    Last edited by JazzSP8; Apr 23rd, 2019 at 12:45 PM.
    The harder you try, the dumber you look.


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  4. #4
    Board Regular JonXL's Avatar
    Join Date
    Feb 2018
    Posts
    189
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problems importing Access data into Excel

    What excel file format are you using?

  5. #5
    Board Regular JazzSP8's Avatar
    Join Date
    Sep 2005
    Location
    Chorley
    Posts
    1,206
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Problems importing Access data into Excel

    Quote Originally Posted by JonXL View Post
    What excel file format are you using?
    I'm using Office 2013 and exporting as a straight .xlsx file

    Quote Originally Posted by stumac View Post
    try checking if the field is 0 (or null) first:

    Code:
    Current Margin: IIf(NZ([End User 1],0)= 0,0,([End User 1]-[Previous LTC])/[End User 1])
    That does seem to have worked... However, when I try and Import it into Excel it won't show me the Query - I did some Googling and found out that Excel doesn't recognise NZ :-/

    I've read that I can replace NZ with IS NULL but I can't get it working with OR, this is what I have so far;

    Code:
    Current  Margin: IIf(Is Null([End User 1]) or Is Null([Previous LTC]),Null,([End  User 1]-[Previous LTC])/[End User 1])
    Can anyone help me with the syntax?

    Thanks
    The harder you try, the dumber you look.


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  6. #6
    Board Regular
    Join Date
    Jul 2010
    Posts
    381
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problems importing Access data into Excel

    But you can export it?

    I imagine it is probably 0's instead of Null you are having the problems with, however IsNull has no space so could be the issue. If you can have either in either field it would be something like:
    Code:
    IIF(IsNull(End User 1]) Or IsNull ([Previous LTC]) Or [End User1] = 0 or [Previous LTC] = 0,0,([End  User 1]-[Previous LTC])/[End User 1])

  7. #7
    Board Regular JazzSP8's Avatar
    Join Date
    Sep 2005
    Location
    Chorley
    Posts
    1,206
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Problems importing Access data into Excel

    Quote Originally Posted by stumac View Post
    But you can export it?
    It does Export, yes - But in an ideal world I'd like it to Import as it's part of a bigger picture within Excel.

    Quote Originally Posted by stumac View Post
    however IsNull has no space so could be the issue.
    Ha! - That's what you get for reading the internet... ( https://www.pcreview.co.uk/threads/c...umped.1173427/ )

    Right, brilliant - I'll go see how far I get with that now!

    Thanks again for the help, is appreciated
    The harder you try, the dumber you look.


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  8. #8
    Board Regular
    Join Date
    Jul 2010
    Posts
    381
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Problems importing Access data into Excel

    Ahhh right, I see - so ISNull wont work but you can have [field] Is null not is null ([Field])

    Code:
    IIF([End User 1] Is Null Or [Previous LTC] Is Null Or [End User1] = 0 or [Previous LTC] = 0,0,([End  User 1]-[Previous LTC])/[End User 1])
    Last edited by stumac; Apr 24th, 2019 at 10:06 AM.

  9. #9
    Board Regular JazzSP8's Avatar
    Join Date
    Sep 2005
    Location
    Chorley
    Posts
    1,206
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Problems importing Access data into Excel

    Ha. Right - I get the difference, with it now, thanks for that!

    I'm basically learning all of this on the fly for a project I'm reworking to be more efficient, it's a bit frustrating trying to get my head around the differences with Excel / Access and the different ways it works - Like I know exactly what I want to do, but not how to do it LOL

    I'll get there in the end, I hope :-S
    The harder you try, the dumber you look.


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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