Numeric Field Overflow

[Rendbre]

Hi guys,

First time posting on the forums, and I'm really hoping that you can help me.

I have a database that pulls in a a text file as a table and I have set up an append query to take some data each day and put it in a new table (the original file updates daily).

I only want to select today's data and I have put a paramater on the query to force me to select a date, however when I run it I get the message 'Numeric Field Overflow' and no data appends.

Any ideas as to what has gone wrong?

Cheers,

 

[DCrake]

Could be you need to change your field type to Long instead of Integer.

David

 

[Rendbre]

I had a look at that but it is a date/time field. I checked the original linked table and the new table that is added to each day and all of the options are set the same. The query just doesn't seem to work.

 

[Brianwarnock]

Possibly blanks in the date field, not Nulls or zero length but blanks.

Brian

 

[Rendbre]

Thanks, I'll check through the data.

Is there a way around this problem?

 

[Brianwarnock]

I don't know a way round it in ACCESS I think you have to change the blank to a Null or empty string in the data before Access gets it. I should have mentioned that in ACCESS it will probably show as #num!

Brian

 

[gemma-the-husky]

the way to do this is probably to import the file, and then validate the data in some way - to force it to be cleaned up properly - there must be something wrong with the data - can you not see whats wrong with it by inspection?

eg, import each column as text, and have a validation query that does isdate(column) on the suspect column to see if there are any badly formed dates.

it depends where the data comes from to some extent - i find that if you use a csv or fixed width file, directly off a computer system then everything should be ok. once people manipulate files in excel first, then you have problems, as they can inadvetrtently mess things up.

It can take quite a while to sort out robust import procedures for error-prone data - but once it is done, it will work forever.

 

[Brianwarnock]

Dave

If the problem is caused by the import of a blank, not "" or Null but " " , date field it shows up as #num! in Access and I have never found a way to correct that, I have always had to clean up the data at source, usually Excel as per another thread recently, But I'm willing to learn.

Brian