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

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. James

    Problems with coding and coding question!!

    James, Feb 23, 2004, in forum: Access Forms Coding
    Replies:
    0
    Views:
    211
    James
    Feb 23, 2004
  2. James

    Coding a Search Button

    James, Apr 14, 2004, in forum: Access Forms Coding
    Replies:
    10
    Views:
    132
    Treebeard
    Apr 17, 2004
  3. James

    Coding a Search Button

    James, Apr 17, 2004, in forum: Access Forms Coding
    Replies:
    0
    Views:
    70
    James
    Apr 17, 2004
  4. James

    Repost: Coding a Search Button

    James, Apr 21, 2004, in forum: Access Forms Coding
    Replies:
    3
    Views:
    100
    James
    Apr 22, 2004
  5. James

    REPOST: (No Response) Coding A Search Button

    James, May 10, 2004, in forum: Access Forms Coding
    Replies:
    2
    Views:
    89
    James
    May 10, 2004
  6. Noemi
    Replies:
    1
    Views:
    85
    Rob Oldfield
    Mar 6, 2005
  7. Flexible Search and Wildcards

    , May 1, 2007, in forum: Access Forms Coding
    Replies:
    2
    Views:
    94
  8. Barry A&P
    Replies:
    4
    Views:
    78
    Barry A&P
    Mar 25, 2010
Loading...