Andy Lacey
andy at minstersystems.co.uk
Mon Feb 7 08:20:40 CST 2005
Terri
Yes, if you have alphanumeric contract id's then cahnge the FindFirst line
to
rstOut.FindFirst "ContractId='" & !ContractId & "'"
--
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 14:53
>
> 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
>
> --
> 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