Terri Jarus
jarus at amerinet-gpo.com
Mon Feb 7 08:50:45 CST 2005
You knew it wouldn't go well straight out. It runs through the first
time just fine and adds the ContractId to the new table - when it loops
through it errors out (Run time 3070)- "The Microsoft Jet Database
Engine does not recognize 'AS90165' as a valid field name or
expression."
AS90165 is the ContractId - the next line in the In table is the same
ContractId with a new memo field. Any thoughts on this problem? Is it
a string issue? Do I need quotes somewhere?
Thanks for taking the time to work this out with me
Terri Jarus
Vice President, Contract Services
jarus at amerinet-gpo.com
314-542-1902
>>> andy at minstersystems.co.uk 2/7/2005 6:44:08 AM >>>
Here's a function to update the table Terri. It assumes that you've
already
created the output table with the appropriate field names. You also
need to
add error handling.
=====================================
Function Terri()
Dim db As Database
Dim rstIn As Recordset
Dim rstOut As Recordset
Set db = CurrentDb
Set rstIn = db.OpenRecordset("tblIn", dbOpenTable, dbReadOnly)
Set rstOut = db.OpenRecordset("tblOut", dbOpenDynaset)
With rstIn
Do While Not .EOF
rstOut.FindFirst "ContractId=" & !ContractId
If rstOut.NoMatch Then
rstOut.AddNew
rstOut!ContractId = !ContractId
Else
rstOut.Edit
End If
rstOut(!Name) = !Text
rstOut.Update
.MoveNext
Loop
End With
rstIn.Close: Set rstIn = Nothing
rstOut.Close: Set rstOut = Nothing
Set db = Nothing
End Function
==========================
--
Andy Lacey
http://www.minstersystems.co.uk
--------- Original Message --------
From: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
To: accessd at databaseadvisors.com <accessd at databaseadvisors.com>
Subject: RE: [AccessD] Help with Code
Date: 07/02/05 12:32
>
> Not sure I fully understand. I've set up the crosstab query which
now
> has the proper field headings - but how do I get the memo fields
> associated with the correct fields/headings?
>
> Terri Jarus
> Vice President, Contract Services
> jarus at amerinet-gpo.com
> 314-542-1902
>
> >>> andy at minstersystems.co.uk 2/7/2005 1:30:16 AM
>>>
>
> Terri
> Borge's answer sounds right to me, but if that doesn't work for you
I
> could
> write you some DAO code if you want. It'll be difficult though for
me
> to fit
> it in so please only ask if Borge's approach doesn't suit.
>
> -- Andy Lacey
> http://www.minstersystems.co.uk
>
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> > Borge Hansen
> > Sent: 07 February 2005 01:47
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Help with Code
> >
> >
> > Terri,
> > This is what I would try to do:
> >
> > 1. Create the crosstab you need withouth the memo field and
> > save as Query 2. Create another query based on 1. and with
> > the memo field added
> >
> > Regards,
> > Borge
> >
> > ----- Original Message -----
> > From: "Terri Jarus" <jarus at amerinet-gpo.com>
> > To: <accessd at databaseadvisors.com>
> > Sent: Monday, February 07, 2005 6:41 AM
> > Subject: [AccessD] Help with Code
> >
> >
> > > SECOND ATTEMPT (without attached sample file)
> > > I apologize for not knowing how to do this on my own and
appreciate
>
> > > any help I can get. I have a table of data - one field
> > named "Name",
> > > a second field named "Text" and a third field
named
"ContractID".
> > >
> > > The TEXT field is a memo field. What I really wanted to do
was
to
>
> > > create a crosstab that has the Contract ID as a row, the
Name as
a
>
> > > Column Heading and the TEXT appearing under the
appropriate
name.
> > > However, crosstab won't work with a memo field.
> > >
> > > The data under NAME is confined to 23 distinctive items.
> > For example,
> > > one would be ACCESS CRITERIA, one would be CONTRACT
> > BENEFITS, etc. So
> > > I thought to create a new table with each of the NAME
items
> > as a field
> > > heading. Then writing a row for each Contract ID (another
field
> > > heading) and the assigned TEXT to the appropriate NAME.
Hope
that
>
> > > makes sense. I just don't know how to write the code to do
this.
> > >
> > > Small sample data file can be sent on request. As is
> > always the case,
> > > I need to get this going ASAP. Any help or pointing to
something
> > > similar would be greatly appreciated.
> > >
> > > You guys rock!
> > >
> > > Terri Jarus
> > > Vice President, Contract Services
> > > jarus at amerinet-gpo.com
> > > 314-542-1902
> > >
> > >
> >
>
----------------------------------------------------------------------
> > > ----
> > -
> > > This email and any files transmitted with it are
confidential
and
> > > intended solely for the use of the individuals or entities
to
whom
>
> > > they are addressed. If you have received this email in
error
please
>
> > > return it to the sender, and erase any copies thereof.
> > Copyright 2005
> > > Amerinet 1nc.
> > > --
> > > 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
>
>
>
---------------------------------------------------------------------------
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individuals or entities to whom
they
> are addressed. If you have received this email in error please
return
> it to the sender, and erase any copies thereof.
> Copyright 2005 Amerinet 1nc.
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
>
>
>
>
________________________________________________
Message sent using UebiMiau 2.7.2
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com