Can anyone explain this

[Ben]

I have a report to filter records

Here is what I entered into the Criteria field to create a parameter
query for the filter

Like "*" & [Enter Sub Category or leave blank for all] & "*" Or
[Enter Sub Category or leave blank for all] Is Null

and it works!


Somehow, after I save the query, and reopen it, the Criteria field gets
truncated to

Like "*" & [Enter Sub Category or leave blank for all] & "*"

However, the query still seems to work.

Is Access truncating the Criteria field in our view of it but actually
storing the entire string in the background? Is there something we can
do so the whole string appears?

Any help mucho appreciated

 

[Guest]

Likely the IsNull section has been automatically moved to a separate column
at the end.
--
***************************
If the message was helpful to you, click Yes next to Was this post helpful
to you?
If the post answers your question, click Yes next to Did this post answer
the question?

 

[Rick Brandt]

I have a report to filter records

Here is what I entered into the Criteria field to create a parameter
query for the filter

Like "*" & [Enter Sub Category or leave blank for all] & "*" Or
[Enter Sub Category or leave blank for all] Is Null

and it works!


Somehow, after I save the query, and reopen it, the Criteria field
gets truncated to

Like "*" & [Enter Sub Category or leave blank for all] & "*"

However, the query still seems to work.

Is Access truncating the Criteria field in our view of it but actually
storing the entire string in the background? Is there something we
can do so the whole string appears?

Any help mucho appreciated

Anytime you have "*" & null & "*" you are automatically going to return all
records. Your OR is unnecessary so Access is tossing it.

 

[David F Cox]

It looks like Access's query optimiser it doing its job. If null your query
resolves to:-
Like "**" or Like "*"

 

[Ben]

Yes, this is what happened, but I don't know why Access does this.
Thanks

Likely the IsNull section has been automatically moved to a separate column
at the end.
--
***************************
If the message was helpful to you, click Yes next to Was this post helpful
to you?
If the post answers your question, click Yes next to Did this post answer
the question?

I have a report to filter records

Here is what I entered into the Criteria field to create a parameter
query for the filter

Like "*" & [Enter Sub Category or leave blank for all] & "*" Or
[Enter Sub Category or leave blank for all] Is Null

and it works!


Somehow, after I save the query, and reopen it, the Criteria field gets
truncated to

Like "*" & [Enter Sub Category or leave blank for all] & "*"

However, the query still seems to work.

Is Access truncating the Criteria field in our view of it but actually
storing the entire string in the background? Is there something we can
do so the whole string appears?

Any help mucho appreciated

 

[Ben]

Not sure I understand your reply, but

Like "*" & [Enter Sub Category or leave blank for all] & "*"

does not return records with Null subcategory when left blank

Like "*" & [Enter Sub Category or leave blank for all] & "*" Or [Enter
Sub Category or leave blank for all] Is Null

does return all records when left blank

Is there a better way to write this?

I have a report to filter records

Here is what I entered into the Criteria field to create a parameter
query for the filter

Like "*" & [Enter Sub Category or leave blank for all] & "*" Or
[Enter Sub Category or leave blank for all] Is Null

and it works!


Somehow, after I save the query, and reopen it, the Criteria field
gets truncated to

Like "*" & [Enter Sub Category or leave blank for all] & "*"

However, the query still seems to work.

Is Access truncating the Criteria field in our view of it but actually
storing the entire string in the background? Is there something we
can do so the whole string appears?

Any help mucho appreciated

Anytime you have "*" & null & "*" you are automatically going to return all
records. Your OR is unnecessary so Access is tossing it.

 

[Dirk Goldgar]

Yes, this is what happened, but I don't know why Access does this.

As far as I can tell, Access doesn't save the design view of the query
as such; it just saves the SQL and some miscellaneous properties. So
when you open the query in design view, Access parses it and constructs
the design view. That doesn't always conform to the logically
equivalent view you used to construct it.

 

[Rick Brandt]

Not sure I understand your reply, but

Like "*" & [Enter Sub Category or leave blank for all] & "*"

does not return records with Null subcategory when left blank

Like "*" & [Enter Sub Category or leave blank for all] & "*" Or [Enter
Sub Category or leave blank for all] Is Null

does return all records when left blank

Is there a better way to write this?

Unless you want the user to be able to find partial matches then you don't
need the "*". Just use...

[Enter Sub Category or leave blank for all] OR [Enter Sub Category or leave
blank for all] Is Null

 

[Pieter Wijnen]

it even tends to f***'s up good query design to the point that it can't
parse the SQL anymore (and/or precedence)
--
Pieter Wijnen

My feeble Access pages (good links though)
http://www.thuleeng.com/access
When all else fail try:
http://www.mvps.org/access
http://www.granite.ab.ca/
http://allenbrowne.com/
http://www.lebans.com/

 

[Dirk Goldgar]

"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.rep
lace.with.norway>
wrote in message news:%[email protected]

it even tends to f***'s up good query design to the point that it
can't parse the SQL anymore (and/or precedence)

And in certain circumstances -- derived tables, for example -- can take
valid SQL and rewrite it so that it is no longer valid. That may be
what you're referring to. It's a major annoyance sometimes.

 

[Pieter Wijnen]

Also constructs like

WHERE (A=1 OR A =0)
AND (B=1 OR B=0)
.....

Will be changed to
WHERE (((A)=0) AND ((B)=1))
OR (((A)=0) AND ((B)=0)) etc
givven enough conditions the SQL just gets to long to handle - let alone
decipherable

Pieter

 

[Dirk Goldgar]

"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.rep
lace.with.norway>
wrote in message news:[email protected]

Also constructs like

WHERE (A=1 OR A =0)
AND (B=1 OR B=0)
....

Will be changed to
WHERE (((A)=0) AND ((B)=1))
OR (((A)=0) AND ((B)=0)) etc
givven enough conditions the SQL just gets to long to handle - let
alone decipherable

I hadn't run into that problem, but I can certainly believe it. When I
run into problems, it's usually with the derived-table reformatting,
where

FROM (SELECT [FOO] FROM BAR) AS T

becomes

FROM [SELECT [FOO] FROM BAR]. AS T

which the query engine can't parse. It's not often I have to work
around the query designer, but I do build a certain number of complex
queries where this becomes a pain.

 

[Pieter Wijnen]

I understood that.
Still doing quite a lot in '97 where you can't have subqueries in the FROM
clause anyway

Pieter

"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.rep
lace.with.norway>
wrote in message news:[email protected]

Also constructs like

WHERE (A=1 OR A =0)
AND (B=1 OR B=0)
....

Will be changed to
WHERE (((A)=0) AND ((B)=1))
OR (((A)=0) AND ((B)=0)) etc
givven enough conditions the SQL just gets to long to handle - let
alone decipherable

I hadn't run into that problem, but I can certainly believe it. When I
run into problems, it's usually with the derived-table reformatting,
where

FROM (SELECT [FOO] FROM BAR) AS T

becomes

FROM [SELECT [FOO] FROM BAR]. AS T

which the query engine can't parse. It's not often I have to work
around the query designer, but I do build a certain number of complex
queries where this becomes a pain.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)