Solved

Access Query (QBE) Using VBA Function for Criteria

Posted on 2015-01-14
13
54 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 45

Expert Comment

by:aikimark
Comment Utility
Note: Indexes are important for performance.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 ]
Comment Utility
Was that mid-February 2016, or a later year?
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

743 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

16 Experts available now in Live!

Get 1:1 Help Now