Coding wildcards in search criteia

Discussion in 'Access Forms Coding' started by looking, Sep 5, 2012.

  1. looking

    looking Guest

    I'm unable to pass the wildcard charachter to a DoCmd.OpenReport
    statement and hope that this forum can provide some pointers.

    I use statement
    strDocWhere = "[StoreValue]=Forms![frmMyForm]![MyField]"
    in a
    DoCmd.OpenReport strDocName, acViewPreview, , strDocWhere
    for a report based on a field. It works for an exact match.
    A message box shows
    An access property sheet shows the filter value(for this eg,) as

    I'm trying to write code that will change the filter, to expand it
    from a single value to one more inclusive one but I am unable to code
    it. I hope to pass this to the form:
    ((StoreTee Like "**t0731**"))

    all the best.
    looking, Sep 5, 2012
    1. Advertisements

  2. looking

    Rob Parker Guest

    You need to build the string by combining the constant part(s) with the
    dynamic value obtained from somewhere. Currently, you're setting
    strDocWhere to the string which is showing in your message box, rather than
    a string including the value from the form control, such as is shown in your
    filter string.

    You need to set strDocWhere as follows:
    strDocWhere = "[StoreValue] = '" & Forms![frmMyForm]![MyField] & "'"

    Note the single quote characters in the pre-defined strings, to delimit the
    text value obtained from the form control. If the control contains a string
    value which includes a ' character, this will fail; it's safer therefore to
    use two double-quote characters (which will be returned as a single
    double-quote character within the string), as follows:
    strDocWhere = "[StoreValue] = """ & Forms![frmMyForm]![MyField] & """"

    To include wildcards, use the following:
    strDocWhere = "[StoreValue] Like ""*" & Forms![frmMyForm]![MyField] &

    Note: you don't need 2 * characters; the * wildcard matches any number of
    characters; use the ? wildcard character to match a single character, or a
    series of them to match a specific number of characters.


    Rob Parker, Sep 6, 2012
    1. Advertisements

  3. looking

    looking Guest

    It did help. I don't "exactly" how (yet), but your single line of code
    did exactly what I wanted. I often stumble on an acceptable syntax
    through persistence but could not in this instance. Your explanation
    with respect to potential limitations is welcome. I applaud your
    effort in particular and this group in general. I can't imagine a more
    useful resource.
    looking, Sep 6, 2012
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.