Using Like in DoCmd.openreport

[HelenJ]

I have a text box on a form where I intend the user to be able to type in eg
Sarah and retrieve from a list where sarah might be listed as Sarah or
sarah/works, or indeed accounts/sarah

(the list of names is an action list and is stored in a table)

My code reads:
strpartname = "[ActionName] Like "" *" & Me.PartName & "*"" "
DoCmd.OpenReport "ActionBySectionReport", acPreview, "", strpartname

But I get no results, yet if I query the table directly eg:
SELECT ActionBy.ActionName
FROM ActionBy
WHERE (((ActionBy.ActionName) Like "*Sarah*"));

then I get the records that I am expecting.

Any one able to help please?

Helen

 

[Allen Browne]

You have a space inside the string at the beginning of your criteria.

Try:
strpartname = "[ActionName] Like ""*" & Me.PartName & "*"""

You can also add:
Debug.Print strpartname
immediately after the line above. Then when it fails press Ctrl+G to open
the Immediate Window, and see if the condition looks wrong.

 

[John Spencer]

strpartname = "[ActionName] Like "" *" & Me.PartName & "*"" "

Note that you have a leading space in the posted example. That means that
if me.PartName is "Sarah" the clause becomes
[ActionName] Like " *Sarah*"
Note the leading space. So first thing to try is to remove that leading
space.
strpartname = "[ActionName] Like ""*" & Me.PartName & "*"" "

 

[HelenJ]

Thanks John and Allen - I was so nearly there! (the space crept in while I
was sorting the apostrophes)

strpartname = "[ActionName] Like "" *" & Me.PartName & "*"" "

Note that you have a leading space in the posted example. That means that
if me.PartName is "Sarah" the clause becomes
[ActionName] Like " *Sarah*"
Note the leading space. So first thing to try is to remove that leading
space.
strpartname = "[ActionName] Like ""*" & Me.PartName & "*"" "

I have a text box on a form where I intend the user to be able to type in
eg
Sarah and retrieve from a list where sarah might be listed as Sarah or
sarah/works, or indeed accounts/sarah

(the list of names is an action list and is stored in a table)

My code reads:
strpartname = "[ActionName] Like "" *" & Me.PartName & "*"" "
DoCmd.OpenReport "ActionBySectionReport", acPreview, "", strpartname

But I get no results, yet if I query the table directly eg:
SELECT ActionBy.ActionName
FROM ActionBy
WHERE (((ActionBy.ActionName) Like "*Sarah*"));

then I get the records that I am expecting.

Any one able to help please?

Helen