OK, here's what I need to do.

I have a query, and I have a form. The form has 3 fields, each tied to a field in the query. I want to have the query output results based on what is typed into the 3 text boxes on the form.

I know to put the form's textbox value in the criteria field, but the problem is I have 3 fields, not one. I won't get that complex here because I don't need to.

Here's what I need: If the form's text box is blank, I want EVERY value to show up in the query, and if there is a specific value in the text box, I want the query to show those. I know how to do the latter, it is the former causing me trouble. I can't quite figure out how to code the wildcard.

Basically, IIF([form]![textbox] is null, ***output every value***,[form]![textbox])

This is what I'm typing into the criteria section in the query, but I cannot figure out how to code the wildcard. I've tried "*", "%", like "*" in the "***output every value***" part of the code.

Any takers on this one????

 

Do you know the theoretical min and max values of the values you are evaluating against? If so, assume you are returning the values in a column named Item1 (an integer between 1 and 1000) depending on what is entered in txtItem1 on frmMain:


[SQL]SELECT *
FROM tblTemp WHERE tblTemp.Item1 >= IIf([Forms]![frmMain].[txtItem1] Is Null,0,[Forms]![frmMain].[txtItem1]) AND tblTemp.Item1 <= IIf([Forms]![frmMain].[txtItem1] Is Null,9999,[Forms]![frmMain].[txtItem1])[/SQL]

The same can be done if you're selecting from a text field by using ' ' and 'ZZZZZZZZZZZZZZZZZZZZZ......' instead of 0 and 9999.

 

the values represent department numbers, a 4 digit number I would probably consider a text field. Further complicating things is I want to search on two more fields, too. I hope access can do that.

I did notict that I didn't put WHERE in the expression and instead put GROUP BY I think. I hope that's it. Basically, all I want to do is return EVERY value in the query if the text box in the form is empty. The rest I can do, but it seems the wildcard coding is eluding me.

I think what you did may help, but I won't know until Monday. Seems with your code if the item is null, it will return every possible value anyway. Seems better than trying a wildcard, which hasn't worked for me.

Hope it works.
Any other Access/Database experts out there? These skills are EXTREMELY rare in my company and TRUE database experts outside of consulting firms are rare, too. Oh well, it basically helps to make sure I stay employed because my company is very grateful I know how to do this stuff. I work in finance, by the way.

 

I just made a small dbase here at home to test the concept, and it worked. Thanks!

Here's the code I wrote:
Between IIf([Forms]![Form1]![Text0] Is Null,"A",[Forms]![Form1]![Text0]) And IIf([Forms]![Form1]![Text0] Is Null,"Z",[Forms]![Form1]![Text0])

 

Ewww, Access code is fugly!

 

eh, it looks impressive, but it is much easier to write than it looks. That's actually a SIMPLE one. You should see some of my others that are 7 lines long

 

Wait a sec, you could've done all of that in SQL, couldn't you? I forget what SQL functions Access supports though.

 

He IS using SQL statements to do this. That is the problem. If he was using non-bound controls in a form, he would be able to dynamically build his query without resorting to making SQL perform his logic. That would be easy. Trying to put business logic in SQL code is hard and ugly because it doesn't belong there.

 

IIf([Forms]![Form1]![Text0] Is Null,"A",[Forms]![Form1]![Text0]) And IIf([Forms]![Form1]![Text0] Is Null,"Z",[Forms]![Form1]![Text0]) is not SQL But yea, I saw the earlier post, it's inside a SQL statement. A quick VB function (procedure? whatever the hell VB calls it) would do the trick. Death to Access!

 

sorry, iif is a VB function. There are different kinds of SQL in use, like T-SQL, ANSI92, etc. Just because you cannot use one statement on every "SQL"-processing server doesn't make one SQL and the rest "not" SQL.

Access is actually pretty powerful, and far better than anything from Oracle, Sybase, Sun, or anyone else, for what it can do. The issue is people try to do things outside the intended scope of the methods they use. Access is a Rapid Application Development tool, and as such, it works very well, for more than just database software. It has limitations, but most often, people's knowledge is the limiting factor, not Access.