Solved

Query Vs Recordset Discrepancy

Posted on 2014-02-12
6
414 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

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

Author Closing Comment

by:clchapman
Comment Utility
Thank you!
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

771 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

10 Experts available now in Live!

Get 1:1 Help Now