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

CSP12345

Board Regular
Joined
Oct 1, 2014
Messages
52
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?
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,842
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

Board Regular
Joined
Oct 1, 2014
Messages
52
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

Board Regular
Joined
Oct 1, 2014
Messages
52
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,603
Office Version
2013
Platform
Windows
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

Board Regular
Joined
Oct 1, 2014
Messages
52
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

New Member
Joined
Feb 20, 2020
Messages
1
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,089,638
Messages
5,409,474
Members
403,265
Latest member
HMR120

This Week's Hot Topics

Top