Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    3

    Red face Unanswered: Filter a report using vba

    All,

    I'm trying to filter report with no success at all.

    I've tried filtering the report using the DoCmdOpenReport object but keep getting the error message type mismatch.

    Both RecNum and the value captured within txtIncidentNumber are both set as long.

    The code reads as follows.

    DoCmd.OpenReport "rpt?????", acViewNormal, "qry??????", [RecNum] = Forms![frmIncidentHandling]![txtIncidentNumber]

    Thanks in advance

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what datatype is recnum
    a datatype mismatch is usually access speak for comparing incomaptable datatypes

    if its string you will need to enclose the supplied value with speech marks
    Code:
    DoCmd.OpenReport "rpt?????", acViewNormal, "qry??????", [RecNum] =  chr$(34) & Forms![frmIncidentHandling]![txtIncidentNumber] & chr$(34)
    it could be that some formatting is doen of Forms![frmIncidentHandling]![txtIncidentNumber]
    so if recnum is of type long do an explciit conversion to type long
    eg

    Code:
    DoCmd.OpenReport "rpt?????", acViewNormal, "qry??????", [RecNum] = clng(Forms![frmIncidentHandling]![txtIncidentNumber])
    it coudl be that you are missing a parameter, or you need to put an additioanl comma betweent he parameters you are supplying.. check the help for the open report macro
    highlight the openreport & press F1
    i'm presuming rpt????? & qry????? are you masking the true name of the report and query, otherwise that could cause a problem
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Try this:

    DoCmd.OpenReport "YourReportName", acViewPreview, , "[RecNum] = " & Forms![frmIncidentHandling]![txtIncidentNumber]

    Good luck!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    May 2009
    Posts
    3
    Guys,

    Thanks to you all for the quick response and additional thanks to StarTrekker the quotation marks have solved it.

    Once again thanks to you all

    Regards

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're most welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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