INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access query calculated fields not exporting to excel

Access query calculated fields not exporting to excel

(OP)
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??

RE: Access query calculated fields not exporting to excel

Can you share the SQL of the query?

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

RE: Access query calculated fields not exporting to excel

(OP)
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;

RE: Access query calculated fields not exporting to excel

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Access query calculated fields not exporting to excel

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:

CODE --> sql

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

RE: Access query calculated fields not exporting to excel

(OP)
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!

RE: Access query calculated fields not exporting to excel

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

RE: Access query calculated fields not exporting to excel

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

RE: Access query calculated fields not exporting to excel

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close