Access query calculated fields not exporting to excel 1

[Paul_W]

Hi,
I have an Excel (2010) spreadsheet which loads an Access (XP) query. Set up using the Excell Data Access button.

It has worked fine for 2 years and suddenly won't connect to the database for this particular query.

By a process of elimination I have found that it has stopped because of 2 calculated fields in the query. When I changed these to a fixed value the rest of the query downloaded fine.

Now I have to rewrite the formula in Excel each time I refresh the imported table which is not convenient in a multi user environment.

Any ideas why the sudden change or how I can fix it , please??

 

[dhookom]

Can you share the SQL of the query?

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[Paul_W]

Thanks , yes ... the fields I had to remove are NoOfCriteria and Ward2

SELECT tblHouseholds.SFID, tblCurrentData.CaptureDate, tblCurrentData.CaptureType, tblCurrentData.Offending, tblCurrentData.Education, tblCurrentData.ChildHelp, tblCurrentData.OutOfWork, tblCurrentData.DV_DA, tblCurrentData.Health, -(([Offending]=-1)+([Education]=-1)+([ChildHelp]=-1)+([OutOfWork]=-1)+([DV_DA]=-1)+([Health]=-1)) AS NoOfCriteria, tblHouseholds.Ward, Left$(tblHouseholds.Ward,Len(tblHouseholds.Ward)-5) AS [Ward 2], tblHouseholds.Priority, tblHouseholds.Eligible, tblHouseholds.Archived, tblHouseholds.Closed, tblDistrict.District
FROM ((tblCurrentData RIGHT JOIN qryCurrentData_Latest ON tblCurrentData.CriteriaDataID = qryCurrentData_Latest.MaxOfCriteriaDataID) RIGHT JOIN tblHouseholds ON qryCurrentData_Latest.SFID = tblHouseholds.SFID) LEFT JOIN tblDistrict ON tblHouseholds.Ward = tblDistrict.Ward
WHERE (((tblHouseholds.Eligible)=Yes))
ORDER BY tblHouseholds.SFID, tblCurrentData.CaptureDate DESC;

 

[Andrzejek]

You may want to fully qualify the fields: [tt]Offending, Education, ChildHelp, OutOfWork, DV_DA, Health[/tt], i.e. state which table they are coming from.


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

I assume the criteria fields are Yes/No type. I would also make sure all of the Ward values are at least 5 characters or you will get an error.
I would try with these calculations:

ABS( [Offending] + [Education] + [ChildHelp] + [OutOfWork] + [DV_DA] + [Health]) AS NoOfCriteria, 

Left(tblHouseholds.Ward,Len(tblHouseholds.Ward)-5) AS [Ward 2],

I don't care for your table structure with data stored in column names however that would be another thread. I would create a child table of household criteria with possibly 6 records per household.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[Paul_W]

Thanks for your comments. Yes, it is a bit untidy!
The table structure is based on a hierarchy of data (and its history) built up from individuals into households hence the columns - it saves a lot of cross tabbing.

The query works fine in Access but it fails when importing to Excel - and this error only started yesterday with no change of design in the spreadsheet nor the database - that's what I don't understand. I could just copy and paste the data into the spreadsheet for analysis but I wanted to find out why it's happended

I'll qualify the fields fully and see if that makes any difference.

Thanks you!

 

[combo]

I reproduced your error having tblHouseholds.Ward)-5<0, query runs in access with errors in some rows, but the data can't be returned to excel due to connection problem. This was suggested by dhookom in his recent post.

combo

 

[dhookom]

Paul W,

Did you try my suggested changes and did you check for Ward values with less than 5 characters? Usually when something doesn't work that did work previously, I look at changes in data.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[Paul_W]

Hi Duane,

Yes! I found a bad data row where the table had been modified directly without the error checking in the form.

Problem solved. Thank you very much for your help

Paul