Results 1 to 6 of 6
  1. #1
    tuniwes is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    43

    Multiple criteria in VBA open form

    I know the answer to this must be quite straightforward but after searching for hours I'm going nuts and would really appreciate some help.


    I'm trying to open a form for a specific department and capture date. I can do either in a single statement but when I put them together I'm getting a mismatch error

    What am I doing wrong please, Thanks in advance
    Ian

    'DoCmd.OpenForm "form4", , , "[department]='" & Me![Department] & "'"

    DoCmd.OpenForm "form4", , , "[capdate]=#" & Me.[capdate] & "#"


    'DoCmd.OpenForm "form4", , , "[department]='" & Me![Department] & "'" And "[capdate]=#" & Me.[capdate] & "#"

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Try putting the criteria into a string and debugging it - you will see the issue very quickly. Your string concatenation is slightly off.
    Also add Option Explicit to the top of every code module.


    Code:
    Dim strCriteria as String
    
    strCriteria =  "[department]='" & Me![Department] & "'" And [capdate]=#" & Me.[capdate] & "#"
    
    Debug.Print strCriteria 
    
    DoCmd.OpenForm "form4", , , strCriteria
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    also, depends what your default date format is but if it is not the uS style of mm/dd/yyyy then you need to format it as such or format as the sql standard of yyyy-mm-dd

    so it may be
    [capdate]=#" & Me.[capdate] & "#"

    needs to be

    [capdate]=#" & format(Me.[capdate],"yyyy-mm-dd") & "#"

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Minty, still had one too many quote marks.
    Code:
    strCriteria =  "[department]='" & Me.[Department] & "' And [capdate]=#" & Me.[capdate] & "#"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by June7 View Post
    Minty, still had one too many quote marks.
    Code:
    strCriteria =  "[department]='" & Me.[Department] & "' And [capdate]=#" & Me.[capdate] & "#"
    Oops - still a Debug would highlight it
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    tuniwes is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    43
    Massive thanks to everybody, that's got me over the line, syntax like this regularly defeats me and I do try to figure it out before asking for help....now working perfectly

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

Similar Threads

  1. Open report based on multiple criteria using vba
    By Khalil Handal in forum Reports
    Replies: 19
    Last Post: 09-09-2020, 11:38 PM
  2. Open form using multiple criteria - VBA
    By jlgray0127 in forum Forms
    Replies: 6
    Last Post: 09-02-2020, 09:25 AM
  3. Replies: 10
    Last Post: 04-10-2018, 03:19 PM
  4. Replies: 2
    Last Post: 10-08-2016, 10:06 PM
  5. Replies: 1
    Last Post: 07-02-2010, 03:55 AM

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