Solved - RE: [AccessD] Urgent help needed from race finish line

Kathryn Bassett kathryn at bassett.net
Sat Sep 27 19:17:40 CDT 2003


No, and the problem turned out NOT to be the time, much to my surprise. It had to do with the foreign key. Rocky can probably explain better what happened (thanks for letting me call, and talking me throught this Rocky). I would have been fine if I had done what I usually do at the begining of the race and compact before starting the year. Usually, my BibID (primary key) and my Bib are identical, but since I was playing, they were different. Now, to get back to updating the web page.

Thanks

--
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 4:30:PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Urgent help needed from race finish line
> 
> 
> Is it possible that MakTime is ever blank?
> 
> Susan H.
> 
> 
> > 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
> >
> > _______________________________________________
> > 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



More information about the AccessD mailing list