Access 2010: FindFirst not finding record with apostrophes

[tanya74]

Hi everyone,

I am not sure if anyone else has had this problem but I am using FindFirst for a Id of a new patient as I would like to update the details if it already exists. The FindFirst does NOT find a matching record but I know it exists!!

The Ids contain letters and punctation (legacy data...). There is a patient with Id = "O'HARA1" (without the double quotes) in the table.
The patient with new details with Id of "O'HARA1" comes along so I want to perform an update on the existing row.

My problem is that the current patient isnt found with the following code.

  rstCurrentPatients.FindFirst ("Id = '" & SQLText(rstNewPatients("Id")) & "'")

rstCurrentPatients.NoMatch seems to be always true even though a record exists.

My FindFirst criteria is "Id = 'O''HARA1'".

I have also tried Chr(34) as other posts suggest...

rstCurrentPatients.FindFirst ("Id = " & Chr(34) & SQLText(rstNewPatients("Id")) & Chr(34))

but still NoMatch is true.

Function SQLText(ByVal ST As String) As String
    
    Dim tempString As String
    
    tempString = Replace(ST, "'", "''")
    
    SQLText = tempString

End Function

I am using Access 2010. I hope someone can help?

Thanks in advance,
Tanya

 

[Khalid_Afridi]

Hi Tanya, Welcome to AWF,

Did you try to find your record using combo box filtration? the combobox solve this problem for data with the punctuation marks. In access 2010 the combo box will convert your code for find first in embedded macro (which i don't like) but still it will find the record with the punctuation marks and apostrophe.

Macro
Action:
SearchForRecord

Arguments:
First

Where condition:
="[fieldname] = " & Str(Nz(Screen.ActiveControl,0))

 

[tanya74]

Thanks for your reply.

I slept on it and realised that I needed to remove the SQLText function to the FindFirst when I use double quotes.

rstCurrentPatients.FindFirst ("PracSoftId = " & Chr(34) & rstNewPatients("PracSoftId") & Chr(34))

Thanks again

 

[boblarson]

Thanks for your reply.

I slept on it and realised that I needed to remove the SQLText function to the FindFirst when I use double quotes.

rstCurrentPatients.FindFirst ("PracSoftId = " & Chr(34) & rstNewPatients("PracSoftId") & Chr(34))

Thanks again

I've gone to using Chr(34) exclusively as I am much less likely to come across double quotes in my fields than I am single quotes.

 

[Khalid_Afridi]

Hi Tanya,

if you can change your code for SQLText Function to:

Function SQLText(ByVal ST As String) As String

Dim tempString As String

tempString = Replace(ST, "'", "''") ' to tempString = Replace(ST, "'", "")

SQLText = tempString

End Function

then see what is happening?

This will remove the apostraphe from your ID but still it will not show the result for Id = "O'HARA1"

I don't think that any ID should contain with the apostraphe... my suggestion is, if you can create a combobox on your desire field to find any record is a better choice.