Simple Access Help...hopefully

[Mambozzy]

Alright, I've designed a database to be as user-friendly as possible. Currently, I have a button to run a parameter query and put it all on a report. There are three variables (school, city, ASVAB score) and the user must have the option to use all, none and some of the 'filters'.
I have the whole "criteria: [name of school:], Or: Is null" code in and it will work if I enter perameters for every field and it will show all entries if I enter no parameters. However, if I enter one parameter and leave another blank, I recieve no results and for the life of me can't figure out why. Please help.
-Oz

 

[sathandle]

Try this:
enter a choice for school
enter * for city
enter * for ASVAB

* should return all items in that column.

Good luck!
SAThandle
definition of 'less behind': "not fully caught up, digging out slowly, one-week delay to "The IT hit the fan."

 

[Mambozzy]

No good, Sat. It's not recognizing the * as either a wildcard or an entry character. I don't get it, it's like light: Is it a wave, is it a particle? It's really counfusing, it was it is! lol. Thanks, though.

 

[sathandle]

Are all of your parameters on the same line of criteria?

If not move the second one to the second line, third, third...

definition of 'less behind': "not fully caught up, digging out slowly, one-week delay to "The IT hit the fan."

 

[Mambozzy]

I'm not sure I understand what you mean. In the Query design view, yes my parameters (i.e. "[Name of School:]&quot are on the same line (Critera. On the Or: line, I have "Is Null" pertaining to each entry to allow a field to have no entry. I'm screwing up something very simple here, but I just can't grasp it. Thanks again for your help, Sat

 

[sathandle]

school column of query
Criteria L1:[Enter school] or is null
Criteria L2: empty
Criteria L3: empty

city column of query
Criteria L1: empty
Criteria L2: [Enter city] or is null
Criteria L3: empty

ASVAB column of query
Criteria L1: empty
Criteria L2: empty
Criteria L3: [Enter ASVAB] or is null

definition of 'less behind': "not fully caught up, digging out slowly, one-week delay to "The IT hit the fan."

 

[amulrine]

Here's what we use:
Like IIf(RptProject()=0,"*",RptProject())

where RptProject() is function that gets the value of a list box selection from the form that gives the user parameter choices. You can just fill in the values from what the user enters, also.

 

[Mambozzy]

But how do I add additional criteria lines, sat? I have one row of cells for 'Criteria:' and then an infinite number of cells for 'Or:'.

 

[Mambozzy]

I see where you're going, amulrine, but I'm not having the user select from a list box, it's 'fill in the blank'. Can I still use that code? If so, how? Again, I appreciate all your help.
-Oz

 

[sathandle]

The "Or:" lines are Criteria lines.

So...

school column of query
Criteria L1:[Enter school] or is null
Or L1: empty
Or L2: empty

city column of query
Criteria L1: empty
Or L1: [Enter city] or is null
Or L2: empty

ASVAB column of query
Criteria L1: empty
Or L1: empty
Or L2: [Enter ASVAB] or is null

definition of 'less behind': "not fully caught up, digging out slowly, one-week delay to "The IT hit the fan."

 

[amulrine]

You can still use the code I provided by pointing to the text box where the criteria is being typed.

Here's the function we use:
Function RptProject() As Integer
RptProject = IIf(IsNull(Forms![frmReportsAvailable]![lstProject]), 0, Forms![frmReportsAvailable]![lstProject])
End Function

We choose to use a list box, but you can change the code to grab the data in your list box.

Hope this helps.
Andrea

 

[Mambozzy]

Hey, everyone, thanks for your help. I eventually got it to work thanks to your help. I really appreciate it. You RULE!!! Thanks, later.
-Oz

 

[sathandle]

What was the solution?
Thanks. definition of 'less behind': "not fully caught up, digging out slowly, one-week delay to "The IT hit the fan."

 

[Mambozzy]

Ah, it was the "put the code on different lines" thing. Worked great after that. Thanks again, sat.
-Oz