Results 1 to 11 of 11

doCmd.OpenReport - Multiple Where Conditions

  1. #1
    jml9012 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    1

    doCmd.OpenReport - Multiple Where Conditions

    Hello Everyone,

    I am currently making a form that using combo boxes to filter a query for a report. I am using doCmd to open the report using two where conditions. The where conditions get their values from the combo box drop down list. As you can see below, I am attempting to open the report "FORM x" with multiple argument where conditions joining them with AND. However when I run this command I get a type mismatch error. I have tried isolating the issue by only using one where condition at a time and have found that filtering by date only or by vendorID only works perfectly fine. But when I try to combine the two where statements into one so I can filter by both parameters at once I receive a type mismatch error.

    Works
    Code:
        DoCmd.OpenReport "FORM x", acViewPreview, , " [Date] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
    Works
    Code:
        DoCmd.OpenReport "FORM x", acViewPreview, , "[VendorID]='" & Me.cboVendorID & "'"
    Doesn't Work
    Code:
        DoCmd.OpenReport "FORM x", acViewPreview, , "[VendorID]='" & Me.cboVendorID & "'" And " [Date] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"

    Any help at all would be extremely appreciative.

    Thank you
    Josh

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,485
    The double quotes surrounding And are causing a problem.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    Hope I can keep the quotes straight.

    Code:
    DoCmd.OpenReport "FORM x", acViewPreview, , "[VendorID]='" & Me.cboVendorID & "'" And " [Date] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
    Docmd.OpenForm,"Formx",AcPreview,,"VendorID =" & me.cboVendorID & "AND # [Date] # BETWEEN # '" & me.cboFrom & "' AND # '" & me.cboTo & "#'"

    Numbers do NOT use any quotes.

    Text-Date need quotes.

    Hope this is something close.
    I am not sure what data type [Date] is the # may not be needed.
    Summary, Don't quote VendorID, Single quote the text-date variables.


    Dale

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,485
    Not sure which is supposed to be the correct version Dale; they're both wrong. The data types can be derived from the OP.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  5. #5
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    Thanks Paul.

    Can you tell me where I went wrong.

    I have a terrible time with quotes if the statement if very complex.


    Dale

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,485
    Like I said, the only thing wrong with the code Josh posted was the double quotes on either side of And (the first And). It's a common mistake. You have two strings that work independently. You know you have to join them with And. The common mistake Josh made is not removing the quotes that ended the first string, since you no longer want to end that string, and the quotes that started the second string. So the correct string:

    Code:
    DoCmd.OpenReport "FORM x", acViewPreview, , "[VendorID]='" & Me.cboVendorID & "' And [Date] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  7. #7
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    Thanks Paul.

    I see I could use mucho work on my quotes, Still.

    Thanks,
    Dale

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,485
    Plus you had # around the field name, # and ' in a couple of places instead of one or the other. Don't worry, at some point it just all falls into place.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  9. #9
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    I hope so.
    I have gotten a grasp on about everything I have learned so far but these cotton picked quotes.

    EDIT: I figured after I posted the # around the date field was wrong.

    Thanks again for fixing it.

    Dale

  10. #10
    TomChandler is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    San Jose, CA
    Posts
    1
    Quotes can be a pain. An alternative is to use Chr(34). I also prefer to use explicit notation when writing code. You might try this:

    DoCmd.OpenReport _
    ReportName:="FORM x", _
    View:=acViewPreview, _
    WhereCondition:="[VendorID] = " & Chr(34) & Me.cboVendorID & Chr(34) _
    & " AND [Date] Between #" & Me.cboFrom & "# AND #" & Me.cboTo & "#"

    Last edited by TomChandler; 05-20-2013 at 10:14 PM.

  11. #11
    ssanfu is offline VIP
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    3,487
    Not to mention that "Date" as a field name is a terrible idea!!
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. DoCmd.OpenReport WHERE condition
    By bidbud68 in forum Programming
    Replies: 16
    Last Post: 10-19-2012, 04:31 AM
  2. Need help w/ docmd.openreport
    By jwill in forum Reports
    Replies: 3
    Last Post: 06-04-2012, 08:49 PM
  3. Replies: 2
    Last Post: 10-13-2011, 08:14 AM
  4. DoCmd OpenReport ... where condition with a Like
    By Grooz13 in forum Programming
    Replies: 4
    Last Post: 08-31-2010, 08:04 AM
  5. Can docmd.openreport print X copies on Y printer?
    By Coolpapabell in forum Reports
    Replies: 1
    Last Post: 09-02-2009, 07:35 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
  •  
Tech Forums: Microsoft Office Forums