Excel 2007 linked to Access 2007 query pulls default of query formula instead of correct answer

dhdata

New Member
Joined
Aug 5, 2015
Messages
2
Hello - first post here.

I have a spreadsheet in Excel 2007 that is pulling from a query built in Access 2007. The formula in the query reads:

RetireElig: IIf([age]>=65 And [ServiceYrs]>=10,"65 & 110",IIf([age]>=60 And [ServiceYrs]>=15,"60 & 15",IIf([Age]>=55 And ([age]+[ServiceYrs])>=85,"Rule 85","Not Eligible")))

When I run the query in Access, I get a myriad of (correct) returns for my population of 3,400 employees. When I export it into an Excel spreadsheet, I get all the same (correct) data. However, when I use my linked Excel spreadsheet refreshed data, the entire column shows only "Not Eligible."

I have a couple other fields in my query that have similar formulas and Excel pulls the data just fine... for instance:

Generation: IIf(Year([VW_EmpHistData_DWTARGET_DW_PS_EMPLOYEE_DIM].[birth_date])<1925,"pre-Silent",IIf(Year([VW_EmpHistData_DWTARGET_DW_PS_EMPLOYEE_DIM].[birth_date]) Between 1924 And 1943,"Silent",IIf(Year([VW_EmpHistData_DWTARGET_DW_PS_EMPLOYEE_DIM].[birth_date]) Between 1942 And 1961,"Boomer",IIf(Year([VW_EmpHistData_DWTARGET_DW_PS_EMPLOYEE_DIM].[birth_date]) Between 1960 And 1982,"Gen X",IIf(Year([VW_EmpHistData_DWTARGET_DW_PS_EMPLOYEE_DIM].[birth_date]) Between 1981 And 2004,"Millenial",IIf(Year([VW_EmpHistData_DWTARGET_DW_PS_EMPLOYEE_DIM].[birth_date]) Between 2004 And 2018,"Homelander","UPDATE FORMULA"))))))


So far, I have tried the following:
  1. set the column to text in Excel and refreshed
  2. set the column to be Plain Text in Access, then opened Excel and refreshed
  3. changed the default value from "Not Eligible" to something else - Excel spreadsheet refreshes and pulls new default value

Any other ideas?
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,895
your rules do not encompass all possiblilties.
Instead of an IIF field, I would change the query to a UNION query. (which has 5 queries)

select *,"65 & 110" as Note from table where [age]>=65 And [ServiceYrs]>=1
union
select *,"60 & 15" as Note from table where [age]>=60 And [ServiceYrs]>=15
union
select *,"Rule 85" as Note from table where [Age]>=55 And ([age]+[ServiceYrs])>=85
union
...etc

but I still think your ranges are double dipping.
 

dhdata

New Member
Joined
Aug 5, 2015
Messages
2
thanks Ranman ... I am still pretty green and do not know what a Union query is, but I will research and try.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,288
Messages
5,485,912
Members
407,523
Latest member
Talicius

This Week's Hot Topics

Top