Error when importing external data from Access
 

This is a very strange one, that i hope will be an enjoyable challenge to
someone!

I am trying to import data from a query i have made in Access (2003) into a
new worksheet in Excel (2003).

When running the query in Access, column "TE2" shows the correct data.
However, when i import this query into Excel, the same column shows incorrect
data and seems to duplicating whatever data is shown in the column to the
left - "TE1".

The SQL is witten as follows, if it helps:

"SELECT tblDates.Date, 1440-IIf(IsNull([1-TPT]),0,[1-TPT]) AS TPT,
1440-IIf(IsNull([1-NPT]),0,[1-NPT]) AS NPT,
((1440-IIf(IsNull([1-NPT]),0,[1-NPT]))/1440)*100 AS TE1,
IIf(((1440-IIf(IsNull([1-TPT]),0,[1-TPT]))=0),0,((1440-IIf(IsNull([1-NPT]),0,[1-NPT]))/(1440-IIf(IsNull([1-TPT]),0,[1-TPT])))*100)
AS TE2, tblDates.TE1_Target, tblDates.TE2_Target
FROM (tblDates LEFT JOIN qryNPTbyDay ON tblDates.Date = qryNPTbyDay.Date)
LEFT JOIN qryTPTbyDay ON tblDates.Date = qryTPTbyDay.Date
WHERE (((tblDates.Date) Between Now()-29 And Now()-1));"

My feeling is that it is something to do with the changing of Null results
to zeros because when i used the code "Nz" i couldn't even pick the query
from the list when importing into Excel.

I would really appreciate some help with this

Thanks in advance

Error when importing external data from Access
 

When you say "import this query into Excel", what are you importing? Are you
using Excel's MS Query tool to run the Access query? Or are you copying and
pasting the query data into an Excel spreadsheet?

If it's the latter that's causing you a problem maybe try to use the MS
Query tool (Data--Import External Data--New Database Query) to trigger the
query in the MS Access db....

Dave
--
Brevity is the soul of wit.


"Stephen Jame Pattenden" wrote:

This is a very strange one, that i hope will be an enjoyable challenge to
someone!

I am trying to import data from a query i have made in Access (2003) into a
new worksheet in Excel (2003).

When running the query in Access, column "TE2" shows the correct data.
However, when i import this query into Excel, the same column shows incorrect
data and seems to duplicating whatever data is shown in the column to the
left - "TE1".

The SQL is witten as follows, if it helps:

"SELECT tblDates.Date, 1440-IIf(IsNull([1-TPT]),0,[1-TPT]) AS TPT,
1440-IIf(IsNull([1-NPT]),0,[1-NPT]) AS NPT,
((1440-IIf(IsNull([1-NPT]),0,[1-NPT]))/1440)*100 AS TE1,
IIf(((1440-IIf(IsNull([1-TPT]),0,[1-TPT]))=0),0,((1440-IIf(IsNull([1-NPT]),0,[1-NPT]))/(1440-IIf(IsNull([1-TPT]),0,[1-TPT])))*100)
AS TE2, tblDates.TE1_Target, tblDates.TE2_Target
FROM (tblDates LEFT JOIN qryNPTbyDay ON tblDates.Date = qryNPTbyDay.Date)
LEFT JOIN qryTPTbyDay ON tblDates.Date = qryTPTbyDay.Date
WHERE (((tblDates.Date) Between Now()-29 And Now()-1));"

My feeling is that it is something to do with the changing of Null results
to zeros because when i used the code "Nz" i couldn't even pick the query
from the list when importing into Excel.

I would really appreciate some help with this

Thanks in advance