Slow Query Filtering on Concatenated Variable (1 Viewer)

[lemo]

Howdy, it's been a while, hope everybody's in good spirits.

Have a question. I am trying to use a criteria to filter out nulls in concatenated variable, which slows the query considerably.
Is there a quick workaround?
The part that makes it slow looks like this -
...
WHERE ... AND (([tbl1].[x] & [tbl1].[y]) <> "") AND ...

If I work them individually, it's not a problem, the query is fast, but I have to combine them first in order to produce the correct output.
Greatly appreciate your help, as always.
Thanks,
Len

 

[spikepl]

but I have to combine them first in order to produce the correct output.

why?

also, you are not filtering out nulls, you are filtering out zero-length strings - a different animal altogether.

 

[vbaInet]

WHERE ... AND (([tbl1].[x] & [tbl1].[y]) <> "") AND ...

And that's why it's slow because you've lost the power of indexing.

 

[lemo]

Because I have cases when only one of the variables is null, in which case I'd like to keep the record. But when both are null, I want to drop that record.
I did try to play with ' Is Not Null ' vs ' <>"" ', and functions like Trim() and Len(), but nothing helped.

 

[vbaInet]

Do it separately and change the Allow Zero Length String property of the fields to No.

 

[spikepl]

Then the condition is:

NOT (VarA Is Null AND VarB Is Null)

 

[lemo]

Not sure I can index, I am using Access front (Access 2013), but my tables are on SQL server.. I mean, I can ask 'them' to index variables in the tables on the server I suppose.. can you tell me more please.. thanks.

 

[spikepl]

I'm too lazy to retype stuff you can easily google yourself. So google or wait for vbaInet.

 

[vbaInet]

Concatenated fields = No Index

Do it as spikepl showed in post #6.

 

[lemo]

Yep, #6 did it! Brilliant.. and simple too, as I was hoping.
Thanks much, guys.
L

 

[lemo]

and another thanks goes to spikepl for educating me on what San Serriffe is.. fun!

 

[lemo]

An old thread, but just wanted to add something for posterity.
I realized I still had issues even with that #6 solution, NOT (VarA Is Null AND VarB Is Null), not in Access itself, but when I was trying to use the query as a data source for a pivot table in Excel.
Somebody suggested this -
[FONT=&quot]...[/FONT][FONT=&quot] ((VarA Is Not Null) OR (VarB Is Not Null)) ...[/FONT]
which seem to fix problems in both Access and Excel.
l

 

[spikepl]

The two are fully equivalent in logical algebra:

NOT (A AND B) = NOT A OR NOT B

but according to your story Excel does not follow logic

 

[lemo]

But it's true!
And the worst thing is I have no idea why. I am relatively good at Excel, less so at Access, and have really no clue what happens when the two communicate with each other.
Access rewrote NOT (VarA Is Null AND VarB Is Null) as ((VarA Is Null And VarB Is Null)=False) maybe that's the problem? When I try refreshing data in Excel, the error message is rather generic "Error Obtaining Data". Whereas before it was more interesting "... incorrect syntax near '='...".
And no errors with NOT A OR NOT B.
l