Cant get around a #ERROR in Table

[JAD]

I imported a table from Dbase and there is a date column that has a bunch of #Error for values. I cant copy the table or do anything if I am trying to access one of these records. I want to copy the table to a new table using a Query/Make table. What type of expression can I use that would say New_Date=if date is #error then "blank" else "use the date that is in the table"
Can't find any examples for this.
Thanks in advance!!!

 

[jeremie_ingram]

On the import, do you have that field set for a date? Can you change it to text? The chances are that someone entered the date ouside of the normal convention.

But, as you were asking, you can use an update query to change the #error# to blank. Create a query based off of the tbl, add the fields, then view in datasheet. Everything should be there. Now add the criteria "#error#" to the field in question and view the results in datasheet view. If everything looks good to this point, change the query type to update, and in the UPDATE TO row place NULL under the field in question. Run the query and it will take out the errors and leave them empty. Since this is a date formatted field, you should leave it at null.

 

[JAD]

Sounds good but did not work

I tried different ways of entering the error in and it does not like the format of the "#error#" - It gives me a expression entered had invalid date value" or "Data type mismatch in crigteria expression"

 

[JAD]

Also...

When I imported the table I had to do it as a link. It would not let me import the table by itself. In that type of link I can't specify the data type of the fields.

 

[jeremie_ingram]

Ok, then this is imported data that does not fit the data type. Say they needed the date and Access is looking for 2/2/2003, and they enter in Feb, 2 2003. That will generate the error. Try importing the data into a different tbl but format that field as text. Take a look at what data appears and see where the errors occure.

SORRY, MISSED THE SECOND POST

Could you create a tbl and import the data from that source into it? Or just import the data, not link the tbl?

 

[JAD]

Error

I have no control over the table I'm importing. I have to do a link to the Dbase table because the "Import" gives me an error. I suspect it is because of the date field errors. I can't go into Dbase and fix the date errors because I dont have the software. The "link tables" will let me see and view the table but if I hit on one of the records with the Error in the date field that it can't read I get the errors mentioned above.
I think my only option is to find a way to write a query to build a new table and where ever it sees the Error in that date field then ignore it and make it blank.
Thanks for your time!
Jim

 

[JAD]

EXTERNAL DATABASE DRIVER 10019

This is my error I get when I try to import the data:
EXTERNAL DATABASE DRIVER 10019
Can't find any help @ the microsoft site. Guess I'm hosed!

 

[boblarson]

Is there anyway to export from the dBase table instead of importing from Access? If you can export from dBase to a comma-delimited text file, then you can import that from Access using Import specifications.

 

[JAD]

I tried Excel too but it would not bring the data in past the bad Data record. I finally got it to work by creating a new table w/o the date field, then was able to copy the column of partial bad data into excel with the key and do a update from that. I had to leave my finger on the escape key for 10 minutes while it copied to clear the error message (but it worked!) Glad there was only a cuple of thousand records.