Results 1 to 5 of 5
  1. #1
    Anna987 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2015
    Posts
    9

    rs.FindFirst with Multiple Criteria

    Hello,



    I try to use the findfirst instruction with multiple criteria.
    I use this table where Nr is a autonumberfield and ID and ID2 are numeric fields



    The code i wrote :


    Dim db As Database
    Dim RS As Recordset
    Set db = CurrentDb
    Set RS = db.OpenRecordset("test", dbOpenDynaset)
    Dim strCriteria As String


    RS.FindFirst "[ID] =" & 1 And "[ID2] =" & 2



    I get an error 'Type mismatch'

    Can someone tell me how to fix this?

    Thanks in advance.


    Anna

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    First 1 and 2 are pretty silliy names for variables?, which do not appear to be declared anyway?

    You need to concatenate the syntax and have only done half of it?

    Try
    Code:
    RS.FindFirst "[ID] =" & lngID1 & " And [ID2] =" & lngID2
    Tip: When the criteria is no longer simple, put it in a string so you can debug.print it.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Anna987 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2015
    Posts
    9
    I just setup a test table just to be sure, that explanes the silly names
    I have tested your code and it works fine.
    Thanks for your reply.

    Regards, Anna

  4. #4
    brigitteAT is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    61

    Red face

    Quote Originally Posted by Welshgasman View Post
    First 1 and 2 are pretty silliy names for variables?, which do not appear to be declared anyway?

    You need to concatenate the syntax and have only done half of it?

    Try
    Code:
    RS.FindFirst "[ID] =" & lngID1 & " And [ID2] =" & lngID2
    Tip: When the criteria is no longer simple, put it in a string so you can debug.print it.


    Thank you again - this example helped me to do my first correctly working "rs.firstfind"

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    @brigitteAT

    Although Anna987's error was not with the type of variable, but the words, that syntax is not just for FindFirst?. It is used extensively in a lot of functions in Access, DlookUp(), DSum() etc.
    Unless it is just one criteria, I tend to put the criteria into a string variable and then Debug.Print it to make sure the syntax is correct.

    Text should be surrounded by ' unless the value also holds a ' like O'Hara, then use triple " I believe (you will need to test that, as not something I use much)
    Dates by # and in format mm/dd/yyyy or yyyy-mm-dd (I tend to use mm/dd/yyyy, even though in the UK we use dd/mm/yyyy)
    Numerics have nothing surrounding them.


    HTH
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Findfirst problem with Environ as criteria.
    By jtm013 in forum Programming
    Replies: 4
    Last Post: 07-09-2015, 02:22 PM
  2. Replies: 5
    Last Post: 08-20-2014, 10:18 PM
  3. FINDFIRST Multiple criteria
    By trigirl67 in forum Forms
    Replies: 1
    Last Post: 01-31-2012, 02:02 PM
  4. FindFirst with multiple fields in criteria
    By compooper in forum Programming
    Replies: 5
    Last Post: 07-22-2011, 10:29 AM
  5. using FindFirst with a Date Criteria (syntax error)
    By ajetrumpet in forum Programming
    Replies: 2
    Last Post: 09-10-2010, 11:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums