findfirst where string

  • Thread starter Thread starter Wim
  • Start date Start date
W

Wim

Hi all,

I have a problem with the quotes in the following piece of
code :

rst.findfirst strWhere

where strWhere should be :

"rst.fields("FirstField") = " & rst2.fields("Secondfield")

I tried all variations of single and double quotes but
after an hour and a half I resigned. Anyone an idea ?

Thanks in advance,

Wim
 
Wim said:
Hi all,

I have a problem with the quotes in the following piece of
code :

rst.findfirst strWhere

where strWhere should be :

"rst.fields("FirstField") = " & rst2.fields("Secondfield")

I tried all variations of single and double quotes but
after an hour and a half I resigned. Anyone an idea ?

Thanks in advance,

Wim
Click to expand...

Am I right in thinking that you're trying to find the first record in
recordset rst where the field [FirstField] has a value equal to the
current value of the field [SecondField] in some other recordset named
rst2? I'd think you'd use a statement like one of these (depending on
whether these fields are numeric, text/memo, or date:

' for numeric fields
rst.FindFirst "FirstField=" & rst2!SecondField

' for text/memo fields (provided no embedded double-quotes)
rst.FindFirst "FirstField=""" & rst2!SecondField & """"

' for date fields
rst.FindFirst "FirstField=#" & rst2!SecondField & "#"

The point is that you don't need to qualify the reference to FirstField
in rst when specifying it as a search criterion on that recordset.

Note that proper, safe date searching requires that date literals be
unambiguous, or else be formatted in MM/DD/YYYY format.
 
Dirk,

Thanks for your quick answer. Indeed, I search a value in
rst based upon a value in rst2.

Both field values are numeric (long). I'll give it a
try. I'd have liked it however in a string (much more
difficult of course).

Wim
-----Original Message-----
Hi all,

I have a problem with the quotes in the following piece of
code :

rst.findfirst strWhere

where strWhere should be :

"rst.fields("FirstField") = " & rst2.fields ("Secondfield")

I tried all variations of single and double quotes but
after an hour and a half I resigned. Anyone an idea ?

Thanks in advance,

Wim
Click to expand...

Am I right in thinking that you're trying to find the first record in
recordset rst where the field [FirstField] has a value equal to the
current value of the field [SecondField] in some other recordset named
rst2? I'd think you'd use a statement like one of these (depending on
whether these fields are numeric, text/memo, or date:

' for numeric fields
rst.FindFirst "FirstField=" & rst2!SecondField

' for text/memo fields (provided no embedded double- quotes)
rst.FindFirst "FirstField=""" & rst2!SecondField & """"

' for date fields
rst.FindFirst "FirstField=#" & rst2!SecondField & "#"

The point is that you don't need to qualify the reference to FirstField
in rst when specifying it as a search criterion on that recordset.

Note that proper, safe date searching requires that date literals be
unambiguous, or else be formatted in MM/DD/YYYY format.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
If you want to store the criteria in a string, it would be:

strWhere = "FirstField=" & rst2!SecondField

or

strWhere = "FirstField = " & rst2.Fields("Secondfield")


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Dirk,

Thanks for your quick answer. Indeed, I search a value in
rst based upon a value in rst2.

Both field values are numeric (long). I'll give it a
try. I'd have liked it however in a string (much more
difficult of course).

Wim
-----Original Message-----
Hi all,

I have a problem with the quotes in the following piece of
code :

rst.findfirst strWhere

where strWhere should be :

"rst.fields("FirstField") = " & rst2.fields ("Secondfield")

I tried all variations of single and double quotes but
after an hour and a half I resigned. Anyone an idea ?

Thanks in advance,

Wim
Click to expand...

Am I right in thinking that you're trying to find the first record in
recordset rst where the field [FirstField] has a value equal to the
current value of the field [SecondField] in some other recordset named
rst2? I'd think you'd use a statement like one of these (depending on
whether these fields are numeric, text/memo, or date:

' for numeric fields
rst.FindFirst "FirstField=" & rst2!SecondField

' for text/memo fields (provided no embedded double- quotes)
rst.FindFirst "FirstField=""" & rst2!SecondField & """"

' for date fields
rst.FindFirst "FirstField=#" & rst2!SecondField & "#"

The point is that you don't need to qualify the reference to FirstField
in rst when specifying it as a search criterion on that recordset.

Note that proper, safe date searching requires that date literals be
unambiguous, or else be formatted in MM/DD/YYYY format.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
Click to expand...
 
Dirk,

It worked, thank you very much.

Wim
-----Original Message-----
Hi all,

I have a problem with the quotes in the following piece of
code :

rst.findfirst strWhere

where strWhere should be :

"rst.fields("FirstField") = " & rst2.fields ("Secondfield")

I tried all variations of single and double quotes but
after an hour and a half I resigned. Anyone an idea ?

Thanks in advance,

Wim
Click to expand...

Am I right in thinking that you're trying to find the first record in
recordset rst where the field [FirstField] has a value equal to the
current value of the field [SecondField] in some other recordset named
rst2? I'd think you'd use a statement like one of these (depending on
whether these fields are numeric, text/memo, or date:

' for numeric fields
rst.FindFirst "FirstField=" & rst2!SecondField

' for text/memo fields (provided no embedded double- quotes)
rst.FindFirst "FirstField=""" & rst2!SecondField & """"

' for date fields
rst.FindFirst "FirstField=#" & rst2!SecondField & "#"

The point is that you don't need to qualify the reference to FirstField
in rst when specifying it as a search criterion on that recordset.

Note that proper, safe date searching requires that date literals be
unambiguous, or else be formatted in MM/DD/YYYY format.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Doug,

Thank you very much !

Wim
-----Original Message-----
If you want to store the criteria in a string, it would be:

strWhere = "FirstField=" & rst2!SecondField

or

strWhere = "FirstField = " & rst2.Fields("Secondfield")


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Dirk,

Thanks for your quick answer. Indeed, I search a value in
rst based upon a value in rst2.

Both field values are numeric (long). I'll give it a
try. I'd have liked it however in a string (much more
difficult of course).

Wim
-----Original Message-----
Hi all,

I have a problem with the quotes in the following
Click to expand...
piece
of
code :

rst.findfirst strWhere

where strWhere should be :

"rst.fields("FirstField") = " & rst2.fields ("Secondfield")

I tried all variations of single and double quotes but
after an hour and a half I resigned. Anyone an idea ?

Thanks in advance,

Wim

Am I right in thinking that you're trying to find the first record in
recordset rst where the field [FirstField] has a value equal to the
current value of the field [SecondField] in some other recordset named
rst2? I'd think you'd use a statement like one of
Click to expand...
these
(depending on
whether these fields are numeric, text/memo, or date:

' for numeric fields
rst.FindFirst "FirstField=" & rst2!SecondField

' for text/memo fields (provided no embedded
Click to expand...
double-
quotes)
rst.FindFirst "FirstField=""" & rst2!SecondField & """"

' for date fields
rst.FindFirst "FirstField=#" & rst2!SecondField & "#"

The point is that you don't need to qualify the
Click to expand...
reference
to FirstField
in rst when specifying it as a search criterion on that recordset.

Note that proper, safe date searching requires that
Click to expand...
date
literals be
unambiguous, or else be formatted in MM/DD/YYYY format.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top