Finding A Record

[zp162002]

Hello All,

I'm trying to find a record in a table based upon two fields from a form using the code below.

rs.FindFirst "[SellerNameID] = '" & strSellerName & "' And [AuctionNum] = '" & strAuctionNum & "'"

Each time this line of code executes, I receive an error message stating:
"Run-time error '3077':

Syntax error (missing operator) in expression."

Any ideas on what I'm leaving out? Thanks.

Patrick

 

[gol4]

if AuctionNum is indeed a number then there is no need to surround it with quotes

 

[zp162002]

AuctionNum is a text field. It can contain either text or numbers.

Is there a more efficient way to code this?

 

[PHV]

And this ?
rs.FindFirst "SellerNameID=[tt]'"[/tt] & Replace(strSellerName, [tt]"'", "''") & "'[/tt] AND AuctionNum=[tt]'"[/tt] & Replace(strAuctionNum, [tt]"'", "''") & "'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[zp162002]

That's what my problem is - I have apostrophes in the string. I'm using Access 97 (I know - my company is way behind the times) and it doesn't recognize Replace. Isn't there a way to replace the apostrophes using some Ascii code?

 

[gol4]

could or use your own replace function

Public Function SReplace(strOrig As String, Optional StrOld As String, Optional StrNew As String) As String
'performs something like excel replace function
Dim i As Integer
SReplace = ""
i = 1
While i <= Len(strOrig)
    SReplace = SReplace & IIf(Mid(strOrig, i, 1) = StrOld, StrNew, Mid(strOrig, i, 1))
    i = i + 1
Wend
End Function

here is a sub. place it in a module then use SReplace

 

[zp162002]

Thanks guys. I'll give it a shot.