Parameter Query Numeric Field Overflow

[garvock]

Hi
I am new to using Access. I have an Excel table linked to Access. I have setup several queries and reports with no problems.I am trying to set up a Parameter query using date range " between [start date] and [end date]" but I get a numeric overflow error.
I think it may be because the column in the Excel table has dates but where the cell is blank there is still a formula in the cell.
I am sure the solution is obvious but please help.

Thanks

 

[Steve101]

I'm guessing here, but if your date expression is interpreted as a numeric expression, then the "date" 01/01/00 (ie. 1st Jan 2000) could be interpreted as [blue]1 divided by 1 divided by zero[/blue]. The division by zero would result in an arithmetic overflow.

Remember in access to use the correct date syntax; surround dates by # symbols, or use the CVDate function; eg. for the '01/01/00' example, in your query you would use:

#01/01/00#
or
CVDate("01/01/00&quot

Hope this helps,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[garvock]

I think I have found the problem!
The column in Excel has a formula. If the cell is blank the link to Access has a "#NUM!" entry in the cell. This is causing a datatype mismatch.
When I run a query with "between #1/1/2003# and #31/12/2003#" as the criteria it runs OK.
When I run a parameter query with "between[start date]and[end date]" I get the overflow error.
Help!