Andy Lacey
andy at minstersystems.co.uk
Mon Feb 7 09:30:12 CST 2005
Happy to help Terri. And if your last question was in any way semi-serious you could always make a donation to dba. Donations are what keep the list alive. -- 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 15:49 > > That was it!!!! That was what I needed - you are so awesome! Where do > I send the check.... > > Terri Jarus > Vice President, Contract Services > jarus at amerinet-gpo.com > 314-542-1902 > > >>> andy at minstersystems.co.uk 2/7/2005 8:20:40 AM >>> > > 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 > > > > > > &gt;&gt;&gt; andy at minstersystems.co.uk 2/7/2005 > 1:30:16 > AM > > &gt;&gt;&gt; > > > > > > 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 > > > > > > &gt; -----Original Message----- > > > &gt; From: accessd-bounces at databaseadvisors.com > > > &gt; [mailto:accessd-bounces at databaseadvisors.com] On Behalf > Of > > > &gt; Borge Hansen > > > &gt; Sent: 07 February 2005 01:47 > > > &gt; To: Access Developers discussion and problem solving > > > &gt; Subject: Re: [AccessD] Help with Code > > > &gt; > > > &gt; > > > &gt; Terri, > > > &gt; This is what I would try to do: > > > &gt; > > > &gt; 1. Create the crosstab you need withouth the memo field > and > > > &gt; save as Query 2. Create another query based on 1. and > with > > > &gt; the memo field added > > > &gt; > > > &gt; Regards, > > > &gt; Borge > > > &gt; > > > &gt; ----- Original Message ----- > > > &gt; From: &quot;Terri Jarus&quot; > &lt;jarus at amerinet-gpo.com&gt; > > > &gt; To: &lt;accessd at databaseadvisors.com&gt; > > > &gt; Sent: Monday, February 07, 2005 6:41 AM > > > &gt; Subject: [AccessD] Help with Code > > > &gt; > > > &gt; > > > &gt; &gt; SECOND ATTEMPT (without attached sample file) > > > &gt; &gt; I apologize for not knowing how to do this on > my > own and > > appreciate > > > > > > &gt; &gt; any help I can get. I have a table of data - > one > field > > > &gt; named &quot;Name&quot;, > > > &gt; &gt; a second field named &quot;Text&quot; > and a > third field > > named > > &quot;ContractID&quot;. > > > &gt; &gt; > > > &gt; &gt; The TEXT field is a memo field. What I > really > wanted to do > > was > > to > > > > > > &gt; &gt; create a crosstab that has the Contract ID as > a > row, the > > Name as > > a > > > > > > &gt; &gt; Column Heading and the TEXT appearing under > the > > appropriate > > name. > > > &gt; &gt; However, crosstab won't work with a memo > field. > > > &gt; &gt; > > > &gt; &gt; The data under NAME is confined to 23 > distinctive > items. > > > &gt; For example, > > > &gt; &gt; one would be ACCESS CRITERIA, one would be > CONTRACT > > > &gt; BENEFITS, etc. So > > > &gt; &gt; I thought to create a new table with each of > the > NAME > > items > > > &gt; as a field > > > &gt; &gt; heading. Then writing a row for each Contract > ID > (another > > field > > > &gt; &gt; heading) and the assigned TEXT to the > appropriate > NAME. > > Hope > > that > > > > > > &gt; &gt; makes sense. I just don't know how to write > the > code to do > > this. > > > &gt; &gt; > > > &gt; &gt; Small sample data file can be sent on request. > As > is > > > &gt; always the case, > > > &gt; &gt; I need to get this going ASAP. Any help or > pointing to > > something > > > &gt; &gt; similar would be greatly appreciated. > > > &gt; &gt; > > > &gt; &gt; You guys rock! > > > &gt; &gt; > > > &gt; &gt; Terri Jarus > > > &gt; &gt; Vice President, Contract Services > > > &gt; &gt; jarus at amerinet-gpo.com > > > &gt; &gt; 314-542-1902 > > > &gt; &gt; > > > &gt; &gt; > > > &gt; > > > > > > ---------------------------------------------------------------------- > > > &gt; &gt; ---- > > > &gt; - > > > &gt; &gt; This email and any files transmitted with it > are > > confidential > > and > > > &gt; &gt; intended solely for the use of the individuals > or > entities > > to > > whom > > > > > > &gt; &gt; they are addressed. If you have received this > email > in > > error > > please > > > > > > &gt; &gt; return it to the sender, and erase any copies > thereof. > > > &gt; Copyright 2005 > > > &gt; &gt; Amerinet 1nc. > > > &gt; &gt; -- > > > &gt; &gt; AccessD mailing list > > > &gt; &gt; AccessD at databaseadvisors.com > > > &gt; &gt; > http://databaseadvisors.com/mailman/listinfo/accessd > > > &gt; &gt; Website: http://www.databaseadvisors.com > > > &gt; &gt; > > > &gt; > > > &gt; -- > > > &gt; AccessD mailing list > > > &gt; AccessD at databaseadvisors.com > > > &gt; http://databaseadvisors.com/mailman/listinfo/accessd > > > &gt; Website: http://www.databaseadvisors.com > > > &gt; > > > &gt; > > > > > > -- > > > 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 > > -- > 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