[AccessD] Urgent help needed from race finish line

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:

Function MakeTime(strTime)
    ' Works with 4-digit or 6-digit times
    ' "0637" is interpreted as 6:37
    ' "063706" is interpreted as 6:37:06
    ' WARNING: Unpredictable results if any other length string is fed in
    
    If Len(strTime) = 4 Then
        MakeTime = TimeValue(Left(strTime, 2) & ":" & Right(strTime, 2))
    Else
        MakeTime = TimeValue(Left(strTime, 2) & ":" & _
         Mid(strTime, 3, 2) & ":" & Right(strTime, 2))
    End If

I don't see any time fields other than 4 characters in Sample1, unless I'm overlooking something.

--
Kathryn Bassett (AC100 Finish Line webmaster)
kathryn at bassett.net
http://ac100.com - Race Site
http://ac100.com/finish - Finish Line
http://bassett.net - personal website

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Susan Harkins
> Sent: 27 Sep 2003 3:31:PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Urgent help needed from race finish line
> 
> 
> If the converted time isn't a valid date/time data type and the table field
> is a Date/Time type, then yes, that could certainly be the problem. This
> could be the problem with any field in the query though -- the data types
> must match.
> 
> Also, check the Required property -- if the table requires a value and the
> append isn't supplying one, you'll get that error.
> 
> If the records you're appending duplicate existing key values, you'll get
> this error.
> 
> Susan H.
> 
> ----- Original Message -----
> From: "Kathryn Bassett" <kathryn at bassett.net>
> To: "dbAdvisors (AccessD)" <accessd at databaseadvisors.com>; "Access OT list -
> dba" <dba-OT at databaseadvisors.com>
> Sent: Saturday, September 27, 2003 6:20 PM
> Subject: [AccessD] Urgent help needed from race finish line
> 
> 
> > tblSample1
> > ID - autonumber
> > activity - text
> > bib - number
> > timetext - text
> > day - number
> > checkpoint - text
> >
> > This query works:
> > SELECT Sample1.activity, Sample1.bib, Sample1.timetext, Sample1.day,
> Sample1.checkpoint, MakeTime([timetext]) AS ConvertedTime,
> FixActivity([activity]) AS ConvertedActivity, FixCkpoint([checkpoint]) AS
> ConvertedCkpoint
> > FROM Sample1;
> >
> > But the next query that gets run is this code:
> > INSERT INTO tblRunnerTimes ( [RT-IDBib], [RT-Day], [RT-Time], [RT-IDAC],
> [RT-IDCP] )
> > SELECT qryChangeNewData.bib, qryChangeNewData.day,
> qryChangeNewData.ConvertedTime, qryChangeNewData.ConvertedActivity,
> qryChangeNewData.ConvertedCkpoint
> > FROM qryChangeNewData;
> >
> > and it gives me "Microsoft Access can't append all the records in the
> append query."
> > Microsoft Access set 0 fields to Null due to a type converstion failur,
> and it didn't add 694 records to the table due to key violations, 0 records
> due to lock violations, and 0 records due to validation rule violations.
> >
> > I suspect it's got something to do with the time conversion but I can't
> figure out what. I've going nuts for more than two hours trying to figure
> out what's wrong. Should have had the first results online 4 hours ago, and
> have people emailing me wanting to know where their runners are.
> > HHEEELLLLPPPPP!!!!
> > http://ac100.com/finish/2003Ac100.zip
> > the mdb is A2k - just tell me what to change.
> >
> > --
> > Kathryn Bassett (AC100 Finish Line webmaster)
> > kathryn at bassett.net
> > http://ac100.com - Race Site
> > http://ac100.com/finish - Finish Line
> > http://bassett.net - personal website
> >
> > _______________________________________________
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com