Establish Criteria in Query Based on Form Text Box Value

[mjseim]

I sure am having difficulty with this. I'm trying to have the criteria in my form refer to a text box within one of my forms which is the text as I would write it myself within the query design view.

Query's [DEST_ZIP5] criteria = [Forms]![Mainswitchboard]![QueryWizard].[form]![calcDestZip5]

[calcDestZip5]'s value in QueryWizard form = "55422" OR "55343"

Basically, I just want to be able to enter the text string within a form instead of within the query. Sure this can be done!

Oh, and for what it's worth...
If, [calcDestZip5]'s value in QueryWizard form = "55422"
Then the query runs just fine. It's as if the query doesn't want to accept multiple criteria from another source.

TIA

 

[mjseim]

Nobody??? If I need to clarify anything just let me know.

 

[ajetrumpet]

Basically, I just want to be able to enter the text string within a form instead of within the query. Sure this can be done!

Ive never seen syntax with [query wizard] in it before. Not sure what it means, or what you're doing, but basic form reference syntax should work.

I'm also wondering if you can even reference switchboards that are created by the program. I have rarely used one of those, so I'm not sure. I don't even know if Access reads it as a form or not. It's just a thought...that might be the problem....

It's as if the query doesn't want to accept multiple criteria from another source.

I wonder too if you might have to write extra steps if you have criteria located on an "inactive" form window (just another thought)....

 

[mjseim]

Thanks for the reply!

Basically, all I'm trying to do is create a really easy to use form that my users can use in order to create a custom query (which is a daily occurance).

In regard to switchboards that are created by Access, this is not that. I just have a habit of calling my primary form, used to navigate to different functions, the "MainSwitchboard".

In regard to your third idea, can you explain further what you meant by "write extra steps" and "inactive" form window? I didn't quite follow that.

 

[ajetrumpet]

When you have more than two forms OPEN, only one can be active (as far a I know), and that will be the window "on top" of all the others. I said this because it sounded like you might have been using two different forms with data for the criteria. If you write a query that references two forms, and only one is currently active, it will disregard the data on the inactive form and treat that reference as though it has no data. The "extra steps" referred to a VB module that you might have had to use.

If you need one form (doesn't matter how much data is on it) for the basis of your query, I would say the standard syntax for "customizing" a query that can use any combination of data (available on the form) would be...

WHERE [table].[field] = Nz(Forms![formname]![controlname] AND
[table].[field] = Nz(Forms![formname]![controlname] AND 
etc....

The "Nz" here disregards a control if it has no data. It also eliminates it from the criteria completely, so there is no affect on the resultset (i.e., no data in a control does NOT READ as "AND IS NULL" for the related field).

 

[mjseim]

As I read you last post I knew something was wrong... I looked back and found an error in my original post which I think may have confused the question. A change was made from the previous post and bolded.

As you can see, I'm only dealing with one form (that has a subform). I just want to be able to write the criteria for a query within an unbound text box in a form and have that act as my queries criteria. This is all just to avoid having users use the query design view. I can't use the a SQL query within VBA because my criteria string can be much too long (the 256character limit).


----------------------------------------------------
I sure am having difficulty with this. I'm trying to have the criteria in my QUERY refer to a text box within one of my forms which is the text as I would write it myself within the query design view.

Query's [DEST_ZIP5] criteria = [Forms]![Mainswitchboard]![QueryWizard].[form]![calcDestZip5]

[calcDestZip5]'s value in QueryWizard form = "55422" OR "55343"

Basically, I just want to be able to enter the text string within a form instead of within the query. Sure this can be done!

Oh, and for what it's worth...
If, [calcDestZip5]'s value in QueryWizard form = "55422"
Then the query runs just fine. It's as if the query doesn't want to accept multiple criteria from another source.
----------------------------------------------------