Solved

Query Vs Recordset Discrepancy

Posted on 2014-02-12
6
415 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 119

Expert Comment

by:Rey Obrero
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

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 …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

867 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