Problems importing Access data into Excel

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,227
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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 :)
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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:
Upvote 0
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:
Upvote 0
What excel file format are you using?
 
Upvote 0
What excel file format are you using?

I'm using Office 2013 and exporting as a straight .xlsx file

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 :)
 
Upvote 0
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,[COLOR=#333333]([End  User 1]-[Previous LTC])/[End User 1])[/COLOR]
 
Upvote 0
Upvote 0
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,[COLOR=#333333]([End  User 1]-[Previous LTC])/[End User 1])[/COLOR]
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top