Coding wildcards in search criteia

  • Thread starter Thread starter looking
  • Start date Start date
L

looking

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.
 
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
 
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,
Click to expand...

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 said:
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.
Click to expand...
Click to expand...
 
Back
Top