Results 1 to 7 of 7

Thread: Find First

  1. #1
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76

    Unanswered: Find First

    Hi,

    I can not get this code to work. I am trying to find a record in query1 that is equal to a post code.

    Dim db As Database
    Dim rst As Recordset
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("query1")
    Dim strCriteria As String
    Dim strPost As String

    strPost = "n3 1tr"

    strCriteria = "CompanyPostCode = strPost"
    rst.FindFirst strCriteria
    rst.Close

    This does work:

    strCriteria = "CompanyPostCode = 'n3 1tr'"

    I assume my syntax using the variable strPost is wrong. Can anyone point me in the right direction with this line of code?

    Thanks
    Simon

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It should be:
    Code:
    strCriteria = "CompanyPostCode = '" & strPost & "'"
    Have a nice day!

  3. #3
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76
    Thank you. Works perfectly.

  4. #4
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76
    My next problem is that post codes can be written with our without a space. M17 1NN or M171NN. My above code treats these two entries as different. How can I get:

    rst.findfirst strCriteria

    to treat both entries as the same value?

    Thanks
    Simon

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Add a column in the query (query1), like this:
    Code:
    SELECT <list of fields>, Replace([CompanyPostCode], " ", "") AS TCompanyPostCode FROM . . ., etc.
    This will create a field (TCompanyPostCode) based on the field (CompanyPostCode) with every space removed.

    2. Change the criteria to:
    Code:
    strCriteria = "TCompanyPostCode = '" & strPost & "'"
    Have a nice day!

  6. #6
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76
    Excellent. I have learnt the Replace function today!

    Replace(expression, find, replace)
    Replace([CompanyPostCode], " ", "")

    Thanks for your help

    Simon

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •