Solved

Query Vs Recordset Discrepancy

Posted on 2014-02-12
6
416 Views
Last Modified: 2014-02-13
I am encountering a problem trying to apply criteria to a query. I have a table of 150945 records. I want to filter those to show only “Active” profiles (“ProfileStatus” field) and exclude those with a Profile (“SecProfile” field) name that begins with “999”.

Filtering only the active profiles nets 113319 records. In this specific set of data there are 9 records with a Profile name that begins with “999”. Thus, my end result should be 113310.

VBA SQL
"SELECT DISTINCT "[T_Facets Data].DomainDesc, [T_Facets Data].DomainID, " & _
   "[T_Facets Data].DomainAction, [T_Facets Data].EntityID, " & _
   "[T_Facets Data].EntityDesc, [T_Facets Data].SecProfile, " & _
   "[T_Facets Data].SecProDesc, [T_Facets Data].SecLevel, " & _
   "[T_Facets Data].ProfileStatus " & _
   "FROM [T_Facets Data] WHERE ((([T_Facets Data].ProfileStatus) = 'X') AND " & _
   "([T_Facets Data].SecProfile) not like '999*')"

If I run the SQL as a recordset, the RecordCount is 113310; however, if I run the SQL as a Query (via Design View or QueryDef) the records count is 113319 and the “999” profiles are not excluded from the query datasheet view. What am I doing wrong?

This is the criteria if viewed from the query object:
WHERE ((([T_Facets Data].SecProfile) Not ALike “999*”) AND (([T_Facets Data].ProfileStatus)=”X”));
0
Comment
Question by:clchapman
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39855016
you have " Not ALike “999*”   in the where clause of the query, is this  typo "ALike" ?

if yes, change it to Not Like "999*"
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 39856116
SQL uses percent (%) as a wild-card in a like-clause, does ADO use asterisk (*) as a wild-card?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39856663
John_Vidmar is correct.  IF this is an ADO recordset, you need to use the % wildcard rather than *.

It's not clear from the limited code you posted whether you are using DAO or ADO, but they use different wildcards.

Your Asterisk is correct for DAO, but  % would be needed however for ADO.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:clchapman
ID: 39856667
Rey -
No, it is not a typo, I enter "Like" and it changes to "Alike".
Thanks
0
 

Author Closing Comment

by:clchapman
ID: 39856686
Thank you!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39856690
There is an Access Option which is likely responsible for that (see the image).

This option makes your queries, etc adhere to the same standards/syntax as SQL ServerAccess option
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now