Numeric field overflow error on date field

[Guest]

Using Access 2003, I have a table linked to an Excel spreadsheet. There is a
date field on the spreadsheet formatted in Excel as m/dd/yyyy. When I run a
query against the table checking if the date field "Is Null", it gives me a
"Numeric field overflow" error. How can I fix this? Thanks, Sheri

 

[Guest]

Please post the SQL so that we can see what all is going on.

 

[Guest]

SELECT [Customer Survey Data].DATE, [Customer Survey Data].CSR, [Customer
Survey Data].[ZONE (E, D, C, A) (CSR)], [Customer Survey Data].[CONTACT
NAME], [Customer Survey Data].[COMPANY NAME], [Customer Survey Data].[EMAIL
ADDRESS], [Customer Survey Data].[ORDER #], [Customer Survey Data].COMMENTS,
[Customer Survey Data].[CUSTOMER PO#], [Customer Survey Data].SALESPERSON,
[Customer Survey Data].[Send Survey?Y/N (PK)], [Customer Survey
Data].[RESPONSE REC# Y/N (PK)], [Customer Survey Data].[SHIP DATE (CSR fill
in)], [Customer Survey Data].[SURVEY ISSUE DATE (PK)]
FROM [Customer Survey Data]
WHERE ((([Customer Survey Data].DATE) Between [From Date:] And [To Date:])
AND (([Customer Survey Data].[Send Survey?Y/N (PK)])<>'N') AND (([Customer
Survey Data].[RESPONSE REC# Y/N (PK)]) Is Null) AND (([Customer Survey
Data].[SHIP DATE (CSR fill in)]) Is Not Null) AND (([Customer Survey
Data].[SURVEY ISSUE DATE (PK)]) Is Null));

The error is with the SURVEY ISSUE DATE field.

 

[Guest]

It's very possible that the problem isn't with the SURVEY ISSUE DATE field at
all. Access tends to read the Where clause of the SQL from the bottom up and
that's just where the errors stops. It could actually be something above it.

What happens when you remove the last line of your Where clause? Do you
still get an error or just more records that you want? I ran the following in
A03 with no problems.

WHERE ((([qryExcelOutput].[DateReceived]) Is Null));

If it is a problem with that field, you could try something like:

and NZ([Customer Survey Data].[SURVEY ISSUE DATE (PK)], 0) = 0 ;

Also I always highly recommend to never, ever use any special characters in
table, field, form, report, or query names. Not even the space. The
underscore " _ " is acceptable. The way your things are named, Access could
very easily mess up with the special characters or even reserved words like
"From". Putting things inside of the square brackets "[ ]" should stop this
from happening but I've seen Access mess up.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

SELECT [Customer Survey Data].DATE, [Customer Survey Data].CSR, [Customer
Survey Data].[ZONE (E, D, C, A) (CSR)], [Customer Survey Data].[CONTACT
NAME], [Customer Survey Data].[COMPANY NAME], [Customer Survey Data].[EMAIL
ADDRESS], [Customer Survey Data].[ORDER #], [Customer Survey Data].COMMENTS,
[Customer Survey Data].[CUSTOMER PO#], [Customer Survey Data].SALESPERSON,
[Customer Survey Data].[Send Survey?Y/N (PK)], [Customer Survey
Data].[RESPONSE REC# Y/N (PK)], [Customer Survey Data].[SHIP DATE (CSR fill
in)], [Customer Survey Data].[SURVEY ISSUE DATE (PK)]
FROM [Customer Survey Data]
WHERE ((([Customer Survey Data].DATE) Between [From Date:] And [To Date:])
AND (([Customer Survey Data].[Send Survey?Y/N (PK)])<>'N') AND (([Customer
Survey Data].[RESPONSE REC# Y/N (PK)]) Is Null) AND (([Customer Survey
Data].[SHIP DATE (CSR fill in)]) Is Not Null) AND (([Customer Survey
Data].[SURVEY ISSUE DATE (PK)]) Is Null));

The error is with the SURVEY ISSUE DATE field.

Please post the SQL so that we can see what all is going on.