Criteria Problem

[R32chaos]

I have the following criteria under [location] field in a query:

Like [Enter location code: ND for North Dallas, SM for South McAllen, etc or leave blank for all records] & "*"

Is there any way to modify this code so that when the query is run, I can type something like [ "ND" or "SM" or "RD" ], and then the query may show me only the ND, SM, and RD location combined together versus having to run it three times for each location individually? In other words, I dont want it to restrict me to type one specific city codes at a time.
________________________
I am basing the above requirment on the following example which actually looks at a form where a user will make the selections in up to 8 fields and then run the query. So if it can be done where the criteria is looking at up to 8 fields in a form, why can it not utilize the same principle by entering the information whenever prompted?
Here is the 2nd example:
[forms]![form1]![ss] Or [forms]![form1]![ss1] Or [forms]![form1]![ss2] Or [forms]![form1]![ss3] Or [forms]![form1]![ss4] Or [forms]![form1]![ss5] Or [forms]![form1]![ss6] Or [forms]![form1]![ss7] Or [forms]![form1]![ss8]
This works great. They can choose any and all fields in form1 while the query provides all information efficiently.
________________________
I am just trying to get away from having to develop specific forms for each and every such query's convenient criteria utilization.

 

[WayneRyan]

R32,

As far as I know Access is not that flexible.

There have been a lot of posts here where people wanted a form control to
have a criteria like --> Forms!SomeForm!SomeField

where SomeField has a value like --> In ('a', 'b', 'c')

I think that you'll have to Search here for QueryDefs and build your own
SQL strings. It's not that difficult.

Wayne

 

[Mike375]

Your second example, that is the [forms]![form1]![ss] Or [forms]![form1]![ss1] etc will work. You can heaps of them. I have one with 32 of them.

Make the ss, ss1 etc unbound text boxes and type in your ND, SM each into one box. You don't need to fill all boxes. In other words if you have 10 unbound textboxes and just ND in one of them that is OK

However, if it is North Dallas that is the actual field entry then things will be different

Make a combo that will stick the field entry in one of the boxes.

 

[R32chaos]

Yes Mike375, that is precisely how I have made the form1. I am trying to get away from that by creating a criteria which can allow user entry instead of being form non-bound field driven.

WayneRyan has a good suggestion, please enlighten me further on it as I am not that familiar with SQL Strings...

Again, thank you for both your responses...hoping to continually dig for a solution

 

[R32chaos]

Criteria Problem...

I have been able to create a form which contains 11 fields. The purpose of the form and fields is to help with a query criteria.
A field in the query which depends on these 11 fields has the following criteria:

[forms]![RDmonthlyReports]![l1] Or [forms]![RDmonthlyReports]![l2] Or [forms]![RDmonthlyReports]![l3] Or [forms]![RDmonthlyReports]![l4] Or [forms]![RDmonthlyReports]![l5] Or [forms]![RDmonthlyReports]![l6] Or [forms]![RDmonthlyReports]![l7] Or [forms]![RDmonthlyReports]![l8]Or [forms]![RDmonthlyReports]![l9]Or [forms]![RDmonthlyReports]![l10]Or [forms]![RDmonthlyReports]![l11]

This all helps, however, I would like for the query to show all records if all of these fields are left blank. I have tried "*"& in the beginning and at the end; it helps but with sporradic problems. Your help will be very much appreciated...

Thank you!

 

[dkinley]

You can have a form reference for 100 columns in query if you want. Your original post was correct ...

Like [Forms]![FormName]![txtUnboundTextBoxName] & "*"

Use something similar in each column, you can then limit the query return. If your query is composed of 1 or more tables, it won't work unless you use left joins.

-dK

 

[R32chaos]

Criteria Problem...

Thank you dkinley...I got everything going well with fields and how it works with the criteria. The problem is, i want the query to show all records when all 11 fields on the form are left blank. Thats where I am having some trouble.

The criteria is working fine like this:

[forms]![RDmonthlyReports]![l1] Or [forms]![RDmonthlyReports]![l2]

its when I add the following to it, is when it misbehaves:
"*"& [forms]![RDmonthlyReports]![l1] Or [forms]![RDmonthlyReports]![l2] &"*"


Thanks in advance!

 

[dkinley]

When the 11 fields are left blank .... I am not sure what this exactly means, but the Like [][][] & "*" ought to cover the fields that do not have criteria.

If the fields are blank because they have null values you might have to input across the critieria rows Is Null | Is Null | Like [][] ... so on and so forth and fill up all the rows mixing the pattern up.

Again, if it's joined tables that could be causing the fields to come up blank, you will need to use left joins for those to work.

-dK

 

[R32chaos]

Criteria Problem...

Thank you dkinley...
Thats the setup I had with the &"*" yet I get weird behavior.

Here's another thought;

...suppose I make a 12th field in the form and when the 11 fields are left blank, the 12th one could have a wild card value which the query could consider and show all records...I am not sure however, what type of wild card value to assign to the 12th unbound field which the query criteria could refer to...

I have tried "*" and "[a-z]*"...so far no success, any suggestions?

Thanks in advance!

 

[dkinley]

Is there any way you can post the db? Perhaps strip out everything but the one query and table(s) the query uses?

-dK

 

[R32chaos]

Thanks dkinley,

Before I figure out ways to attach a sample of the db, i wonder why the an iif statement cannot work in a query...for instance, I could actually create a text box in a form which will have certain criteria in it based on some conditions. The query criteria can be told to simply look at that text box for criteria; I have tried this and it does not seem to work. So, how can I create a functioning iif statement regulated criteria, such as follows:

IIf(([forms]![RDmonthlyReports]![tot])<1,Like "*",([forms]![RDmonthlyReports]![l1] Or [forms]![RDmonthlyReports]![l2] Or [forms]![RDmonthlyReports]![l3] Or [forms]![RDmonthlyReports]![l4] Or [forms]![RDmonthlyReports]![l5] Or [forms]![RDmonthlyReports]![l6] Or [forms]![RDmonthlyReports]![l7] Or [forms]![RDmonthlyReports]![l8] Or [forms]![RDmonthlyReports]![l9] Or [forms]![RDmonthlyReports]![l10] Or [forms]![RDmonthlyReports]![l11]))

Thx again!

 

[dkinley]

Errr .. you can, at least, I know it is possible. I was just donating to thread on what I am thinking you are trying to do. The only caveat I see to the IIF ad infinum is it is hardcoded and what it one more thing was added to the criteria. You are now back in the trenches trying to untangle the hardcoding and add to it, or let the query just point to 10 or 20 combo boxes for the criteria.

However, the Switch function might be better in this instance.

I am sorry, I can work in the query builder, will have to defer to someone else to interpret all of that (I am not that good).

-dK

 

[R32chaos]

Thank you for all your efforts dkinley...

Problem is solved. Basically, I created a 12th invisible field in the form. The field will automatically contain an iif statement where it returned "*" value if the other 11 fields happened to be blank. The query criteria had to read the following statement and it worked!

[forms]![RDmonthlyReports]![l1] Or [forms]![RDmonthlyReports]![l2] Or [forms]![RDmonthlyReports]![l3] Or [forms]![RDmonthlyReports]![l4] Or [forms]![RDmonthlyReports]![l5] Or [forms]![RDmonthlyReports]![l6] Or [forms]![RDmonthlyReports]![l7] Or [forms]![RDmonthlyReports]![l8] Or [forms]![RDmonthlyReports]![l9] Or [forms]![RDmonthlyReports]![l10] Or [forms]![RDmonthlyReports]![l11] Or Like [forms]![RDmonthlyReports]![l12]

The odd thing is; the 12 field which is invisible had to be made a combo box for the query to accept it as a contender and actually utilize its value...and now it works!

I hope this will help expand your horizons as well; we all learn something new from each challenge...

Thanks again!

 

[dkinley]

I hope this will help expand your horizons as well; we all learn something new from each challenge...

Amen ... awesome you figured it out!

-dK

 

[R32chaos]

Dlookup from a query

I have a totals query, "qrsChildren" in which I have only three fields:
1-Lastname (is set to count, and returns the correct totals titled: countofLasName)
2-facility# (in criteria; forms![frmFosterFamily]![facility#])
3-current (is set to "yes")

When run, the query works fine and shows me a total count of lastname. I want that number to show on a form. I have used the dlookup function in a non-bound control field as follows but it does not work:

dlookup("[lastname]","qrsChildren")
I have tried dsum("[lastname]","qrsChildren") also...nothing works

Any help will be very much appreciated...Thank you!

ps. it is not very easy to figure out how to create a new post on this website...so I am using an old post