1

Topic: Problem in request with parameter in Access!!!

Kind time of days. A problem in that that if we admit to assign on two or more fields parameter and at performance of request not to enter value into one of them or on the contrary to enter value only into one of parameters, the request produces the empty table. As to make what those parameters in which I were considered entered values i.e. 3 of 5, 1 of 5 etc.

2

Re: Problem in request with parameter in Access!!!

And even in addition. Looked video on  at these subjects and in comments to it there was a similar question, the answer to it was that that all problem dares by means of variables and as was specific to do it the author of video was promised to explain in a trace. Video but it does not quit later 3 years)))

3

Re: Problem in request with parameter in Access!!!

Afkshnik;
I will answer, as I understand.
Let's admit, you have in request five fields with parameters.
If the parameter is passed - that it does not mean that Aks ceases to pay to it attention, the type - well is not present, so is not present. Simply this parameter accepts value "", that is - empty value. Accordingly, the request will search for a line with coincidence on the sample of the gated in parameters and with two or how many there empty field values from "passed". And if such coincidence it is not found - you receive the blank line.
Now on your second question.
The request with parameter as it is - not so convenient piece as you were convinced. As the variant - to be played with variables, but it is not always convenient.
It is possible to make easier. Make the form, make on it empty fields with the necessary formatting. Also make the same request which as parameters will take values from form fields.
Nuances:
It can directly be necessary in request to transform value of this or that field of the form to the necessary data type.
It is in certain cases convenient to make the table with fields of the necessary data types, and it to fasten to the form on which values will be hammered into query parameters.
It can be necessary to make a series of serial requests, correctly to fulfill all variants of values, including with empty fields.

4

Re: Problem in request with parameter in Access!!!

Somehow so:

Dim myQuery As QueryDef, strSQL, p1, p2
Set myQuery = CurrentDb. QueryDefs ("VashZapros")
strSQL = "SELECT * FROM tbl WHERE"
IF IsNull (me.1) then
p1=null
Else
p1 = " (=,>, <<>, like) 1"
End if
IF IsNull (me.2) then
p2=null
Else
p2 = " (=,>, <<>, like) 2"
End if
myQueru. SQL=strSQL AND p1 AND p2
DoCmd. OpenQuery "VashZapros"

5

Re: Problem in request with parameter in Access!!!

Afkshnik;
I so understand the HARDWARE at it is necessary to implement in request a variable amount of parameters from any to for example five...
It is possible to make the form, to interpose there together with fields for parameters near flags and to drag in request and flags and fields as the selection conditions, what flags are included, such conditions and work...
Lack one - the amount of parameters is restricted also the text of request because of conditions big, for example at three parameters it is necessary to register all combinations in a condition:
000 - any parameter is not used
001 - the third parameter is used
010
011
100
101
110
111 - all parameters are used.
So normally do all  for the method demands a code minimum (in all flags and fields on one Requery).
But is more correct  depending on presence of ticks in shape to form request to a touch with the necessary parameters, especially when it is a lot of parameters...

6

Re: Problem in request with parameter in Access!!!

I ask sorry-forgot about AND and MID:

Dim myQuery As QueryDef, strSQL, p1, p2 ' how many want parameters pN
Set myQuery = CurrentDb. QueryDefs ("VashZapros")
IF IsNull (me.1) and IsNull (me.2) and... IsNull (me.N) then
myQuery. SQL = "SELECT * FROM tbl"
DoCmd. OpenQuery "VashZapros"
exit sub
End if
strSQL = "SELECT * FROM tbl WHERE"
IF IsNull (me.1) then ' or one unit IF c operators ElseIF
p1=null
Else
p1 = "and  (=,>, <<>, like) 1" ' it is similar for all parameters
End if
IF IsNull (me.2) then
p2=null
Else
p2 = "and  (=,>, <<>, like) 2"
End if
...................
myQueru. SQL=strSQL AND Mid (p1 AND p2 ....& pN, 6)
DoCmd. OpenQuery "VashZapros"

[/quote]

7

Re: Problem in request with parameter in Access!!!

Tarasios, that's it with the form I also try to implement it. While it turned out to make through a macro a command if and to apply the filter and that only two fields if more than fields that commands in a macro very long and here in request at least as in shape at me it did not turn out, I will try examples resulted more low. If at whom the thread still is ideas all I will try

8

Re: Problem in request with parameter in Access!!!

sdku;
I so understand this code is written in a mode sql in request?

9

Re: Problem in request with parameter in Access!!!

Afkshnik wrote:

If at whom the thread still is ideas all I will try

You  the decision would put, and that continuous game of type
Also it is finite easier to do by the code , instead of a macro
It is necessary to make the form on which to allocate
-- for input of values of your parameters
--And the button for start of the code dynamic forming the filter on the basis of entered

10

Re: Problem in request with parameter in Access!!!

Afkshnik;
Example of dynamic search and the code

11

Re: Problem in request with parameter in Access!!!

Afkshnik;
The code decreases time in 3 if filtration to do not on-simvolno, and by the button
(There will be 4 lines in the field +10 lines of the general +10 lines, if a filtration unrenewable request)

12

Re: Problem in request with parameter in Access!!!

Afkshnik wrote:

sdku;
I so understand this code is written in a mode sql in request?

This code is written on button click event in that form which you create. Still correct: or a gap add after WHERE, or 5 instead of 6 in MID
What for request? Look it-form "1":