Query retains parameters

[Steve H]

I have a query that reads parameters from a form. The form contains several
unbound controls and two buttons: one to run the query, and the other to set
the value of all of the controls to Null. I've debugged the "Clear values"
button, and all of the controls (text and combo boxes) have a value of null
once it has run.

The query parameters include this code:
Like "*" & [forms]![frmSelector]![sc1] & "*"

If I run the query with no parameters entered in the form (there are others
that read dates, for example), the query returns 350 records - as it should.
If I enter a letter in the sc1 text box, the query returns 54 records - as
it should.

Here's my problem: if I then clear the form so that sc1 is Null - I've added
a control that says that IsNull([Sc1]) is True - and, therefore, the query
runs without any parameters, I only retrieve 54 records. Indeed, if I rename
- or delete - the field from the form, I still only get 54 records... the
query seems to retain its earlier results, and only clears them if I delete
the parameter from the query and run it again.

I'd be interested to know why it happens! A cure would be good.

 

[Allen Browne]

I'm not sure if this is an issue with Nulls, or with the value of the
control.

When you enter or clear the value in the text box, Access updates the Value
of the control when you tab out of it. If the cursor is still in the
control, and you run the query again, the query may not reflect what you see
in the control, because its Value has not been updated.

The other possibility is an issue with nulls. The criterion:
Like "*" & [forms]![frmSelector]![sc1] & "*"
does not return all records when sc1 is null. If your table has some records
where this field is null, those records will not be returned. That's because
Null doesn't match anything, so a Null in the field does not even match the
wildcard "*".

A third possibility is a bug in Access. If you explicitly declare your
parameter of type Text, JET (the data engine in Access) does not process is
correctly when it contains null. Details in:
Parameter of type Text is evaluated wrongly
at:
http://allenbrowne.com/bug-13.html

 

[Steve H]

Huh. Yes, it sounds like a "nulls" issue, as the value in Sc1 is selected (on
the original form) if a Sc is used, but is left null if a Sc is not used.
Interesting! I'd thought of a null value in a field as a waste of space in a
database, but not as a problem in a query.

I'll have to totally re-design my (very clever!) query form - or find a way
to convert all of the null values in the database to something else - perhaps
a zero-length string, or a space.

I'm not sure if this is an issue with Nulls, or with the value of the
control.

When you enter or clear the value in the text box, Access updates the Value
of the control when you tab out of it. If the cursor is still in the
control, and you run the query again, the query may not reflect what you see
in the control, because its Value has not been updated.

The other possibility is an issue with nulls. The criterion:
Like "*" & [forms]![frmSelector]![sc1] & "*"
does not return all records when sc1 is null. If your table has some records
where this field is null, those records will not be returned. That's because
Null doesn't match anything, so a Null in the field does not even match the
wildcard "*".

A third possibility is a bug in Access. If you explicitly declare your
parameter of type Text, JET (the data engine in Access) does not process is
correctly when it contains null. Details in:
Parameter of type Text is evaluated wrongly
at:
http://allenbrowne.com/bug-13.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a query that reads parameters from a form. The form contains
several
unbound controls and two buttons: one to run the query, and the other to
set
the value of all of the controls to Null. I've debugged the "Clear
values"
button, and all of the controls (text and combo boxes) have a value of
null
once it has run.

The query parameters include this code:
Like "*" & [forms]![frmSelector]![sc1] & "*"

If I run the query with no parameters entered in the form (there are
others
that read dates, for example), the query returns 350 records - as it
should.
If I enter a letter in the sc1 text box, the query returns 54 records - as
it should.

Here's my problem: if I then clear the form so that sc1 is Null - I've
added
a control that says that IsNull([Sc1]) is True - and, therefore, the query
runs without any parameters, I only retrieve 54 records. Indeed, if I
rename
- or delete - the field from the form, I still only get 54 records... the
query seems to retain its earlier results, and only clears them if I
delete
the parameter from the query and run it again.

I'd be interested to know why it happens! A cure would be good.

 

[Allen Browne]

An all-too-familiar story, Steve.

Can I encourage you not to give up on Nulls, but to learn about how truly
useful they really are - when understood correctly.

Here's a quick summary of the most common problems people hit with nulls:
http://allenbrowne.com/casu-12.html

And here's a downloadable example of how to create that (clever) query form
you're aiming for:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Huh. Yes, it sounds like a "nulls" issue, as the value in Sc1 is selected
(on
the original form) if a Sc is used, but is left null if a Sc is not used.
Interesting! I'd thought of a null value in a field as a waste of space
in a
database, but not as a problem in a query.

I'll have to totally re-design my (very clever!) query form - or find a
way
to convert all of the null values in the database to something else -
perhaps
a zero-length string, or a space.

I'm not sure if this is an issue with Nulls, or with the value of the
control.

When you enter or clear the value in the text box, Access updates the
Value
of the control when you tab out of it. If the cursor is still in the
control, and you run the query again, the query may not reflect what you
see
in the control, because its Value has not been updated.

The other possibility is an issue with nulls. The criterion:
Like "*" & [forms]![frmSelector]![sc1] & "*"
does not return all records when sc1 is null. If your table has some
records
where this field is null, those records will not be returned. That's
because
Null doesn't match anything, so a Null in the field does not even match
the
wildcard "*".

A third possibility is a bug in Access. If you explicitly declare your
parameter of type Text, JET (the data engine in Access) does not process
is
correctly when it contains null. Details in:
Parameter of type Text is evaluated wrongly
at:
http://allenbrowne.com/bug-13.html

I have a query that reads parameters from a form. The form contains
several
unbound controls and two buttons: one to run the query, and the other
to
set
the value of all of the controls to Null. I've debugged the "Clear
values"
button, and all of the controls (text and combo boxes) have a value of
null
once it has run.

The query parameters include this code:
Like "*" & [forms]![frmSelector]![sc1] & "*"

If I run the query with no parameters entered in the form (there are
others
that read dates, for example), the query returns 350 records - as it
should.
If I enter a letter in the sc1 text box, the query returns 54 records -
as
it should.

Here's my problem: if I then clear the form so that sc1 is Null - I've
added
a control that says that IsNull([Sc1]) is True - and, therefore, the
query
runs without any parameters, I only retrieve 54 records. Indeed, if I
rename
- or delete - the field from the form, I still only get 54 records...
the
query seems to retain its earlier results, and only clears them if I
delete
the parameter from the query and run it again.

I'd be interested to know why it happens! A cure would be good.

 

[Steve H]

Thanks, Allen. (You must enjoy your working day!)

Once you pointed me to the Nulls issue, I cheated with my form. I used a
query to provide the basic data from my table, and used an iif statement to
replace Null dates with the value 2 (1/1/1900). It's a weak (er, poor,
actually) workaround, but using iif statements at least lets me retain my
data. Now I'll have a look at your query form, and find out how I should
have done it!

Cheers,

Steve

An all-too-familiar story, Steve.

Can I encourage you not to give up on Nulls, but to learn about how truly
useful they really are - when understood correctly.

Here's a quick summary of the most common problems people hit with nulls:
http://allenbrowne.com/casu-12.html

And here's a downloadable example of how to create that (clever) query form
you're aiming for:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Huh. Yes, it sounds like a "nulls" issue, as the value in Sc1 is selected
(on
the original form) if a Sc is used, but is left null if a Sc is not used.
Interesting! I'd thought of a null value in a field as a waste of space
in a
database, but not as a problem in a query.

I'll have to totally re-design my (very clever!) query form - or find a
way
to convert all of the null values in the database to something else -
perhaps
a zero-length string, or a space.

I'm not sure if this is an issue with Nulls, or with the value of the
control.

When you enter or clear the value in the text box, Access updates the
Value
of the control when you tab out of it. If the cursor is still in the
control, and you run the query again, the query may not reflect what you
see
in the control, because its Value has not been updated.

The other possibility is an issue with nulls. The criterion:
Like "*" & [forms]![frmSelector]![sc1] & "*"
does not return all records when sc1 is null. If your table has some
records
where this field is null, those records will not be returned. That's
because
Null doesn't match anything, so a Null in the field does not even match
the
wildcard "*".

A third possibility is a bug in Access. If you explicitly declare your
parameter of type Text, JET (the data engine in Access) does not process
is
correctly when it contains null. Details in:
Parameter of type Text is evaluated wrongly
at:
http://allenbrowne.com/bug-13.html

I have a query that reads parameters from a form. The form contains
several
unbound controls and two buttons: one to run the query, and the other
to
set
the value of all of the controls to Null. I've debugged the "Clear
values"
button, and all of the controls (text and combo boxes) have a value of
null
once it has run.

The query parameters include this code:
Like "*" & [forms]![frmSelector]![sc1] & "*"

If I run the query with no parameters entered in the form (there are
others
that read dates, for example), the query returns 350 records - as it
should.
If I enter a letter in the sc1 text box, the query returns 54 records -
as
it should.

Here's my problem: if I then clear the form so that sc1 is Null - I've
added
a control that says that IsNull([Sc1]) is True - and, therefore, the
query
runs without any parameters, I only retrieve 54 records. Indeed, if I
rename
- or delete - the field from the form, I still only get 54 records...
the
query seems to retain its earlier results, and only clears them if I
delete
the parameter from the query and run it again.

I'd be interested to know why it happens! A cure would be good.