Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 118
  • Last Modified:

Access Query (QBE) Using VBA Function for Criteria

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
jrogersok
Asked:
jrogersok
  • 4
  • 3
  • 2
  • +3
4 Solutions
 
Gustav BrockCIOCommented:
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
 
Nick67Commented:
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
 
aikimarkCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
jrogersokAuthor Commented:
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
 
aikimarkCommented:
Something like this
Select *
From mytable
Where DLookup("Enumber", "tblExecCrosswalk", "Company = " & mytable.companycode & " and Enumber = " & mytable.EmployeeNumber) Is Null

Open in new window

0
 
Nick67Commented:
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
 
aikimarkCommented:
Note: Indexes are important for performance.
0
 
Nick67Commented:
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
 
aikimarkCommented:
You can also use the Exists() function/clause in your query.
0
 
bonjour-autCommented:
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
 
jrogersokAuthor Commented:
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
 
[ fanpages ]IT Services ConsultantCommented:
Was that mid-February 2016, or a later year?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now