Solved

Query Vs Recordset Discrepancy

Posted on 2014-02-12
6
417 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
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.

 

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

840 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