Multiple Criteria in FindFirst?

[Guest]

I'm wondering if you can use multiple criteria in a FindFirst statement. I
created a query to select by CutomerID and Customer Address and took the
SELECT statement and tried to adapt it to the FindFirst statement which I
thought allowed you to use a SELECT statement without the WHERE. However, I
have not been able to make the end strStringSearch look like the one in the
SELECT statement. According to the query the WHERE part of the query I
created was

(((tblFamilyMembers.CustomerID)=9)AND((tblFamilyMembers.CustomerAddress)="124 First Avenue"))
I've tried all several different ways to try to duplicate the above but
haven't been able to. Is it possible? Also, should I use brackets or
parenthesis for the table names? This is my last attempt.

strStringSearch = "(([CustomerID]=" & Me!CustomerID & ")" & "AND" & _
"([CustomerAddres]=)" & Me!CustomerAddress & "))"
rs.FindFirst strStringSearch

The error I get, most of the time, is 3077 - 'missing operator', but it
looks like the parenthesis and the quotes are paired up right. Another
problem seems to be that I need quotes around the CustomerAddress as is in
the WHERE statement, but when I try to put those in instead of the actual
CustomerAddress, i.e. 123 First St., I get Me!CustomerAddress. Any help is
greatly appreciated as has been all the other help that has been offered to
other questions I have posted. This discussion group has helped immensely in
wading through Access and VBA. Thank you all so much for your continuing help
and patience.
Randy M

 

[Brian Bastl]

WCDoan,

Using the 'And' operator seems redundant, since your customer will most
likely only have one address.

I'd reduce it to this:
strStringSearch = "[CustomerID]=" & Me!CustomerID
rs.FindFirst strStringSearch

but if you must:

strStringSearch = "[CustomerID]=" & Me!CustomerID & " AND " & _
"[CustomerAddress]=""" & Me!CustomerAddress & """"
rs.FindFirst strStringSearch

HTH,
Brian

I'm wondering if you can use multiple criteria in a FindFirst statement. I
created a query to select by CutomerID and Customer Address and took the
SELECT statement and tried to adapt it to the FindFirst statement which I
thought allowed you to use a SELECT statement without the WHERE. However, I
have not been able to make the end strStringSearch look like the one in the
SELECT statement. According to the query the WHERE part of the query I
created was

(((tblFamilyMembers.CustomerID)=9)AND((tblFamilyMembers.CustomerAddress)="12
4 First Avenue"))

I've tried all several different ways to try to duplicate the above but
haven't been able to. Is it possible? Also, should I use brackets or
parenthesis for the table names? This is my last attempt.

strStringSearch = "(([CustomerID]=" & Me!CustomerID & ")" & "AND" & _
"([CustomerAddres]=)" & Me!CustomerAddress & "))"
rs.FindFirst strStringSearch

The error I get, most of the time, is 3077 - 'missing operator', but it
looks like the parenthesis and the quotes are paired up right. Another
problem seems to be that I need quotes around the CustomerAddress as is in
the WHERE statement, but when I try to put those in instead of the actual
CustomerAddress, i.e. 123 First St., I get Me!CustomerAddress. Any help is
greatly appreciated as has been all the other help that has been offered to
other questions I have posted. This discussion group has helped immensely in
wading through Access and VBA. Thank you all so much for your continuing help
and patience.
Randy M

 

[Guest]

Brian,
Thanks for answering. Sorry, I'm only now replying, but I've been out of
my office the rest of the week. This is a data base for a city for licenses
for yard sales, garage sales, etc. I need to be able to link a buyer with
relatives that live at the location of the yard sale, etc. That's the reason
for the multiple addresses. Under ordinary circumstances, I wouldn't need a
different address, but in this instance it's a possibility. That's why I was
having to check and make sure the address was the right one. I'll try this
and see what happens.
Thanks again,
Randy M

WCDoan,

Using the 'And' operator seems redundant, since your customer will most
likely only have one address.

I'd reduce it to this:
strStringSearch = "[CustomerID]=" & Me!CustomerID
rs.FindFirst strStringSearch

but if you must:

strStringSearch = "[CustomerID]=" & Me!CustomerID & " AND " & _
"[CustomerAddress]=""" & Me!CustomerAddress & """"
rs.FindFirst strStringSearch

HTH,
Brian

I'm wondering if you can use multiple criteria in a FindFirst statement. I
created a query to select by CutomerID and Customer Address and took the
SELECT statement and tried to adapt it to the FindFirst statement which I
thought allowed you to use a SELECT statement without the WHERE. However, I
have not been able to make the end strStringSearch look like the one in the
SELECT statement. According to the query the WHERE part of the query I
created was

(((tblFamilyMembers.CustomerID)=9)AND((tblFamilyMembers.CustomerAddress)="12
4 First Avenue"))

I've tried all several different ways to try to duplicate the above but
haven't been able to. Is it possible? Also, should I use brackets or
parenthesis for the table names? This is my last attempt.

strStringSearch = "(([CustomerID]=" & Me!CustomerID & ")" & "AND" & _
"([CustomerAddres]=)" & Me!CustomerAddress & "))"
rs.FindFirst strStringSearch

The error I get, most of the time, is 3077 - 'missing operator', but it
looks like the parenthesis and the quotes are paired up right. Another
problem seems to be that I need quotes around the CustomerAddress as is in
the WHERE statement, but when I try to put those in instead of the actual
CustomerAddress, i.e. 123 First St., I get Me!CustomerAddress. Any help is
greatly appreciated as has been all the other help that has been offered to
other questions I have posted. This discussion group has helped immensely in
wading through Access and VBA. Thank you all so much for your continuing help
and patience.
Randy M