Unable to set query parameter

[Neil]

I'm having problem setting a parameter in an append query. I want to
optionally use all records or only active records (Inactive field = False).
In the Inactive field of the query, I have this:

0 or [blnIncludeInactives]

If I want to use all records, I set blnIncludeInactives to -1; if I only
want to use active records, I set blnIncludeInactives to 0. Only problem is,
I'm always getting only active records.

I have defined blnIncludeInactives as a query parameter, and set its type to
Yes/No. I'm using the following code to set the parameter and execute the
query:

Set qd = CurrentDB.QueryDefs("qappCustSummary_WantList_All")
qd.Parameters("blnIncludeInactives") = blnWantInactives
qd.Execute dbFailOnError

blnWantInactives is a boolean variable that contains the True/False value to
use here.

I have tried manually replacing blnIncludeInactives with the value "-1" (so
that the criteria reads "0 or -1" instead of "0 or [blnIncludeInactives]"),
and the query worked fine, appending both active and inactive records. So I
know it's not the query itself that's the problem. But I can't set
blnIncludeInactives to -1.

What am I doing wrong?

Thanks!

Neil

 

[Dale Fye]

Post the SQL string for your query.

Have you tried running the query from the query grid, without changing the
criteria manually? If so, and you have the parameter declared properly, you
should see a popup window asking for the value of the parameter. If you type
-1 into that parameter window, does it work properly?

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Marshall Barton]

I'm having problem setting a parameter in an append query. I want to
optionally use all records or only active records (Inactive field = False).
In the Inactive field of the query, I have this:

0 or [blnIncludeInactives]

If I want to use all records, I set blnIncludeInactives to -1; if I only
want to use active records, I set blnIncludeInactives to 0. Only problem is,
I'm always getting only active records.

I have defined blnIncludeInactives as a query parameter, and set its type to
Yes/No. I'm using the following code to set the parameter and execute the
query:

Set qd = CurrentDB.QueryDefs("qappCustSummary_WantList_All")
qd.Parameters("blnIncludeInactives") = blnWantInactives
qd.Execute dbFailOnError

blnWantInactives is a boolean variable that contains the True/False value to
use here.

I have tried manually replacing blnIncludeInactives with the value "-1" (so
that the criteria reads "0 or -1" instead of "0 or [blnIncludeInactives]"),
and the query worked fine, appending both active and inactive records. So I
know it's not the query itself that's the problem. But I can't set
blnIncludeInactives to -1.

Take a look at youe query's SQL view and inspect the WHERE
clause. When you use a criteria like A Or B, the query
designer constructs the WHERE clause to"
WHERE field = A OR field = B

You want the WHERE clause to be:
WHERE field = A OR B

If you edit the SQL view WHERE clause and then switch back
to the designer, Access will put the B in a calculated field
with the criteria set to True. All very confusing, but it
will work,

 

[Neil]

Take a look at youe query's SQL view and inspect the WHERE
clause. When you use a criteria like A Or B, the query
designer constructs the WHERE clause to"
WHERE field = A OR field = B

You want the WHERE clause to be:
WHERE field = A OR B

If you edit the SQL view WHERE clause and then switch back
to the designer, Access will put the B in a calculated field
with the criteria set to True. All very confusing, but it
will work,

Don't you mean that the WHERE clause should be:

WHERE field = A OR ((field = B)=True)

?

If B is my parameter value, then what is accomplished by putting B in a
column by itself without a reference to the actual field? If the column says
=True, and I pass a value of True or False for B, then what is
accomplished? Or am I missing something?

Thanks,

Neil

 

[Neil]

Take a look at youe query's SQL view and inspect the WHERE
clause. When you use a criteria like A Or B, the query
designer constructs the WHERE clause to"
WHERE field = A OR field = B

You want the WHERE clause to be:
WHERE field = A OR B

If you edit the SQL view WHERE clause and then switch back
to the designer, Access will put the B in a calculated field
with the criteria set to True. All very confusing, but it
will work,

BTW, I just tried it your way (above) and my way (previous message), and
your way works, but mine doesn't. But I'm totally lost. Why would "OR
<>False" affect anything? If I pass True for B (include inactives), then
that statement resolves to True; but it has no reference to the Inactive
field, itself; so why should it affect the query? Totally confused here.

 

[Neil]

Take a look at youe query's SQL view and inspect the WHERE
clause. When you use a criteria like A Or B, the query
designer constructs the WHERE clause to"
WHERE field = A OR field = B

You want the WHERE clause to be:
WHERE field = A OR B

If you edit the SQL view WHERE clause and then switch back
to the designer, Access will put the B in a calculated field
with the criteria set to True. All very confusing, but it
will work,

OK, figured it out. The criteria ( OR <>False) causes the query to return
ALL records when B = True, essentially overriding the first parameter.
That's fine in this case, where it's a "some or all" type thing; but in a
case where it's "some or some other" that wouldn't work.

I tried a different approach, going back to my original mechanism (Field = A
or , where is the parameter). I simply wrapped in an Eval()
function, and it worked! Apparently, even though you declare a parameter and
declare it as a Yes/No type field, Access is too dumb to actually know that
it's a Yes/No field, but, presumably, treats it as a string field. So by
doing the Eval() it converted it to a true true/false value, and caused the
query to work. And, in this way, it would work even if one didn't want all
records as the other option.

Thanks for your note!

Neil

 

[Neil]

Have you tried running the query from the query grid, without changing the

criteria manually? If so, and you have the parameter declared properly,
you
should see a popup window asking for the value of the parameter. If you
type
-1 into that parameter window, does it work properly?

No, it didn't work then either, when I entered a -1 when prompted. Only if I
manually changed the [blnIncludeInactives] to a -1 did it work.

But I found a solution: if I wrap it in Eval() it works. So the criteria row
now has:

0 or Eval([blnIncludeInactives])

and it works perfectly. Apparently, though I declared the parameter as type
Yes/No, Access wasn't able to convert -1 to a true Yes/No value. (And, yes,
I did try setting the parameter to both "True" and "-1" in code; neither
worked.)

Neil

 

[Marshall Barton]

Take a look at youe query's SQL view and inspect the WHERE
clause. When you use a criteria like A Or B, the query
designer constructs the WHERE clause to"
WHERE field = A OR field = B

You want the WHERE clause to be:
WHERE field = A OR B

If you edit the SQL view WHERE clause and then switch back
to the designer, Access will put the B in a calculated field
with the criteria set to True. All very confusing, but it
will work,

OK, figured it out. The criteria ( OR <>False) causes the query to return
ALL records when B = True, essentially overriding the first parameter.
That's fine in this case, where it's a "some or all" type thing; but in a
case where it's "some or some other" that wouldn't work.

I tried a different approach, going back to my original mechanism (Field = A
or , where is the parameter). I simply wrapped in an Eval()
function, and it worked! Apparently, even though you declare a parameter and
declare it as a Yes/No type field, Access is too dumb to actually know that
it's a Yes/No field, but, presumably, treats it as a string field. So by
doing the Eval() it converted it to a true true/false value, and caused the
query to work. And, in this way, it would work even if one didn't want all
records as the other option.

Using Eval(B) makes it complex enough to get the query
designer to construct a WHERE clause like:

WHERE field = A OR Eval(B)

which is just a slow, roundabout way of doing what I
suggested.

The key point is that the second criteria is supposed to
override the first criteria. It has nothing to do with the
field's value, which it seems like you have already figured
that out.

Remember that my initial reply tried to clarify that all
your confusion is caused by using the query designer. If
you skipped that crutch, the SQL statement's WHERE clause
would be simple and clear.

 

[Neil]

Take a look at youe query's SQL view and inspect the WHERE
clause. When you use a criteria like A Or B, the query
designer constructs the WHERE clause to"
WHERE field = A OR field = B

You want the WHERE clause to be:
WHERE field = A OR B

If you edit the SQL view WHERE clause and then switch back
to the designer, Access will put the B in a calculated field
with the criteria set to True. All very confusing, but it
will work,

OK, figured it out. The criteria ( OR <>False) causes the query to
return
ALL records when B = True, essentially overriding the first parameter.
That's fine in this case, where it's a "some or all" type thing; but in a
case where it's "some or some other" that wouldn't work.

I tried a different approach, going back to my original mechanism (Field =
A
or , where is the parameter). I simply wrapped in an Eval()
function, and it worked! Apparently, even though you declare a parameter
and
declare it as a Yes/No type field, Access is too dumb to actually know
that
it's a Yes/No field, but, presumably, treats it as a string field. So by
doing the Eval() it converted it to a true true/false value, and caused
the
query to work. And, in this way, it would work even if one didn't want all
records as the other option.

Using Eval(B) makes it complex enough to get the query
designer to construct a WHERE clause like:

WHERE field = A OR Eval(B)

which is just a slow, roundabout way of doing what I
suggested.

I disagree. The field in question is a SQL Server smallint field (used
instead of bit), which is Integer in Access. All values are 0 or -1. With my
test record, there are two items for which Inactive = 0, and 4 for which
Inactive = -1. Using your solution, the query uses all six records, as one
would expect. Same with my solution (using Eval()).

However, to test whether my solution was forcing Access to correctly
evaluation the value, or was just returning all records, as yours does, I
changed one of the -1 values to -2, and passed a value of -1 for the "B"
parameter.

With your solution, all six records were used, as one would expect. However,
with my Eval() solution, only FIVE records were used. The record with a
value of -2 was skipped.

Thus, my solution is not forcing Access to ignore the first parameter and
just return all records; it's actually causing Access to evaluate the
criteria correctly (field = A or field = B, where A=0 and B=-1 should not
return the -2 value, which it didn't).

Thus, I disagree that mine is doing the same as yours.

The key point is that the second criteria is supposed to
override the first criteria. It has nothing to do with the
field's value, which it seems like you have already figured
that out.

Right, I figured out that that was how your solution worked.

Remember that my initial reply tried to clarify that all
your confusion is caused by using the query designer. If
you skipped that crutch, the SQL statement's WHERE clause
would be simple and clear.

The query designer *should* work. If one puts "0 or -1" in the criteria row,
that works. So one *should* be able to put "0 or " in the criteria row
and be able to pass it a value of -1 for and get the same results. And,
indeed, with Eval(), you can. But, as noted, the key difference here is that
one is not forced to simply return all records. If there are some records
which are not 0 or -1 (or which are Null)they would not be returned with the
Eval() solution.

Thanks,

Neil

 

[Marshall Barton]

Take a look at youe query's SQL view and inspect the WHERE
clause. When you use a criteria like A Or B, the query
designer constructs the WHERE clause to"
WHERE field = A OR field = B

You want the WHERE clause to be:
WHERE field = A OR B

If you edit the SQL view WHERE clause and then switch back
to the designer, Access will put the B in a calculated field
with the criteria set to True. All very confusing, but it
will work,

OK, figured it out. The criteria ( OR <>False) causes the query to
return
ALL records when B = True, essentially overriding the first parameter.
That's fine in this case, where it's a "some or all" type thing; but in a
case where it's "some or some other" that wouldn't work.

I tried a different approach, going back to my original mechanism (Field =
A
or , where is the parameter). I simply wrapped in an Eval()
function, and it worked! Apparently, even though you declare a parameter
and
declare it as a Yes/No type field, Access is too dumb to actually know
that
it's a Yes/No field, but, presumably, treats it as a string field. So by
doing the Eval() it converted it to a true true/false value, and caused
the
query to work. And, in this way, it would work even if one didn't want all
records as the other option.

Using Eval(B) makes it complex enough to get the query
designer to construct a WHERE clause like:

WHERE field = A OR Eval(B)

which is just a slow, roundabout way of doing what I
suggested.

I disagree. The field in question is a SQL Server smallint field (used
instead of bit), which is Integer in Access. All values are 0 or -1. With my
test record, there are two items for which Inactive = 0, and 4 for which
Inactive = -1. Using your solution, the query uses all six records, as one
would expect. Same with my solution (using Eval()).

However, to test whether my solution was forcing Access to correctly
evaluation the value, or was just returning all records, as yours does, I
changed one of the -1 values to -2, and passed a value of -1 for the "B"
parameter.

With your solution, all six records were used, as one would expect. However,
with my Eval() solution, only FIVE records were used. The record with a
value of -2 was skipped.

Thus, my solution is not forcing Access to ignore the first parameter and
just return all records; it's actually causing Access to evaluate the
criteria correctly (field = A or field = B, where A=0 and B=-1 should not
return the -2 value, which it didn't).

Thus, I disagree that mine is doing the same as yours.

The key point is that the second criteria is supposed to
override the first criteria. It has nothing to do with the
field's value, which it seems like you have already figured
that out.

Right, I figured out that that was how your solution worked.

Remember that my initial reply tried to clarify that all
your confusion is caused by using the query designer. If
you skipped that crutch, the SQL statement's WHERE clause
would be simple and clear.

The query designer *should* work. If one puts "0 or -1" in the criteria row,
that works. So one *should* be able to put "0 or " in the criteria row
and be able to pass it a value of -1 for and get the same results. And,
indeed, with Eval(), you can. But, as noted, the key difference here is that
one is not forced to simply return all records. If there are some records
which are not 0 or -1 (or which are Null)they would not be returned with the
Eval() solution.

The query designer does what it has always done, which I
think is by far the most common situation. You have to bend
over backwards to get it to do something else and I thought
the Eval might one way, but your test verfies that the
designer continued to do its thing. Regardless of what we
think the designer should do, it's so much easier and
unambiguous in SQL view.

 

[Neil]

The query designer does what it has always done, which I

think is by far the most common situation. You have to bend
over backwards to get it to do something else and I thought
the Eval might one way, but your test verfies that the
designer continued to do its thing. Regardless of what we
think the designer should do, it's so much easier and
unambiguous in SQL view.

I agree that when dealing with multiple parameters with AND or OR it's much
simpler to just go into SQL view. I usually just do that.

But, in this case, I wasn't concerned with which was the simplest approach,
but, rather, how to get the thing to work. I think, even if I had used SQL
view, and tried it this way or that, it wouldn't have worked without Eval().
Sad, really. I mean, you'd think that it would be able to work with a
parameter value directly -- especially when you explicitly declare it as
type Yes/No -- without having to wrap it in Eval(). Weird.

Neil

 

[Marshall Barton]

I agree that when dealing with multiple parameters with AND or OR it's much
simpler to just go into SQL view. I usually just do that.

But, in this case, I wasn't concerned with which was the simplest approach,
but, rather, how to get the thing to work. I think, even if I had used SQL
view, and tried it this way or that, it wouldn't have worked without Eval().
Sad, really. I mean, you'd think that it would be able to work with a
parameter value directly -- especially when you explicitly declare it as
type Yes/No -- without having to wrap it in Eval(). Weird.

I guess I don't fully understand what you tried because
there is no way that using Eval can be required for the
simple situation I thought you were trying to deal with. As
far as I can see from here, your problem was specifying the
Where clause correctly.

If all that was done as I thought it was, then the only
reason that I can think of for Eval to have a useful effect
is if the variable blnWantInactives is a string instead of a
boolean.

 

[Neil]

I guess I don't fully understand what you tried because

there is no way that using Eval can be required for the
simple situation I thought you were trying to deal with. As
far as I can see from here, your problem was specifying the
Where clause correctly.

If all that was done as I thought it was, then the only
reason that I can think of for Eval to have a useful effect
is if the variable blnWantInactives is a string instead of a
boolean.

In the query, in the Criteria row under the Inactive field (Inactive is an
Integer in Access, based on a smallint in SQL Server, and contains values of
0 and -1), I originally had:

"0 or [blnIncludeInactives]" (without the quotes)

(blnIncludeInactives was defined in the query parameters as type Yes/No.)

The idea is that if I wanted only actives, I would pass it a 0, so it would
be "0 or 0"; and if I wanted both actives and inactives, I would pass it -1,
so it would be "0 or -1".

However, whether I passed it 0 or -1, the query still returned only actives,
as though the parameter after the OR didn't exist. (The value passed was
stored in code in the variable blnWantInactives, which I just double-checked
is defined as type Boolean.)

When I changed the text in the Criteria row to instead be:

"0 or Eval([blnIncludeInactives])" (without the quotes)

it worked. It would return all records if I passed it -1, and only actives
if I passed it 0, just as I had planned.

You originally said that the reason it was working with Eval() was because
Eval() was causing the criteria to be so complex that it was putting it into
the format that you had recommended (which results in all records being
returned).

To test this theory, I changed one of the records' Inactive value to -2,
instead of -1. Since my criteria (when I passed it a value of -1) would be
"0 or -1", it should not return the record with the -2 value, if it were
actually evaluating the criteria. If, instead, it was placing it in your
format, which basically just overrides the first criteria and returns all
records, it WOULD return the record with a value of -2.

When I ran the query with the Eval() in place, the record with a value of -2
was not returned, indicating that the query was NOT placing it in the format
you had recommended (which would return all records), but was, instead,
correctly evaluating the criteria.

So, in summary (with 2 records having a value of 0; 3 records having a value
of -1, and 1 record having a value of -2; and passing it a value of -1 for
blnIncludeInactives):

- Original query returned 2 records
- Query modified using your method returned 6 records
- Query modified by wrapping Eval() around [blnIncludeInactives] returned 5
records (omitting the one with a value of -2).

================================

OK, just figured out what was going on (after writing all this!). Even
though I was passing it a value from a boolean variable; and even though the
values passed are only 0 or -1; the table field itself is not boolean, but
is Integer. And I defined the query parameter as type Yes/No. So,
apparently, it couldn't use a -1 sent to a Yes/No parameter against an table
field which is an Integer. You'd think it would be able to do that.

I just changed the query parameter from Yes/No to Integer, and it works
fine, without Eval(). So, even though I'm passing it a value stored in a
boolean variable, what's really key here is what the query parameter is
defined as. Apparently there's no automatic type conversion from Yes/No to
Integer!

So, in summary, the string:

"0 or [blnIncludeInactives]" (without the quotes)

works just fine when passed a value of 0 or -1, to return either items with
a value of 0 only, or items with a value of 0 or -1.

Thanks!

Neil

 

[Marshall Barton]

I guess I don't fully understand what you tried because
there is no way that using Eval can be required for the
simple situation I thought you were trying to deal with. As
far as I can see from here, your problem was specifying the
Where clause correctly.

If all that was done as I thought it was, then the only
reason that I can think of for Eval to have a useful effect
is if the variable blnWantInactives is a string instead of a
boolean.

[snip]
OK, just figured out what was going on (after writing all this!). Even
though I was passing it a value from a boolean variable; and even though the
values passed are only 0 or -1; the table field itself is not boolean, but
is Integer. And I defined the query parameter as type Yes/No. So,
apparently, it couldn't use a -1 sent to a Yes/No parameter against an table
field which is an Integer. You'd think it would be able to do that.

I just changed the query parameter from Yes/No to Integer, and it works
fine, without Eval(). So, even though I'm passing it a value stored in a
boolean variable, what's really key here is what the query parameter is
defined as. Apparently there's no automatic type conversion from Yes/No to
Integer!

So, in summary, the string:

"0 or [blnIncludeInactives]" (without the quotes)

works just fine when passed a value of 0 or -1, to return either items with
a value of 0 only, or items with a value of 0 or -1.

Bleeping computers, they only do what you say, not what you
meant ;-)

As has been said so often, the devil is in the details.

 

[Neil]

Bleeping computers, they only do what you say, not what you

meant ;-)

As has been said so often, the devil is in the details.

Yeah, well Access does implicit type conversions all over the place. You'd
*think* a simple conversion from Yes/No to Integer would be simple for it.
But noooooooooooooo. The one time I *need* it to implicit type conversion,
it lets me down. Oh Access; why do I continue to love thee? :-|

 

[lyle fairfield]

Yeah, well Access does implicit type conversions all over the place.
You'd *think* a simple conversion from Yes/No to Integer would be
simple for it. But noooooooooooooo. The one time I *need* it to
implicit type conversion, it lets me down. Oh Access; why do I
continue to love thee? :-|

Perhaps it's operating as advertised?

“Or Operator
Used to perform a logical disjunction on two expressions.
Syntax

result = expression1 Or expression2

The Or operator syntax has these parts:

Part Description
result Required; any numeric variable.
expression1 Required; any expression.
expression2 Required; any expression.

Remarks

If either or both expressions evaluate to True, result is True. The
following table illustrates how result is determined:

If expression1 is And expression2 is Then result is
True True True
True False True
True Null True
False True True
False False False
False Null Null
Null True True
Null False Null
Null Null Null

The Or operator also performs a bitwise comparison of identically
positioned bits in two numeric expressions and sets the corresponding bit
in result according to the following table:

If bit in expression1 is And bit in expression2 is Then result is
0 0 0
0 1 1
1 0 1
1 1 1

Example
This example uses the Or operator to perform logical disjunction on two
expressions.

Dim A, B, C, D, MyCheck
A = 10: B = 8: C = 6: D = Null ' Initialize variables.
MyCheck = A > B Or B > C ' Returns True.
MyCheck = B > A Or B > C ' Returns True.
MyCheck = A > B Or B > D ' Returns True.
MyCheck = B > D Or B > A ' Returns Null.
MyCheck = A Or B ' Returns 10 (bitwise comparison).”

---------------
---------------

Also I think that operator precedence is very important when using logical
operators. Rather than try to memorize and rely on this I tend to
parenthesize (is that a word?) expressions containing logical operators
heavily.

 

[Neil]

Perhaps it's operating as advertised?

<snip>

Not sure what your point is; but here's mine in a nutshell:

Query parameter for field MyField set to "0 or [param]" where [param] is
defined as Yes/No. But MyField is Integer. Passing a value of -1 to [param]
does not return record having -1 in MyField. Changing the parameter type to
Integer resolves the problem.

Thus, Access does not perform implicit type conversion from defined type
(Yes/No) to table field type (Integer), even though Yes/No values can only
be integers, and even though Access performs implicit type conversions all
over the place -- most of the time when they're not desired. But here, where
an implicit type conversion would actually be logical, it fails to do so.
*shrug*

 

[huangda ½ú½­ÐË´ï»úе]

Yeah, well Access does implicit type conversions all over the place. You'd
*think* a simple conversion from Yes/No to Integer would be simple for it.
But noooooooooooooo. The one time I *need* it to implicit type conversion,
it lets me down. Oh Access; why do I continue to love thee? :-|