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

[dhdata]

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?

 

[ranman256]

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]

thanks Ranman ... I am still pretty green and do not know what a Union query is, but I will research and try.