Charlotte Foust
charlotte.foust at gmail.com
Thu Dec 29 13:28:26 CST 2011
What is the field name of RS8.Fields(1)? If it's Meter1, then use
RS8("Meter1") instead of the index. And what are you actually trying to
match? Use an actual value for RS9.Fields(1), i.e., RS9("Meter2"). Just
using a variable doesn't get rid of the indexed reference. And as was
pointed out, you are trying to match All RS8 fields(1) to the value in the
first record of RS9. Fields(1). Is that what you're trying to do?
Charlotte Foust
On Thu, Dec 29, 2011 at 11:18 AM, Kaup, Chester <
Chester_Kaup at kindermorgan.com> wrote:
> Thanks for taking the time to give me some really good info. I am still
> having trouble though. I get an Unknown Function Error on the line
>
> RS8.FindFirst "RS8.Fields(1)=" & RS9.Fields(1)
>
> Both RS8.Fields(1) and RS9.Fields(1) are number fields
>
> I then tried the following. Meter1 and Meter2 are both dimmed as single
>
> Meter1 = RS8.Fields(1)
> Meter2 = RS9.Fields(1)
> RS8.FindFirst "Meter1=" & Meter2
>
> This gives the error
> The Microsoft Access database engine does not recognize "Meter1" as a
> valid field name or expression.
>
> Thanks for the help.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:
> accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
> Sent: Thursday, December 29, 2011 11:56 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] FindFirst
>
> Chester,
>
> Your criteria string used by FindFirst is the problem.
>
> But first some comments about referencing fields in a recordset.
>
> There are several ways (i.e. several valid syntaxes) to reference a
> specific field in a recordset (which can point to a table or a query)
>
> 1/ By the ordinal value of the field, i.e. its position in the list of
> fields in the recordset. Your use of the expression "RS8.Fields(1)" is an
> example. It resolves to the value of the first field in the recordset. The
> big disadvantage of this syntax is that it is not "reader friendly". When
> reading the code you (or anyone else) would probably have to go back to the
> table/query to find out what Fields(1) actually is.
>
> 2/ By using the field name as an index to the fields collection of the
> recordset.
> e.g. RS8("SomeFieldName")
> This is more readable as the field name in the quotes tells you what
> data is being referenced.
>
> 3/ BY directly using the Recordset and field names.
> e.g. [RS8]![SomeFieldName]
> The square brackets are optional if the object names do not have spaces,
> but I always use them.
>
> I personally prefer to use method 3 as it is then possible in code blocks
> to use the With / End With construct, which allows you to skip the
> recordset name in multiple statements...
>
> With RS8
> ![SomeFieldName] = AValue
> ![AnotherField] = AnotherValue
> .FindFirst "SomeOtherField=" & Avalue
> If Not .NoMatch Then ' We found it
> ' Do something
> End If
> ... Etc.
> End With
>
> So, returning to your question, how to use FindFirst.
>
> "RS8.Fields(1)=RS9.Fields(1)" does not work as a criteria string because
> it is literally saying: "find the first record where the field(1) value is
> equal to the literal string "RS9.Fields(1)", but you actually want to find
> the record where the *value* of RS8.Fields(1) is equal to the *value* of
> RS8.Fields(1).
>
> If you change the criteria string to this...
>
> RS8.FindFirst "RS8.Fields(1)=" & RS9.Fields(1)
>
> Then it will work, providing Fields(1) is not a text or a date value.
>
> The above syntax works for other data types, but for TEXT you have to
> enclose the value you are searching for in quotes. Either a combination of
> single and double quotes like this...
>
> "RS8.Fields(1)='" & RS9.Fields(1) & "'"
>
> Or all double quotes like this....
>
> "RS8.Fields(1)="""" & RS9.Fields(1) & """"
>
> Those are a little difficult to read, especially seeing "'" for what it is
> with a proportional font. So I use a little function to wrap the text in
> quotes...
>
> Function Quote(aString) As String
> Quote = """" & aString & """"
> End Function
>
> And using that I can then write...
>
> "RS8.Fields(1)=" & Quote(RS9.Fields(1))
>
> Or my preferred syntax
>
> "[RS8]![SomeFieldName]=" & Quote([RS9]![SomeOtherField]
>
> Searching for date values is similar, but a different delimiter is used,
> the # sign. So if RS9.Field(1) was a date value, the search criteria would
> have to be
>
> "RS8.Fields(1)=#" & RS9.Fields(1) & "#"
>
> HTH
>
> Lambert
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:
> accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
> Sent: Thursday, December 29, 2011 12:27 PM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] FindFirst
>
> This is my first try using this command and I need some help. Here is what
> I have. Does it work only on tables? Thanks.
>
> Set RS8 = MyDb.OpenRecordset("qry Produced Gas CO2 Analysis")
>
> Set RS9 = MyDb.OpenRecordset(strSQL)
>
> RS8.FindFirst "RS8.Fields(1)=RS9.Fields(1)"
>
>
>
> 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
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
>
>
> Website: http://www.databaseadvisors.com
>
>
>