Hidden invalid charater in date field

  • Thread starter Thread starter JRE
  • Start date Start date
J

JRE

In Access 2002, I'm having a SQL Server upsizing problem with hidden invalid
characters in certain date fields (probably because of minute network
glitches during data entry).

I usually export the problem tables to Excel since Access will generate an
export error table and tell what field, row number, and error the problems
are on. In this case, there were no export errors. Looking at the data in a
raw text file export didn't reveal the hidden character either. Upsizing or
importing the table into SQL Server 2000 still fails.

Does anyone have any methods or tricks to clean date fields and remove
invalid or hidden characters?

Thanks,
Joe
 
Why Excel as the intermediate step?

I normally upsize directly from Access Tables to SQL 2K Tables without any
problem. Even though the zero reference dates for Access/JET and SQL are
different, the upsizing wizard (ODBC driver???) will take care of this and
upsize the date values correcly for you.
 
Date fields normally fail to upsize because SQL won't accept a date
that is valid in Access. Access dates are from January 1, 100 to
December 31, 9999. SQL Server dates are only from ???? 1900 ????

Just run a query on the table to select records where the date value
is less than 1904, and you will find most of the problems. If you
actually need historical dates, you will not be able to use the SQL
Server date fields and the upsizing wizard.

(david)
 
Thanks, but all the dates are all properly within range. The SQL Server Data
Transformation import wizard reports an invalid character. The offending
value looks to be a valid date in Access. After deleting the date in the
date field in Access and retyping it, the SQL Server import wizard will run
and stop on the next record that has an invalid character.
 
value looks to be a valid date in Access. After deleting the date in the

<Normally> because the date you see as 04/03/54 is actually something
like April 3, 2054, or April 3, 0354. Access date fields CANNOT hold
invalid characters, but if you were using a text field instead of a date
field, you can find all invalid entries by using a query like this:

"select ID from tbl where not isdate(tbl.strDate);"

(david)
 
just a question. Are you sure you have a "date/time" field. Date field
are actually number fields that represent the elapsed time since
specific time in history. I don't know how there could be invali
characters in a number field
 
The year is shown as four characters and all the years are valid and withing
range. Access date fields (and numeric fields too) can hold "hidden" invalid
characters (like non-printable ANSI control codes) if they slip in during
machine or network problems while editing is occurring (sometimes causing
data and/or database corruption). This is what I think happened.

Thanks for the tip on Not IsDate([DateField]). It found over 1,700 problem
date entries. Now if I can only find a way to clean them up in mass without
having the users to delete and retype each entry.
 
If you can identify the "bad" character, you could run an update query to
use the Replace function to remove that "bad" character.


--
Ken Snell
<MS ACCESS MVP>

JRE said:
The year is shown as four characters and all the years are valid and withing
range. Access date fields (and numeric fields too) can hold "hidden" invalid
characters (like non-printable ANSI control codes) if they slip in during
machine or network problems while editing is occurring (sometimes causing
data and/or database corruption). This is what I think happened.

Thanks for the tip on Not IsDate([DateField]). It found over 1,700 problem
date entries. Now if I can only find a way to clean them up in mass without
having the users to delete and retype each entry.

david epsom dot com dot au said:
the

<Normally> because the date you see as 04/03/54 is actually something
like April 3, 2054, or April 3, 0354. Access date fields CANNOT hold
invalid characters, but if you were using a text field instead of a date
field, you can find all invalid entries by using a query like this:

"select ID from tbl where not isdate(tbl.strDate);"

(david)


Server
Data will
run
Click to expand...
Click to expand...
 
Yes, the field has a Date/Time datatype. I've experienced non-printable
hidden characters slipping in before.
 
Yes, the field has a Date/Time datatype.
I've experienced non-printable
hidden characters slipping in before.
Click to expand...

The point was, and is, that a Date/Time field is not a character or text
field, so there's just no possibility of "hidden characters slipping in".

A Date/Time field is internally in the same format as a double-precision
floating point number with the whole-number part representing days since
Dec. 30, 1899, and the fractional part representing time since midnight. The
data only exists in character form _after_ it has been interpreted by Access
and formatted.

Larry Linson
Microsoft Access MVP
 
Sounds like a corruption of the program or OS files. In olden days you would
get unusual characters at the presence of a corrupted file and/or virus.
Have you ruled these two out yet by 1.) running an AV recovery disk 2.)
repairing/reinstalling the program - backup databases first?
 
I forgot to mention if there are improper instances of discontecting from
the db you will get corruption also - I thought that would be a given
though.
 
If the dates look good, you may be able to use an update, export
or create table query with the date first in text form, then back
to date/time. I think you have a corrupt database, so the upsize
to SQL Server has come none to soon.

As part of the upsize, you ALSO need to check for dates that are
valid in Jet, but not valid in SQL Server: there are often a few
(1 or 2) of these in even the best kept database.

(david)

Ken Snell said:
If you can identify the "bad" character, you could run an update query to
use the Replace function to remove that "bad" character.


--
Ken Snell
<MS ACCESS MVP>

JRE said:
The year is shown as four characters and all the years are valid and withing
range. Access date fields (and numeric fields too) can hold "hidden" invalid
characters (like non-printable ANSI control codes) if they slip in during
machine or network problems while editing is occurring (sometimes causing
data and/or database corruption). This is what I think happened.

Thanks for the tip on Not IsDate([DateField]). It found over 1,700 problem
date entries. Now if I can only find a way to clean them up in mass without
having the users to delete and retype each entry.

david epsom dot com dot au said:
value looks to be a valid date in Access. After deleting the date in the

<Normally> because the date you see as 04/03/54 is actually something
like April 3, 2054, or April 3, 0354. Access date fields CANNOT hold
invalid characters, but if you were using a text field instead of a date
field, you can find all invalid entries by using a query like this:

"select ID from tbl where not isdate(tbl.strDate);"

(david)


Thanks, but all the dates are all properly within range. The SQL Server
Data
Transformation import wizard reports an invalid character. The offending
value looks to be a valid date in Access. After deleting the date in the
date field in Access and retyping it, the SQL Server import wizard will
run
and stop on the next record that has an invalid character.

Date fields normally fail to upsize because SQL won't accept a date
that is valid in Access. Access dates are from January 1, 100 to
December 31, 9999. SQL Server dates are only from ???? 1900 ????

Just run a query on the table to select records where the date value
is less than 1904, and you will find most of the problems. If you
actually need historical dates, you will not be able to use the SQL
Server date fields and the upsizing wizard.

(david)


In Access 2002, I'm having a SQL Server upsizing problem with hidden
invalid
characters in certain date fields (probably because of minute network
glitches during data entry).

I usually export the problem tables to Excel since Access will
generate
an
export error table and tell what field, row number, and error the
problems
are on. In this case, there were no export errors. Looking at
Click to expand...
the
data
in
a
raw text file export didn't reveal the hidden character either.
Upsizing
or
importing the table into SQL Server 2000 still fails.

Does anyone have any methods or tricks to clean date fields and remove
invalid or hidden characters?

Thanks,
Joe
Click to expand...
Click to expand...
Click to expand...
 
1) Parse the [Date] field into three temporary text fields:

Left(Format([Date],"mm/dd/yyyy),2) into a text month field
Mid(Format([Date],"mm/dd/yyyy"),4,2) into a text day field
Right(Format([Date],"mm/dd/yyyy"),4) into a text year field

2) And then concatinate the three text fields into a new [NewDate] Date/Time
field:

[DateMM] & "/" & [DateDD] & "/" & [DateYYYY] into the new date field

3) Delete the bad [Date] field, delete the temporary fields, and rename the
[NewDate] field to the original fieldname.

4) Upsize to SQL Server

My reasoning was that maybe using the Left, Mid, and Right functions would
pick up only the digits I specified, and not the embedded problem (hidden
character or not). It did the trick. I know the technique can be tighter and
cleaner, but I had to do it reasonably quicky.

Thanks for everyone's suggestions
Joe
 
Back
Top