locked
Trying to use REPLACE in Query RRS feed

  • Question

  • I have this query:

    SELECT * FROM tblMatter WHERE Replace(Case_Number,"-","")='31500463DB'

    The Case_Number field has strings with dashes, like 315-00463-DB. I want to search the Case_Number field without the dashes. I thought this would work, but I get the following error:

    Data type mismatch in criteria experession

    How do I do this, or is there a better way?

    tod

    Monday, June 6, 2016 7:34 PM

Answers

  • If you're going to do this quite a bit, you might also consider creating a query that has a CaseNumber field that has no dashes, and search on that:

    SELECT Replace(Case_Number, "-", "") AS CaseNumberStripped, * FROM tblMatter

    Store that as "qryStrippedCaseNumbers", and then use that to search:

    SELECT * FROM qryStrippedCaseNumbers WHERE CaseNumberStripped = '31500463DB'



    -- Scott McDaniel, Microsoft Access MVP

    Monday, June 6, 2016 9:30 PM

All replies

  • Hi Tod. Not sure if it matters but have you tried using double-quotes around the value as well? For example:

    ...WHERE Replace(Case_Number,"-","")="31500463DB"

    Monday, June 6, 2016 7:40 PM
  • Hi Tod. Not sure if it matters but have you tried using double-quotes around the value as well? For example:

    ...WHERE Replace(Case_Number,"-","")="31500463DB"

    Hi tod,

    Or ...

    change all double quotes to single quotes:

    ...WHERE Replace(Case_Number,'-','')='31500463DB'

    Imb.

    Monday, June 6, 2016 8:14 PM
  • If you're going to do this quite a bit, you might also consider creating a query that has a CaseNumber field that has no dashes, and search on that:

    SELECT Replace(Case_Number, "-", "") AS CaseNumberStripped, * FROM tblMatter

    Store that as "qryStrippedCaseNumbers", and then use that to search:

    SELECT * FROM qryStrippedCaseNumbers WHERE CaseNumberStripped = '31500463DB'



    -- Scott McDaniel, Microsoft Access MVP

    Monday, June 6, 2016 9:30 PM
  • Hi todtown,

    Here I think suggestion given by the Scott McDaniel MVP can work for you.

    I have tested it and found that it gives output as per your requirement.

    I would recommend you to check the suggestion given by the Scott McDaniel MVP.

    If you think that it worked for you then mark the suggestion as an Answer.

    if you still having a problem then please let us know.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 7, 2016 1:00 AM