Having Data could not be retrieved from Database issue in excel but related to a column in access

[CSP12345]

Hi all,

I have been having issues with importing data into excel I get the message Data could not be retrieve from database. I thought it was an excel issue but I did a few things.

Since my query had some expressions in it I made a query of that query just to see if that worked, it didn't so then I started removing columns from the query and bingo issue gone. I then went back to the original query with the expressions in it and hid the last column and bingo that worked. Thing is I can not for the life of me figure out why this would be the case.

I have made sure there is no errors in it with a iff expression so if there is a non value it returns a 0.

The field is not formatted to any thing same as the others.

So what the Hell could it be?

 

[ranman256]

Is it possible to EXPORT data to excel while in Access , rather than pull data form Access in Excel?
via DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, vQry, vFile, True, vTab

Access knows the functions in it, where Excel wont.

 

[CSP12345]

No, because the spreadsheet needs to be opened by another user who does not have access on their computer at any given time with the items being current to what is in access.

I actually fixed the issue. I found it was a iif([field1 = ""], [field1] + [field2, "") in access that was giving me the import data error. For some reason which I do not understand it was causing issues. I removed the iif expression and used a formatcurrency expression and made sure that no fields were left blank rather put in a 0.

If someone could explain that to me would be fantastic.

 

[CSP12345]

As you can see I am still trying to get my head around access using excel type formulas to over come issues. It doesn't always work that way when you have to import back.

 

[xenou]

Hi,

IIF() doesn't work to avoid errors in the way you want it to, because of the way IIF works. Basically, it evaluates all the arguments in both conditions, even when the first is false. So with this expression iif([field1] = "", [field1] + [field2], "") you are still adding field1 + field2 even when field1 is empty.

Usually you find some other workaround, though in a database there's really no reason to be keeping numeric values and strings in the same column -- i.e. Field1, in this case. How can that sometimes be numbers and sometimes strings? What is the datatype of Field1? It doesn't quite add up here.

 

[SydneyGeek]

Iif isn't an Excel function. More often than not, if Excel encounters a 'foreign' function, it can't do the calculation. Mostly it catches you out with custom functions, but I think I remember having troubles with Iif too.

Denis

 

[CSP12345]

Yep that is most of the issues I am having trying to use excel type functions to carry out access expressions. I am slowly getting there. Thank you all for you help.

 

[therealtiger]

Had this issue where the data in one column (varchar(20)) of a SQL Server View had a mixture of bad data in it - dates, strings, commas, double quotes - when I removed this column from the query that was pulling from the SQL Server View, the results returned without issue.