[AccessD] Compile Error - Object Required

I agree that it is not well written code that was done by a previous employee years ago. Just trying to do a quick fix before a rewrite.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Wednesday, January 28, 2009 10:03 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Compile Error - Object Required

The Set keyword is normally only used when initializing objects, such as Recordsets, QueryDefs, Excel Worksheets etc.

The SQL in strSQL selects just five fields, and so it would appear that the index value 4 into the .Fields collection should be correct. However my questions for your are...

1/ Why use the fields collection at all? You know the field names, and so could use !PID, !TestDate1, !Expr1, !Expr2 and !Area to reference them.

2/ The fields all appear contain regular scalar variable data, not objects, so why are you using "Set PID1 = " ? How have you declared those variables PID1,Date1 etc.? It looks like they should be Dim-ed as Longs and Dates etc., not as some object type.

HTH

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Wednesday, January 28, 2009 9:51 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Compile Error - Object Required

The following piece of code generates the informative message Compile Error - Object Required when it gets to the last SET statement. The variable is dimmed. Somewhere I am missing the problem.

strSql = "SELECT DISTINCT dbo_DSS_WellTests.PID, CDate(Int([TestDate])) AS TestDate1, Nz(LinePees,0) AS Expr1, Nz(TubingPres,0) AS Expr2, " & _
    "Switch([ConfigMaster]![PID]='WGRP115',1,[ConfigMaster]![PID]='WGRP116',2,[ConfigMaster]![PID]='WGRP117',3) AS Area " & _
    "FROM ConfigMaster INNER JOIN (dbo_DSS_WellTests INNER JOIN dbo_DSS_LastStatus ON dbo_DSS_WellTests.PID = dbo_DSS_LastStatus.PID) ON ConfigMaster.ChildPID =    dbo_DSS_WellTests.Well_Name " & _
    "WHERE (((dbo_DSS_LastStatus.Status)='WI' Or (dbo_DSS_LastStatus.Status)='CI')) " & _
    "GROUP BY dbo_DSS_WellTests.PID, CDate(Int([TestDate])), dbo_DSS_WellTests.LinePres, dbo_DSS_WellTests.TubingPres, " & _
    "Switch([ConfigMaster]![PID]='WGRP115',1,[ConfigMaster]![PID]='WGRP116',2,[ConfigMaster]![PID]='WGRP117',3) " & _
    "HAVING (((CDate(Int([TestDate]))) > Now() - 365) " & _
    "And ((Switch([ConfigMaster]![PID] = 'WGRP115', 1, [ConfigMaster]![PID] = 'WGRP116', 2, [ConfigMaster]![PID] = 'WGRP117', 3)) = 1 " & _
    "Or (Switch([ConfigMaster]![PID] = 'WGRP115', 1, [ConfigMaster]![PID] = 'WGRP116', 2, [ConfigMaster]![PID] = 'WGRP117', 3)) = 2 " & _
    "Or (Switch([ConfigMaster]![PID] = 'WGRP115', 1, [ConfigMaster]![PID] = 'WGRP116', 2, [ConfigMaster]![PID] = 'WGRP117', 3)) = 3)) " & _
    "ORDER BY dbo_DSS_WellTests.PID;"

Set Rs1 = MyDb.OpenRecordset(strSql)

Set PID1 = Rs1.Fields(0)
Set Date1 = Rs1.Fields(1)
Set LinePres1 = Rs1.Fields(2)
Set TubingPres1 = Rs1.Fields(3)
Set Area1 = Rs1.Fields(4)


Chester Kaup

Engineering Technician

Kinder Morgan CO2 Company, LLP

Office (432) 688-3797

FAX (432) 688-3799





No trees were killed in the sending of this message. However a large number of electrons were terribly inconvenienced.

--
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