Use a string as criteria to run query

[Guest]

Hi there,

I am trying to pass the value from a text box on frmInputBoxRptSkills to a
query which will filter results in two tables based on the contents of the
text box and provide the basis for a report.

The problem I am having is that the text box may contain a wildcard and part
of a role title and I cannot figure out how to get the query to find all
roles "like" the contents of the text box, e.g. I want to search for all
roles with Training in the title.

Can anyone give me some code to help me out?

Thanks
A

 

[Douglas J. Steele]

Use

Like "*" & Forms!frmInputBoxRptSkills!NameOfTextbox & "*"

as your criteria.

Or, if you only want wildcards if the user types them in, try

Like Forms!frmInputBoxRptSkills!NameOfTextbox

 

[Guest]

D'ya know, I've been trying this all day and I'm sure I've typed that in once!

It works! You are genius.

Thanks
tigger

 

[Guest]

One more question, Doug.

If I wish to use the data from frmInputBoxRptSkills.txtRoleSearch to run an
existing query that has no criteria set but then apply the criteria from the
text box (to power a report), how could I do that?

Thanks

 

[Douglas J. Steele]

DoCmd.OpenReport "MyReport", acViewPreview, , _
"MyField Like '*" & Forms!frmInputBoxRptSkills!NameOfTextbox & "*'"

Exagerated for clarity, that last line is

"MyField Like ' * " & Forms!frmInputBoxRptSkills!NameOfTextbox & " * ' "