Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access Query (QBE) Using VBA Function for Criteria

Posted on 2015-01-14
13
Medium Priority
?
95 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
12 Comments
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 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 500 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 46

Assisted Solution

by:aikimark
aikimark earned 500 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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 46

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
 
LVL 46

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 46

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 500 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

879 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