"Numeric field overflow" select query on linked Excel spreadsheet

[coyote1]

Here is the latest revision of my query (SQL view) linking to the Excel spreadsheet

SELECT Sum(linkPNOrders.E) AS [WEB Site Orders], Sum(linkPNOrders.T) AS [Telephone Orders], Sum(linkPNOrders.F) AS [Fax Orders], Sum(linkPNOrders.M) AS [Mail Orders], Count(*) AS [Count Of Orders]
FROM linkPNOrders
WHERE (((linkPNOrders.[DATE of Order]) Between [Enter Start Date] And [Enter End Date]));

I also removed the "Date of Order" field in the query and get same results. Also noted that the cells in the linked spreadsheet are formatted as "currency" and the linked table fields is as well.

Thanks for any input

Karl

 

[PHV]

You may try this:
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Sum(CCur(E)) AS [WEB Site Orders], Sum(CCur(T)) AS [Telephone Orders], Sum(CCur(F)) AS [Fax Orders], Sum(CCur(M)) AS [Mail Orders], Count(*) AS [Count Of Orders]
FROM linkPNOrders
WHERE [DATE of Order] Between [Enter Start Date] And [Enter End Date];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[coyote1]

Thanks for your time but I found the problem. I had one date value in the Excel spreadsheet that was entered wrong. I looked before but had over 8000 rows and must have missed it.
Please see link below for reference:

http://support.microsoft.com/default.aspx?scid=kb;en-us;815277

Karl