Formula won’t do it’s job on External Data Query

A

Adnan

Hi everyone, I have an External Data Query set up (data that I take from an
Access database with this query to excel). The query seams to work great but
when I insert a formula, they simply do not work.
i.e: I have data (numbers) in A2, A3, A4 and want to SUM them, formula is
accepted but will not sum them until I delete the data then type them up
manually. It seams as this query is either formatting cells on an unknowns
format (which shows no problem when I right-click it and see for formatting)
or it’s just not possible to use formulas on data that come from an External
Data Query set up.
Any help is greatly appreciated!
Thank you, Adnan
Computer Student Student, Kosovo
 
M

Mikeopolo

It certainly IS possible to add numbers arising from queries.

Are your numbers displaying right-aligned? If you r/mouse-Click and
choose Format, which option is high-lighted?

Can you edit your query, view the SQL, copy it and post it here?

Regards
Mike
 
A

Adnan

Thank you for quick response Mike,

My numbers are displaying left-aligned.

If I right-mouse-click and choose Format, I get General format (first top
category, which says general -- and it displays general on Custom category,
too)

I edited my query, viewed the SQL, copied and pasted it below:

SELECT tblUnscheduledServices.ID, tblUnscheduledServices.DPWNumber,
tblUnscheduledServices.DateReceived, tblUnscheduledServices.Priority,
tblUnscheduledServices.DepartmentAllocatedTo,
tblUnscheduledServices.TravelTime, tblUnscheduledServices.PreperationTime,
tblUnscheduledServices.WorkTime, tblUnscheduledServices.DailyHours,
tblUnscheduledServices.NumberOfEmployees,
tblUnscheduledServices.CumulativeHours
FROM tblUnscheduledServices tblUnscheduledServices


In addition to explanation above, please open the following link for better
explanation:
http://punaime.itgo.com/photo.html

Again, Thank you for taking the time to write to us!
Adnan
 
M

Mikeopolo

Your numbers being left-aligned means that they are either text a
source, or being treated as text somewhere along the way.

I suggest you check the field in the table definition, both in th
Access table itself, and open the query, go to File, Table Definition
and search for the table and field entry there.

Both definitions should be a numeric type. Post back the result.

Regards
Mik
 
A

Adnan

Outstanding, this is it! Thank you Mike,
I’ve checked the filed in the Access table and they were text --- I of
course changed them to number and now it works outstanding.

THANK YOU FOR YOUR GREAT HELP MIKE,

Cheers,
Adnan
 
P

Pam M

I have a question on this post--I am experiencing the same problem, and have
found in the table definition where the field is char, but I cannot modify it
to change it to a number because it is a download from an external accounting
package. Is there any other shortcut fix to this?
 
Top