<!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>
<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></BODY></HTML>