Coding wildcards in search criteia

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

  1. looking

    looking Guest

    Hi,
    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
    [StoreValue]=Forms![frmMyForm]![MyField]
    An access property sheet shows the filter value(for this eg,) as
    ((StoreValue="t0731"))

    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
    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.

    HTH,

    Rob

    "looking" <> wrote in message
    news:...
    > Hi,
    > 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
    > [StoreValue]=Forms![frmMyForm]![MyField]
    > An access property sheet shows the filter value(for this eg,) as
    > ((StoreValue="t0731"))
    >
    > 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.
     
    Rob Parker, Sep 6, 2012
    #2
    1. Advertisements

  3. looking

    looking Guest

    On Thu, 6 Sep 2012 13:43:09 +1000, "Rob Parker"
    <> wrote:

    >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.
    >
    >HTH,


    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.

    >
    >Rob
    >
    >"looking" <> wrote in message
    >news:...
    >> Hi,
    >> 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
    >> [StoreValue]=Forms![frmMyForm]![MyField]
    >> An access property sheet shows the filter value(for this eg,) as
    >> ((StoreValue="t0731"))
    >>
    >> 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 6, 2012
    #3
    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.