REPLACE Function

[Tony2021]

Hello Experts, 

I have a find form (frmFind) and I have many subforms on this find form. 

I enter a number or text in txtFind on frmFind and if there is a hit, the subforms will show the data. I have many subforms on this frmFind.

I have an issue when trying to find a number (that is text) in a text field on the subform due to the number having a comma. 

Example:

If I enter 1000 in txtFind on frmFind it will find all hits with 1000 in a number field (whether or not there is a comma) or 1000 that is in a text field.

It wont find 1000 (with a comma) in a text field unless I enter it as 1,000 (with a comma) 

I am not sure how I can ignore a comma in a number formatted with a comma in a text field tblPmtProposalALL.Notes. 

I want to be able to enter 1000 in txtFind and find all occurrences with or without a comma in tblPmtProposalALL.Notes.  

I am not sure but REPLACE (and replace a "," with "") could be the function I need to incorporate in the bolded text below?   

thank you very much.  Let me know if any questions. 

here is my sql in 1 of the subforms on frmFind:

(Its modified to reduce space)

SELECT tblPmtProposalALL.ID, tblPmtProposalALL.DDNo, tblPmtProposalALL.Amount, tblPmtProposalALL.Notes
FROM tblPmtProposalALL LEFT JOIN (tblBankAccounts LEFT JOIN tblCurrency ON tblBankAccounts.Currency = tblCurrency.ID) ON tblPmtProposalALL.ID2 = tblBankAccounts.CoIDfk
WHERE (((tblPmtProposalALL.Notes) Like "*" & Nz([Forms]![frmFind]![txtFind],"") & "*"));

[#2 · Accepted solution]

WHERE ((Replace(tblPmtProposalALL.Notes, ",", "") Like "*" & Nz([Forms]![frmFind]![txtFind],"") & "*"));

[#3]

@Tom_van_Stiphout 

Hi Tom, thanks for the response. 

please see below my sql with the Replace function added.  Let me know if you see any issues.  I would expect to see a criteria (as I do in the other columns) in the query window but I am not sure. 

WHERE (((tblPmtProposalALL.DDNo) Like "*" & Nz([Forms]![frmFind]![txtFind],"") & "*")) OR (((tblPmtProposalALL.Amount) Like "*" & Nz([Forms]![frmFind]![txtFind],"") & "*")) OR ((Replace(tblPmtProposalALL.Notes, ",", "") Like "*" & Nz([Forms]![frmFind]![txtFind],"") & "*")) OR (((tblBankAccounts.Beneficiary) Like "*" & Nz([Forms]![frmFind]![txtFind],"") & "*")) OR (((tblPmtProposalALL.Reference) Like "*" & Nz([Forms]![frmFind]![txtFind],"") & "*"));

thank you.  

[#4]

Tom, just to confirm, is the REPLACE making a permanent change to the table data or is this only in the query and doesnt change the actual table data?

[#5]

This is a SELECT query. They cannot update data in tables. We have UPDATE queries for that.

Of course you can try on a backup of your database.

[#6]

Hi Tom, thank you. I’m not sure if you saw my other question?

[#7]

I got it now. I actually ended up using a function I already had in my db that strips out the extranneous characters. I forgot I had it. Thank you very much Tom.