Query Vs Recordset Discrepancy

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”));
clchapmanAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
John_VidmarConnect With a Mentor Commented:
SQL uses percent (%) as a wild-card in a like-clause, does ADO use asterisk (*) as a wild-card?
0
 
Rey Obrero (Capricorn1)Commented:
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
 
mbizupCommented:
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
clchapmanAuthor Commented:
Rey -
No, it is not a typo, I enter "Like" and it changes to "Alike".
Thanks
0
 
clchapmanAuthor Commented:
Thank you!
0
 
mbizupCommented:
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
All Courses

From novice to tech pro — start learning today.