Problems importing Access data into Excel

[JazzSP8]

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;

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;

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

 

[stumac]

Re: Problems importing Data into Excel

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

Current Margin: IIf(NZ([End User 1],0)= 0,0,([End User 1]-[Previous LTC])/[End User 1])

 

[JazzSP8]

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

 

[JonXL]

What excel file format are you using?

 

[JazzSP8]

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:

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;

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

 

[stumac]

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:

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]

 

[JazzSP8]

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.

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/convert-nz-iif-problem-stumped.1173427/ )

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

Thanks again for the help, is appreciated

 

[stumac]

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

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]

 

[JazzSP8]

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