Your initial sum is wrong. It should be referencing the field names from the inner Union query (amt) not FinalAmount, which doesn't exist.

It should be:

select a.CountryName, Sum(a.amt) as Total

from...

As far as I know MSQuery only works with named ranges. That is all I've ever used.

Your Welcome! Glad I could help.

FYI - The C:\TestSource ref. was a separate workbook for data; wasn't sure of your setup and wouldn't be required for a single workbook as...

I had that same problem and it seemed to give me that when I had syntax errors elsewhere in the query. I would double check everything.

You could do something like the following:

select a.Country, Sum(a.Amt) as Total

from (

SELECT TheBigData.Country as Country, TheBigData.amt as amt

FROM `C:\DataTestSource.xlsx`.TheBigData TheBigData

union all

SELECT TheLittleData.Country as Country, TheLittleData.amt as amt

...

The other thing you may be running into is that the AS400 requires all tables accessed for anything but reading need to be journalled. If the table(s) you run only...

This seems to work for all combinations of time values.

Create Table #test (

TestDate varchar(9)

)

Insert into #test

Select '0'

union

select '01:00:22'

union

select '39:00'

union

select '02:19'

union

select '-03:06'

Select TestDate

...

order by convert(decimal(5,2),REPLACE(TestDate,':','.'))

Doesn't work for hours:min:sec either: Error converting data type varchar to numeric

What about a calculated field based on the decimal portion of the internal time representation. Such as:

Create Table #test (

TestDate varchar(9)

)

Insert into #test

Select '0'

union

select '00:22'

union

select '00:39'

union

select '02:19'

union

select '-03:06'

Select TestDate,...

Do you really need the data in Access?

If not, you could setup a linked table(s) to the source data in SQL and design the report on those tables.

You're welcome! Glad I could help!

Thx for the feedback.

Sorry, missed that.

Looking at the existing functions there isn't one to use. I think you would have to write your own function and do some kind of char. by char....

While I have never worked with MySQL, looking at the function syntax for it you could use the REGEXP function which looks very close to PATINDEX.

I'm assuming from the .XYZ file extension that the file is on the IFS file system not the native file system as there is no extension on the native.

To FTP...

I have been using the following to convert to JDE:

Select (DATEPART(yy, getdate()) - 1900) * 1000 + DATEPART(dy, getdate())