dave sharpe
davesharpe2 at cox.net
Sat Sep 27 19:18:19 CDT 2003
There are two dups in the data You're attempting to add
[ here they are ]
bib day ConvertedTime ConvertedActivity ConvertedCkpoint cnt
58 1 11:38:00 AM 2 4 2
117 1 6:56:00 AM 3 2 2
After modifying the functions I previously mentioned,I:
1 -Removed referencial integrity in the relationships.
2 -deleted all records
3 -then ran the query. the only error msg was about the
two duplicates.
Dave
----- Original Message -----
From: dave sharpe
To: Access Developers discussion and problem solving
Sent: Saturday, September 27, 2003 7:41 PM
Subject: Re: [AccessD] Urgent help needed from race finish line
Kathryn
A couple of things I noticed.
1 - I don't know how to addess.
You have relationships set to require
a record in other tables. It will not
allow Me to even manually enter a new record
to it.
2 - I noted that if I make Your query a MakeTable one,
some types don't match the existing table's ( forgot
which ones and I've modified the code already).Some
of the functions were defined incompletely
[ ex "Function FixActivity(TheLetter As String)"
doesn't have "As Integer" ]
These don't fix it though.
Dave
----- Original Message -----
From: "Kathryn Bassett" <kathryn at bassett.net>
To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
Sent: Saturday, September 27, 2003 7:09 PM
Subject: RE: [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
>
> _______________________________________________
> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030927/5cf6b1b3/attachment-0001.html>