[AccessD] Union Query WHERE Bug?

Solution:
 
After I changed the query from 'UNION' to 'UNION ALL' the filter
works!!!
 
I don't know if this is a quirk in my application or Access -- or maybe
the UNION ALL ensures a unique record. :-)
 
Thanks for inspiring me to continue to hunt for the answer. Sometimes I
have to sleep on a problem to get the solution.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of StaRKeY
Sent: Saturday, April 19, 2003 3:07 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Union Query WHERE Bug?


If it contains chars and you are sure the value is all you need then you
could also try the following:
....WHERE Val(RepNum) = 8222 
 
If that also doesn't work I'd like to see your data and query(s)
myself:-)
 
Regards,
Eric S.
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Myke Myers
Sent: zaterdag 19 april 2003 06:22 
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Union Query WHERE Bug?


I trimmed the RepNum field in both the Select queries used in the Union
query.
 
I can 'Filter By Selection' in either of the Select queries. But when I
try the same 'Filter By Selection' in the Union query, it returns no
records.
 
I found others with the same trouble in Google Groups. (Seached on
'Filter By Selection'  and 'union query'.) Very strange.
 
Susan and Jim: Thanks for your suggestions.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
(AccessD)
Sent: Friday, April 18, 2003 10:05 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Union Query WHERE Bug?


Hi Myke:
 
The only reason is because the field with the number string has a space
or another invisible character in the field.
the 'like' operand will return any string with '8222' any where in it.
If the field was five characters in size it could return three
possiblities of '8222 ' or ' 8222' or '8222'.
WHERE (((quniCEIncompleteCredits.RepNum) Like "8222")); 
 
you could try, to remove spaces:

WHERE (((trim(quniCEIncompleteCredits.RepNum))="8222")); 
 
HTH
JIm

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Myke Myers
Sent: Friday, April 18, 2003 12:53 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Union Query WHERE Bug?


Can anyone explain why this query returns one record, as it should...
 
SELECT quniCEIncompleteCredits.*
FROM quniCEIncompleteCredits
WHERE (((quniCEIncompleteCredits.RepNum) Like "8222")); 
 
And this query returns no records?. 
 
SELECT quniCEIncompleteCredits.*
FROM quniCEIncompleteCredits
WHERE (((quniCEIncompleteCredits.RepNum)="8222")); 
 
 
Using Access 2000. 'quniCEIncompleteCredits' is a union query.
 
TIA,
 
Myke
The Better Information Group




  _____  

avast!  <http://www.avast.com> Antivirus: Outbound message clean. 


Virus Database (VPS): 17-4-2003
Tested on: 19-4-2003 9:06:53
avast! is copyright (c) 2000-2003 ALWIL Software.


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030421/67dc811f/attachment-0001.html>