Solved

Access Query (QBE) Using VBA Function for Criteria

Posted on 2015-01-14
13
57 Views
Last Modified: 2016-05-22
Tying to feed a query criteria via VBA Function.  The function works and I get the results I need in the query criteria, such as
NOT IN (38024,91464,126691,129623,164585)

I need to exclude these numbers from the query's output.

Here is the function:

Public Function NOT_ExecCriteria() As String
    Dim rs1 As DAO.Recordset
    Dim db As DAO.Database
    Dim strInstCode As String
    Dim SQL1 As String
    Dim strCrit As String
   
    Set db = CurrentDb
   
    SQL1 = "SELECT DISTINCT Enumber FROM tblExecCrosswalk " _
            & "WHERE [Company] = " & lngCoCode()
   
    Set rs1 = db.OpenRecordset(SQL1, dbOpenDynaset)
    With rs1
        strCrit = "NOT IN ("
        Do Until rs1.EOF
            strCrit = strCrit & .Fields("Enumber") & ","
           
        .MoveNext
        Loop
        'Trim closing comma and close with a paren
        NOT_ExecCriteria = Left(strCrit, Len(strCrit) - 1) & ")"
    End With
rs1.Close
End Function


I can directly past the NOT IN (xxx,xxxxx,xxx) criteria into the criteria line and it works, but when I put the function NOT_ExecCriteria()  it returns no records.
0
Comment
Question by:jrogersok
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 125 total points
ID: 40549458
You can't use: NOT IN (SomeFunctionReturningList())
It must be: NOT IN (x,xx,xxx, etc)

So you will have to change your method to build the full SQL string dynamically including the finished list of IDs in the NOT IN (...) expression.

/gustav
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 125 total points
ID: 40549491
You are attempting to pass the whole string in?
"NOT IN (38024,91464,126691,129623,164585)"
and then I think what you get in practice in the criteria line is
="NOT IN (38024,91464,126691,129623,164585)"
and that isn't what you want.

I don't know if you can do it in the Query Editor the way you want.
You certainly can create the entire SQL string in code and make the query's SQL the same

Dim qdf as QueryDef
set qdf = CurrentDb.QueryDefs("TheNameOfYourSavedQuery")
qdf.SQL = "APerfectlyFormattedSQLStringThatHasTheNotInClauseIWant"
set qdf = Nothing

The problem is that I don't think there's any nice way to keep the editor from turning any function's input into badness like
="Not in (38024,91464,126691,129623,164585)"
Not In ("38024,91464,126691,129623,164585")
or something else inappropriate.

Maybe other folks have other suggestions.
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 125 total points
ID: 40549503
In addition to what Gustav mentioned, you have other options.
1. in your query, use the DLookup() function to match on the company code and the field you are trying to exclude via a Not In () clause.  When the DLookup() result is Not Null, or IsNull()=False, your query will operate as expected.

2. Include your tblExecCrosswalk in your query, doing a left join.  When the joined field from the tblExecCrosswalk is null, your query will operate as expected.

3. Change your NOT_ExecCriteria function to accept both a company code and an Enumber-matching value.  Your function would return true or false, depending on whether the value was found.
Read Harfang's article on fast table look-ups on this subject:
http:A_1921-Access-Techniques-Fast-Table-Lookup-Functions.html
0
 
LVL 2

Author Comment

by:jrogersok
ID: 40549549
Nick76 -- I tried it as both a Variant and String and returned no records.  I see where a type of String won't work.

Gustov/aikimark -- I can build the SQL but was hoping not to have to go that route in this case.  

aikimark -- re your option 1 -- sounds intriguing -- can you explain a bit more what you are thinking here?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40549644
Something like this
Select *
From mytable
Where DLookup("Enumber", "tblExecCrosswalk", "Company = " & mytable.companycode & " and Enumber = " & mytable.EmployeeNumber) Is Null

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
ID: 40549648
What @aikimark is getting at is that you are not going to get a function to play nicely as a criteria.
Functions work very nicely as fields, however.
So if you create a function

Function ExcludeIt(PossibleBadValue as string) as Boolean
select case true
    Case PossibleBadValue = "38024"
    Case PossibleBadValue ="91464"
    Case PossibleBadValue ="126691"
    Case PossibleBadValue ="129623"
    Case PossibleBadValue ="164585"
    Case Else
          'all other values are good
          ExcludeIt = False
          exit function
end select
'we only get here on a baddie
ExcludeIt = true
end function

And in your query you add a field
NotBad:ExcludeIt([WhateverTheFieldToBeTestedIs)
and it has a criteria of False

Now, I coded those values, and you may want to do that dynamically.
That'll be less efficient, as every row in the query will call the code to dynamically create the comparison.
@harfangs article may have more efficient ways to do the task.
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)

 
LVL 45

Expert Comment

by:aikimark
ID: 40549654
Note: Indexes are important for performance.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40549665
you are not going to get a function to play nicely as a criteria.
That isn't to say that functions never work as criteria.
For >= <= = <> some function they can work quite well.
To pass in a Global variable as a criteria about the only way to do so is to create a wrapper function

But as a more complex criteria (multiple criteria, In, Not In, Exists, subqueries) it's quite doubtful.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40549708
You can also use the Exists() function/clause in your query.
0
 
LVL 18

Assisted Solution

by:bonjour-aut
bonjour-aut earned 125 total points
ID: 40549837
If you would put your values (38024,91464,126691,129623,164585 in a table (e.g. tbl2 - fieldname 'exclude')
you could easily SQL them away from  e.g  tbl1 - fieldname 'field2':

SELECT tbl1.* FROM tbl1 LEFT JOIN tbl2 ON tbl1.[field2] = tbl2.[exclude] WHERE (((tbl2.exclude) Is Null));
0
 
LVL 2

Author Comment

by:jrogersok
ID: 40578893
I know it seems as if I've abandoned the question, just have had other pressing matters that I've needed to deal with over the last few weeks (and it's going to continue into the next two.)

Lots of great ideas that I'm going to have to look at mid-February unfortunately.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 40905048
Was that mid-February 2016, or a later year?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

863 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

25 Experts available now in Live!

Get 1:1 Help Now