<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1226" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2>There are two dups in the data You're
attempting to add</FONT></DIV>
<DIV><FONT face="Courier New" size=2>[ here they are ]</FONT></DIV><FONT
face="Courier New" size=2>
<DIV>
<TABLE cellSpacing=0 bgColor=#ffffff border=1><FONT face="Courier New"
color=#000000>
<CAPTION><B></B></CAPTION></FONT>
<THEAD>
<TR>
<TH borderColor=#000000 bgColor=#c0c0c0><FONT face="Courier New"
color=#000000 size=2>bib</FONT></TH>
<TH borderColor=#000000 bgColor=#c0c0c0><FONT face="Courier New"
color=#000000 size=2>day</FONT></TH>
<TH borderColor=#000000 bgColor=#c0c0c0><FONT face="Courier New"
color=#000000 size=2>ConvertedTime</FONT></TH>
<TH borderColor=#000000 bgColor=#c0c0c0><FONT face="Courier New"
color=#000000 size=2>ConvertedActivity</FONT></TH>
<TH borderColor=#000000 bgColor=#c0c0c0><FONT face="Courier New"
color=#000000 size=2>ConvertedCkpoint</FONT></TH>
<TH borderColor=#000000 bgColor=#c0c0c0><FONT face="Courier New"
color=#000000 size=2>cnt</FONT></TH></TR></THEAD>
<TBODY>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT face="Courier New" color=#000000
size=2>58</FONT></TD>
<TD borderColor=#c0c0c0 align=right><FONT face="Courier New" color=#000000
size=2>1</FONT></TD>
<TD borderColor=#c0c0c0 align=right><FONT face="Courier New" color=#000000
size=2>11:38:00 AM</FONT></TD>
<TD borderColor=#c0c0c0 align=right><FONT face="Courier New" color=#000000
size=2>2</FONT></TD>
<TD borderColor=#c0c0c0 align=right><FONT face="Courier New" color=#000000
size=2>4</FONT></TD>
<TD borderColor=#c0c0c0 align=right><FONT face="Courier New" color=#000000
size=2>2</FONT></TD></TR>
<TR vAlign=top>
<TD borderColor=#c0c0c0 align=right><FONT face="Courier New" color=#000000
size=2>117</FONT></TD>
<TD borderColor=#c0c0c0 align=right><FONT face="Courier New" color=#000000
size=2>1</FONT></TD>
<TD borderColor=#c0c0c0 align=right><FONT face="Courier New" color=#000000
size=2>6:56:00 AM</FONT></TD>
<TD borderColor=#c0c0c0 align=right><FONT face="Courier New" color=#000000
size=2>3</FONT></TD>
<TD borderColor=#c0c0c0 align=right><FONT face="Courier New" color=#000000
size=2>2</FONT></TD>
<TD borderColor=#c0c0c0 align=right><FONT face="Courier New" color=#000000
size=2>2</FONT></TD></TR></TBODY>
<TFOOT></TFOOT></TABLE></DIV>
<DIV> </DIV>
<DIV>After modifying the functions I previously mentioned,I:</DIV>
<DIV>1 -Removed referencial integrity in the relationships.</DIV></FONT>
<DIV><FONT face="Courier New" size=2>2 -deleted all records</FONT></DIV>
<DIV><FONT face="Courier New" size=2>3 -then ran the query. the only error msg
was about the</FONT></DIV>
<DIV><FONT face="Courier New" size=2> two duplicates.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Courier New" size=2>Dave</FONT> </DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=davesharpe2@cox.net href="mailto:davesharpe2@cox.net">dave sharpe</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">Access Developers discussion and
problem solving</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Saturday, September 27, 2003 7:41
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [AccessD] Urgent help needed
from race finish line</DIV>
<DIV><BR></DIV>
<DIV><FONT face="Courier New" size=2>Kathryn</FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2>A couple of things I
noticed.</FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2>1 - I don't know how to
addess.<BR> You have relationships set to
require<BR> a record in other tables. It will
not<BR> allow Me to even manually enter a new
record<BR> to it.</FONT></DIV><FONT face="Courier New"
size=2>
<DIV><BR>2 - I noted that if I make Your query a MakeTable
one,<BR> some types don't match the existing table's (
forgot </DIV>
<DIV> which ones and I've modified the code already).Some
</DIV>
<DIV> of the functions were
defined incompletely </DIV>
<DIV> [ ex "Function FixActivity(TheLetter As
String)"<BR> doesn't have "As Integer" ]</DIV>
<DIV> </DIV>
<DIV>These don't fix it though.</DIV>
<DIV> </DIV>
<DIV>Dave</DIV>
<DIV></FONT> </DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2>----- Original Message -----
</FONT></DIV>
<DIV><FONT face="Courier New" size=2>From: "Kathryn Bassett" <</FONT><A
href="mailto:kathryn@bassett.net"><FONT face="Courier New"
size=2>kathryn@bassett.net</FONT></A><FONT face="Courier New"
size=2>></FONT></DIV>
<DIV><FONT face="Courier New" size=2>To: "Access Developers discussion and
problem solving" <</FONT><A
href="mailto:accessd@databaseadvisors.com"><FONT face="Courier New"
size=2>accessd@databaseadvisors.com</FONT></A><FONT face="Courier New"
size=2>></FONT></DIV>
<DIV><FONT face="Courier New" size=2>Sent: Saturday, September 27, 2003 7:09
PM</FONT></DIV>
<DIV><FONT face="Courier New" size=2>Subject: RE: [AccessD] Urgent help needed
from race finish line</FONT></DIV>
<DIV><FONT face="Courier New"><BR><FONT size=2></FONT></FONT></DIV><FONT
face="Courier New" size=2>> I know how to check the table for the time data
type, but can't figure out how to tell about the converted time. I suspect
that may be the problem, though Rocky suggested something else. Weird thing is
that this worked last race. Only change was addition of this module:<BR>>
<BR>> Function MakeTime(strTime)<BR>> ' Works with
4-digit or 6-digit times<BR>> ' "0637" is interpreted as
6:37<BR>> ' "063706" is interpreted as 6:37:06<BR>>
' WARNING: Unpredictable results if any other length string
is fed in<BR>> <BR>> If
Len(strTime) = 4 Then<BR>>
MakeTime = TimeValue(Left(strTime, 2) & ":" & Right(strTime,
2))<BR>> Else<BR>>
MakeTime = TimeValue(Left(strTime,
2) & ":" & _<BR>>
Mid(strTime, 3, 2) & ":" & Right(strTime, 2))<BR>>
End If<BR>> <BR>> I don't see any time fields other
than 4 characters in Sample1, unless I'm overlooking something.<BR>>
<BR>> --<BR>> Kathryn Bassett (AC100 Finish Line webmaster)<BR>>
</FONT><A href="mailto:kathryn@bassett.net"><FONT face="Courier New"
size=2>kathryn@bassett.net</FONT></A><BR><FONT face="Courier New" size=2>>
</FONT><A href="http://ac100.com"><FONT face="Courier New"
size=2>http://ac100.com</FONT></A><FONT face="Courier New" size=2> - Race
Site<BR>> </FONT><A href="http://ac100.com/finish"><FONT face="Courier New"
size=2>http://ac100.com/finish</FONT></A><FONT face="Courier New" size=2> -
Finish Line<BR>> </FONT><A href="http://bassett.net"><FONT
face="Courier New" size=2>http://bassett.net</FONT></A><FONT
face="Courier New" size=2> - personal website<BR>> <BR>> >
-----Original Message-----<BR>> > From: </FONT><A
href="mailto:accessd-bounces@databaseadvisors.com"><FONT face="Courier New"
size=2>accessd-bounces@databaseadvisors.com</FONT></A><BR><FONT
face="Courier New" size=2>> >
[mailto:accessd-bounces@databaseadvisors.com]On Behalf Of Susan
Harkins<BR>> > Sent: 27 Sep 2003 3:31:PM<BR>> > To: Access
Developers discussion and problem solving<BR>> > Subject: Re: [AccessD]
Urgent help needed from race finish line<BR>> > <BR>> > <BR>>
> If the converted time isn't a valid date/time data type and the table
field<BR>> > is a Date/Time type, then yes, that could certainly be the
problem. This<BR>> > could be the problem with any field in the query
though -- the data types<BR>> > must match.<BR>> > <BR>> >
Also, check the Required property -- if the table requires a value and
the<BR>> > append isn't supplying one, you'll get that error.<BR>>
> <BR>> > If the records you're appending duplicate existing key
values, you'll get<BR>> > this error.<BR>> > <BR>> > Susan
H.<BR>> > <BR>> > ----- Original Message -----<BR>> > From:
"Kathryn Bassett" <</FONT><A href="mailto:kathryn@bassett.net"><FONT
face="Courier New" size=2>kathryn@bassett.net</FONT></A><FONT
face="Courier New" size=2>><BR>> > To: "dbAdvisors (AccessD)"
<</FONT><A href="mailto:accessd@databaseadvisors.com"><FONT
face="Courier New" size=2>accessd@databaseadvisors.com</FONT></A><FONT
face="Courier New" size=2>>; "Access OT list -<BR>> > dba"
<</FONT><A href="mailto:dba-OT@databaseadvisors.com"><FONT
face="Courier New" size=2>dba-OT@databaseadvisors.com</FONT></A><FONT
face="Courier New" size=2>><BR>> > Sent: Saturday, September 27, 2003
6:20 PM<BR>> > Subject: [AccessD] Urgent help needed from race finish
line<BR>> > <BR>> > <BR>> > > tblSample1<BR>> >
> ID - autonumber<BR>> > > activity - text<BR>> > > bib -
number<BR>> > > timetext - text<BR>> > > day -
number<BR>> > > checkpoint - text<BR>> > ><BR>> > >
This query works:<BR>> > > SELECT Sample1.activity, Sample1.bib,
Sample1.timetext, Sample1.day,<BR>> > Sample1.checkpoint,
MakeTime([timetext]) AS ConvertedTime,<BR>> > FixActivity([activity]) AS
ConvertedActivity, FixCkpoint([checkpoint]) AS<BR>> >
ConvertedCkpoint<BR>> > > FROM Sample1;<BR>> > ><BR>>
> > But the next query that gets run is this code:<BR>> > >
INSERT INTO tblRunnerTimes ( [RT-IDBib], [RT-Day], [RT-Time],
[RT-IDAC],<BR>> > [RT-IDCP] )<BR>> > > SELECT
qryChangeNewData.bib, qryChangeNewData.day,<BR>> >
qryChangeNewData.ConvertedTime, qryChangeNewData.ConvertedActivity,<BR>>
> qryChangeNewData.ConvertedCkpoint<BR>> > > FROM
qryChangeNewData;<BR>> > ><BR>> > > and it gives me
"Microsoft Access can't append all the records in the<BR>> > append
query."<BR>> > > Microsoft Access set 0 fields to Null due to a type
converstion failur,<BR>> > and it didn't add 694 records to the table
due to key violations, 0 records<BR>> > due to lock violations, and 0
records due to validation rule violations.<BR>> > ><BR>> > >
I suspect it's got something to do with the time conversion but I
can't<BR>> > figure out what. I've going nuts for more than two hours
trying to figure<BR>> > out what's wrong. Should have had the first
results online 4 hours ago, and<BR>> > have people emailing me wanting
to know where their runners are.<BR>> > > HHEEELLLLPPPPP!!!!<BR>>
> > </FONT><A href="http://ac100.com/finish/2003Ac100.zip"><FONT
face="Courier New"
size=2>http://ac100.com/finish/2003Ac100.zip</FONT></A><BR><FONT
face="Courier New" size=2>> > > the mdb is A2k - just tell me what to
change.<BR>> > ><BR>> > > --<BR>> > > Kathryn
Bassett (AC100 Finish Line webmaster)<BR>> > > </FONT><A
href="mailto:kathryn@bassett.net"><FONT face="Courier New"
size=2>kathryn@bassett.net</FONT></A><BR><FONT face="Courier New" size=2>>
> > </FONT><A href="http://ac100.com"><FONT face="Courier New"
size=2>http://ac100.com</FONT></A><FONT face="Courier New" size=2> - Race
Site<BR>> > > </FONT><A href="http://ac100.com/finish"><FONT
face="Courier New" size=2>http://ac100.com/finish</FONT></A><FONT
face="Courier New" size=2> - Finish Line<BR>> > > </FONT><A
href="http://bassett.net"><FONT face="Courier New"
size=2>http://bassett.net</FONT></A><FONT face="Courier New" size=2> -
personal website<BR>> > ><BR>> > >
_______________________________________________<BR>> > > AccessD
mailing list<BR>> > > </FONT><A
href="mailto:AccessD@databaseadvisors.com"><FONT face="Courier New"
size=2>AccessD@databaseadvisors.com</FONT></A><BR><FONT face="Courier New"
size=2>> > > </FONT><A
href="http://databaseadvisors.com/mailman/listinfo/accessd"><FONT
face="Courier New"
size=2>http://databaseadvisors.com/mailman/listinfo/accessd</FONT></A><BR><FONT
face="Courier New" size=2>> > > Website: </FONT><A
href="http://www.databaseadvisors.com"><FONT face="Courier New"
size=2>http://www.databaseadvisors.com</FONT></A><BR><FONT face="Courier New"
size=2>> > ><BR>> > <BR>> >
_______________________________________________<BR>> > AccessD mailing
list<BR>> > </FONT><A href="mailto:AccessD@databaseadvisors.com"><FONT
face="Courier New" size=2>AccessD@databaseadvisors.com</FONT></A><BR><FONT
face="Courier New" size=2>> > </FONT><A
href="http://databaseadvisors.com/mailman/listinfo/accessd"><FONT
face="Courier New"
size=2>http://databaseadvisors.com/mailman/listinfo/accessd</FONT></A><BR><FONT
face="Courier New" size=2>> > Website: </FONT><A
href="http://www.databaseadvisors.com"><FONT face="Courier New"
size=2>http://www.databaseadvisors.com</FONT></A><BR><FONT face="Courier New"
size=2>> <BR>> _______________________________________________<BR>>
AccessD mailing list<BR>> </FONT><A
href="mailto:AccessD@databaseadvisors.com"><FONT face="Courier New"
size=2>AccessD@databaseadvisors.com</FONT></A><BR><FONT face="Courier New"
size=2>> </FONT><A
href="http://databaseadvisors.com/mailman/listinfo/accessd"><FONT
face="Courier New"
size=2>http://databaseadvisors.com/mailman/listinfo/accessd</FONT></A><BR><FONT
face="Courier New" size=2>> Website: </FONT><A
href="http://www.databaseadvisors.com"><FONT face="Courier New"
size=2>http://www.databaseadvisors.com</FONT></A>
<P>
<HR>
<P></P>_______________________________________________<BR>AccessD mailing
list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website:
http://www.databaseadvisors.com<BR></BLOCKQUOTE></BODY></HTML>