Help Coding Parameters for Queries

[AlphaZeroX]

What I am trying to do is create 3 (or more) parameters for a query from a single table. Lets use this for example:

Table Name= "tblExample"
Field Name "A" with Perameter "1"
Field Name "B" with Perameter "2"
Field Name "C" with Perameter "3"
(All from Table= "tblExample")

My intentions are that when the query is run, the user is asked to include 3 subjects (1 subject per perameter; 3 perameters total that pop up). But I am having trouble making it so that if a random person using this query doesn't know or can't remember 1 or 2 of the subjects they are looking for, the query will just (in a sense) ignore the two blank parameters the user has left alone, and just clicked the "ok" button without entering anything, and use the 1 parameter that it was given a subject for, to filter/query out a result.

If you beleive you will have trouble explaining this to me, I'll use this as an example:

Table Name: "tblExample"
Field Name: "A" with Parameter "1"
Field Name: "B" with Parameter "2"
Field Name: "C" with Parameter "3"
(All from Table: "tblExample")

The user uses the query and is asked by the first parameter for input. The user isn't sure, and clicks ok without entering anything, and parameter 2 pops up. The user then enters a subject of which he/she knows to look for and clicks "ok". Then the final parameter asks the user for input, and the user again doesn't know, or can't remember so he/she just presses the "ok" button.
What would be the coding for this kind of parameter that if nothing is entered, the parameter is ignored?

In desperate need of assistance. Thanks in advnace

 

[EMP]

Jon K's sample may help.
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

^

 

[AlphaZeroX]

I see where you are have mistaken this. That example is for when the records in the file are null. But what I am asking is for help of when the Parameter input is null. Like when the user does not enter anything into the parameter that pops up, for it to just ignore that particular parameter, and use whatever parameter the user could give an entry.

 

[AlphaZeroX]

It's been 2 days so far. Does anyone have any idea how to do this? Or does everyone just not understand what I'm asking?

 

[ByteMyzer]

Try something like this (make the appropriate substitutions in the highlighted text):

SELECT tblExample.*
FROM tblExample
WHERE ((Parameter1 Is Null) OR (Parameter1 Is Not Null AND tblExample.FieldA = Parameter1))
AND ((Parameter2 Is Null) OR (Parameter2 Is Not Null AND tblExample.FieldB = Parameter2))
AND ((Parameter3 Is Null) OR (Parameter3 Is Not Null AND tblExample.FieldC = Parameter3));

 

[EMP]

I see where you are have mistaken this. That example is for when the records in the file are null.

No. If you read the opening question, you will see that the example allows the user to leave the parameters blank.



Using text boxes on a form for the user to input parameters like Jon's example is more user-friendly.


If you do not want to use a form, you can set the criteria for each field like this:-
--------------------------------
Field: [A]=[Enter a value for A or leave blank] or [Enter a value for A or leave blank] is null

Show: uncheck

Criteria: True
---------------------------------

If you use a form, the parameter becomes [Forms]![FormName]![ControlName]

^

 

[pdx_man]

I believe you can just put this in the criteria for Field A:

[Enter a value for A or leave blank] or [Enter a value for A or leave blank] is null

 

[EMP]

Originally posted by pdx_man
I believe you can just put this in the criteria for Field A:

[Enter a value for A or leave blank] or [Enter a value for A or leave blank] is null

Yes, it works. But see Jon's remarks in his Note (1):

"It works. However, when the query is saved, Access splits each of such criteria into two columns and two criteria rows, making it very difficult for us to subsequently edit the criteria or add other criteria."


For three fields required by the poster, Access would use 2x3=6 columns and 2^3=8 criteria rows. And if we switch to SQL view, we would see that Access has re-arranged the criteria beyond recognition in the where clause.

^

 

[AlphaZeroX]

Try something like this (make the appropriate substitutions in the highlighted text):

SELECT tblExample.*
FROM tblExample
WHERE ((Parameter1 Is Null) OR (Parameter1 Is Not Null AND tblExample.FieldA = Parameter1))
AND ((Parameter2 Is Null) OR (Parameter2 Is Not Null AND tblExample.FieldB = Parameter2))
AND ((Parameter3 Is Null) OR (Parameter3 Is Not Null AND tblExample.FieldC = Parameter3));

I used this suggestion, and everything seemed to work out fine, but there seems to be a reading problem in acces where instead of 3 parameters popping up, there's 4. Its the second parameter that i dont want, and it pops up and keeps saying:
"tblexample.fieldA"
(actually its the actual table name and field but that really doesnt matter.)
The field name itself is called "Name" and the table is "RS Mnst Database" (ignore the database part of the name in the end, its just part of the table's name, not actually a database.)
So in summary the parameter is showing up as:

RS Mnst Database.Name

(exactly as written)
Again, this is a parameter that is showing up after the first, and it is one that I do not want to show up. It's not even a parameter but its showing up as one. I'm not sure whats going on.
Attached is a screenshot cropped to the parameter that keeps popping up to help with the understanding.

 

[Jon K]

SELECT tblExample.*
FROM tblExample
WHERE ((Parameter1 Is Null) OR (Parameter1 Is Not Null AND tblExample.FieldA = Parameter1))
AND ((Parameter2 Is Null) OR (Parameter2 Is Not Null AND tblExample.FieldB = Parameter2))
AND ((Parameter3 Is Null) OR (Parameter3 Is Not Null AND tblExample.FieldC = Parameter3));

As a parameter can only be Null or Not Null, you can simplify the Where Clause of your SQL statement by removing the "Parameter Is Not Null" parts and removing some brackets to make the statement more readable and the query more efficient. Put any table name and field names that contain embedded spaces in square brackets.

SELECT [tblExample].*
FROM [tblExample]
WHERE (Parameter1 Is Null OR [tblExample].[FieldA] = Parameter1)
AND (Parameter2 Is Null OR [tblExample].[FieldB] = Parameter2)
AND (Parameter3 Is Null OR [tblExample].[FieldC] = Parameter3);

(Do not switch this kind of query to Design View and try to save the query there or Access will re-arrange the Where Clause of your SQL statement beyond recognition as EMP has said. This advice applies also to the original non-simplified SQL statement.)


If it still doesn't work, post your SQL statement.
.

 

[AlphaZeroX]

SELECT [tblExample].*
FROM [tblExample]
WHERE (Parameter1 Is Null OR [tblExample].[FieldA] = Parameter1)
AND (Parameter2 Is Null OR [tblExample].[FieldB] = Parameter2)
AND (Parameter3 Is Null OR [tblExample].[FieldC] = Parameter3);
.

Not sure if it works yet, but a quick question. Is that bolded part of the quote above the asterick part of the Query Design View Field Select box where all the tables are selected?
If so, this wouldnt work would it? Because in order to have the asterick in the field to select all, and have a parameter for a field, you'd have to include the field twice, and then click the "show" box to where the field doesnt appear twice. Then include the parameters under the added fields.

 

[AlphaZeroX]

(sorry for double post)
Okay, after trying out the most recent suggestion by Jon K, I am still getting the extra 4th parameter asking for the same thing as included/attached in the previous post.

Here is the SQL for the query as requested.

SELECT [RS Monster Database].ID, [RS Monster Database].Name, [RS Monster Database].[Cmb Level], [RS Monster Database].[Memb Only], [RS Monster Database].HP, [RS Monster Database].XPC, [RS Monster Database].XPA, [RS Monster Database].CXP, [RS Monster Database].SXP, [RS Monster Database].SLXP
FROM [RS Monster Database]
WHERE ([Input Monster Name] Is Null OR [RS Monster Database].[Name] = [Input Monster Name)
AND ([Input Combat Level] Is Null OR [RS Monster Database].[Cmb Level] = [Input Combat Level)
AND ([Input Health Points] Is Null OR [RS Monster Database].[HP] = [Input Health Points]);

 

[AlphaZeroX]

Okay, I fixed the query code. Im not exactly sure what I did, or if what I did out of what all i've done since the last post, have had any point of doing what so ever. But i've fixed it and the only thing i can think of that i've changed was one of the field's names was "Name". And because of realizing what the extra parameter said, "[Parameter1].Name" i decided to see if it was only the field "Name" itself. So instead of using all three parameters at once, i just included the second into a query, and everything worked fine. So that explained to me that it was indeed something to do with the field "Name". So i changed the name of the field from "Name" to "NAME" and then included all three parameters into a brand new query, and everything works perfectly as needed and wanted.

Thanks again guys for sticking with me lol, I kno its been complicated but thanks again. ^_^