Query filtering using textbox from a form (1 Viewer)

[BrotherBook]

Hi-

I'm developing a report using a query as it's source data. One of the filters I am trying to apply to it uses data from a textbox on a different form. I have used this method before without issue, but it seems to be causing a problem here.

In my form I have a listbox called StageSelections. The user can make multiple selections in the listbox. I then have code that compiles the data into a text string and writes this value to a hidden text box (StageFilter). I then use StageFilter as a criteria for the Stage field in my query.

When i set the criteria to be [Forms]![Launch Reports]![StageFilter] my report always shows up with no records. When i change the criteria to be the text string in StageFilter it works fine. So it seems like it is not an issue with how I have constructed my text string, but how I am setting the query criteria.

Example 1:
[Forms]![Launch Reports]![StageFilter] = Like '*'
-generates no records, but if i just set the criteria to be Like '*' myself it works fine.

Example 2:
[Forms]![Launch Reports]![StageFilter] = In ('1 - Introductory','4 - Term Sheet Accepted & In U/W')
-generates no records, but if i just set the criteria to be In ('1 - Introductory','4 - Term Sheet Accepted & In U/W') myself it works fine.

Any idea what may be causing this? I can provide any other information you might need.

Thanks,
Mike

 

[emorris1000]

In Example 1 I'm not sure I understand what the difference in generating those two queries was, was the first one run through hand-written SQL?

 

[BrotherBook]

No. In the examples I am trying to demonstrate that when using the Design View for the query if i set the Criteria to be [Forms]![Launch Reports]![StageFilter], with the value of the string set to be "Like '*'", it returns no results. But if i set the Criteria to be "Like '*'" it returns the correct results.

There seems to be an issue with using [Forms]![Launch Reports]![StageFilter] as my criteria, because when i just copy and past the value it is holding into the Criteria, it works fine.

Hopefully that is more clear. If not I can try and post some screenshots.

 

[spikepl]

The only thing you can pass to a query from a control on a form is a VALUE. If you wish to construct different criteria, you need to either create the entire sql for the query in code, or just the criteria-part, and modify it for an existing query using the QueryDef object. Search for it in the forum, google it, and have a look at this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=243323

 

[BrotherBook]

I've successfully passed text values into my criteria in another query. In a different query i use the following criteria:

Like "*" & [Forms]![HooversProspects]![SearchText] & "*"

and it works fine. The only difference here seems to be the space in the form name and the leading and trailing *. Unfortunately I am trying to set exact values in this instance.

 

[spikepl]

And ?? That's what I wrote. You can pass VALUES. "*" is a value, "123" is a value.. "Like '*'" is NOT.