DLookup with array critera

D

DeathTrap82

Registered User.
Local time
Today, 09:03
Joined
Jun 25, 2012
Messages
10
I'm trying to write a DLookup (and DMax) using an array as one of the criteria (code snippet below). However, I'm not sure how to get it to work. I need it to only return the [PeriodStart] if the [TitleCode] is one of the numbers in the array titleCodeSearch. I had thought there was an "In" keyword, but I must be thinking of a different language.

Code: 
Dim rID as Integer, startTime as Date, titleCodeSearch(1 To 7) As Variant


    rID = r!DOMID
    titleCodeSearch(1) = 1717
    titleCodeSearch(2) = 1724
    titleCodeSearch(3) = 1455
    titleCodeSearch(4) = 1728
    titleCodeSearch(5) = 1732
    titleCodeSearch(6) = 3220
    titleCodeSearch(7) = 3394

If DLookup("[OtherActionTaken]", "[Appointment]", "[DOMID] = " & rID & " And [OtherActionTaken] = 'APT'") = "APT" Then
        startTime = DMax("[PeriodStart]", "[Appointment]", "[DOMID] = " & rID & " And [OtherActionTaken] = 'APT' And [TitleCode] In '" & titleCodeSearch & "'")
    End If
    If DLookup("[OtherActionTaken]", "[Appointment]", "[DOMID] = " & rID & " And [OtherActionTaken] = 'APPT'") = "APPT" Then
        If startTime < DMax("[PeriodStart]", "[Appointment]", "[DOMID] = " & rID & " And [OtherActionTaken] = 'APPT' And [TitleCode] In '" & titleCodeSearch & "'") Then
            startTime = DMax("[PeriodStart]", "[Appointment]", "[DOMID] = " & rID & " And [OtherActionTaken] = 'APPT' And [TitleCode] In '" & titleCodeSearch & "'")
        End If
    End If
 
So what really is the problem? It doesn't compile? It throws errors? The logic is not right?
 
Perhaps this is not the answer you're looking for, but you can simplify working with arrays like this...
Code: 
[COLOR="Green"]'all arrays subscripted starting at 1 by default[/COLOR]
Option Base 1

Sub Test18037469823764816734()
   Dim titleCodeSearch As Variant
   titleCodeSearch = Array(1717, 1724, 1455, 1728, 1732, 3220, 3394)
   msgbox Join(titleCoseSearch, ", ")
End Sub
 
Well, I made a simple test case to see if the "In" keyword worked and it doesn't. It throws a compile error (expression). I'm hoping one of you would know how to use an array as part of a DLookup and DMax criteria so I only get the results that have title codes matching one of the ones in my array.

Code: 
Dim titleCodeSearch(1 To 7) As Variant, temp As Integer

    temp = 1717
    
    titleCodeSearch(1) = 1717
    titleCodeSearch(2) = 1724
    titleCodeSearch(3) = 1455
    titleCodeSearch(4) = 1728
    titleCodeSearch(5) = 1732
    titleCodeSearch(6) = 3220
    titleCodeSearch(7) = 3394
    
    Debug.Print temp In titleCodeSearch
 
Alright I think I see what you're trying to do. The IN keyword works in any Domain Aggregate function, DLookup included. Here's an example:
Code: 
dim txtSearch as string
 
for x = lbound(titleSearchCode) to ubound(titleSearchCode)
    txtSearch = txtSearch & titleSearchCode(x) & ","
next
 
txtSearch = left(txtSearch, len(txtSearch) - 1)
 
somtextbox.Value = DLookup("FirstName", "Customers", "CustomerID IN [COLOR=red]([/COLOR]" & txtSearch & "[COLOR=red])[/COLOR]")
So the two things you missed where the parentheses in red and the structure of the IN clause.
 
vbaInet said:
Alright I think I see what you're trying to do. The IN keyword works in any Domain Aggregate function, DLookup included. Here's an example:
Code: 
dim txtSearch as string
 
for x = lbound(titleSearchCode) to ubound(titleSearchCode)
    txtSearch = txtSearch & titleSearchCode(x) & ","
next
 
txtSearch = left(txtSearch, len(txtSearch) - 1)
 
somtextbox.Value = DLookup("FirstName", "Customers", "CustomerID IN [COLOR=red]([/COLOR]" & txtSearch & "[COLOR=red])[/COLOR]")
So the two things you missed where the parentheses in red and the structure of the IN clause.
Click to expand...

I've tried adding that, but now I seem to be getting Error #13 (Type Mismatch). I feel like I'm missing something really obvious, but can't quite figure out what. Any ideas?

Code: 
    strTitleCodes = Array("Asst Prof-medcomp-a", "Asst Prof IR-mdcp-a", "Ast Prof Clin X-mdcp", "Asst Adj Prof-mdcp-a", "Ast Clin Prof-mdcp-a", "Asst Res-11mo", "Asst Proj __, Fy")
    rID = r!DOMID
    delay = 0
    delay = n!numDelays
    
    findEnd = "Function Failed"
    
    If DLookup("[OtherActionTaken]", "[Appointment]", "[DOMID] = " & rID & " And [OtherActionTaken] = 'APT' And [AbbrevTitle] In (" & strTitleCodes & ")") = "APT" Then
        startTime = DMax("[PeriodStart]", "[Appointment]", "[DOMID] = " & rID & " And [OtherActionTaken] = 'APT' And [AbbrevTitle] In (" & strTitleCodes & ")")
    End If
    If DLookup("[OtherActionTaken]", "[Appointment]", "[DOMID] = " & rID & " And [OtherActionTaken] = 'APPT' And [AbbrevTitle] In (" & strTitleCodes & ")") = "APPT" Then
        If startTime < DMax("[PeriodStart]", "[Appointment]", "[DOMID] = " & rID & " And [OtherActionTaken] = 'APPT' And [AbbrevTitle] In (" & strTitleCodes & ")") Then
            startTime = DMax("[PeriodStart]", "[Appointment]", "[DOMID] = " & rID & " And [OtherActionTaken] = 'APPT' And [AbbrevTitle] In (" & strTitleCodes & ")")
        End If
    End If
 
You're attempting to use the Array directly in the IN clause. That won't work. The values in the Array may be separated by commas, but that is not the same as a comma delimited string of values, which is what you need for the IN clause. If you look closer at vbaInet's example, you'll see that he loops through an Array (from LBound to UBound), taking each element and creating a comma delimited string.
 
Likewise, had to look up the Join function. Thanks! Wish I would have known about the join sooner.

Code: 
Function TrimSpace(strInput As String) As String
   ' This procedure trims extra space from any part of a string and then Join().
   Dim astrInput()     As String
   Dim astrText()      As String
   Dim strElement      As String
   Dim lngCount        As Long
   Dim lngIncr         As Long
 
   ' Split passed-in string.
   astrInput = Split(strInput)
 
   ' Resize second array to be same size.
   ReDim astrText(UBound(astrInput))
 
   ' Initialize counter variable for second array.
   lngIncr = LBound(astrInput)
   ' Loop through split array, looking for
   ' non-zero-length strings.
   For lngCount = LBound(astrInput) To UBound(astrInput)
      strElement = astrInput(lngCount)
      If Len(strElement) > 0 Then
         ' Store in second array.
         astrText(lngIncr) = strElement
         lngIncr = lngIncr + 1
      End If
   Next
   ' Resize new array.
   ReDim Preserve astrText(LBound(astrText) To lngIncr - 1)
   ' Join new array to return string.
   TrimSpace = Join(astrText)  ' join
End Function
'To test the TrimSpace procedure, try calling it from the Immediate window with a string like the following: Print TrimSpace(" This is a great tip ")
 
Beetle said:
You're attempting to use the Array directly in the IN clause. That won't work. The values in the Array may be separated by commas, but that is not the same as a comma delimited string of values, which is what you need for the IN clause. If you look closer at vbaInet's example, you'll see that he loops through an Array (from LBound to UBound), taking each element and creating a comma delimited string.
Click to expand...

Oh! I didn't notice that. Do I have to use a comma as a delimiter? Some of the titles in my array have commas in them, which will probably cause a few problems. When I try out the code below, I get the following error:

Error #3075
Syntax error (missing operator) in query expression '[DOMID] = 1 And [OtherActionTaken] = 'APT' And [AbbrevTitle] IN (Asst Prof-medcomp-a,Asst Prof IR-mdcp-a,Ast Prof Clin X-mdcp,Asst Adj Prof-mdcp-a,Ast Clin Prof-mdcp-a,Asst Res-11mo,Asst Proj_, Fy)'.
Click to expand...

Code: 
    strTitleCodes = Array("Asst Prof-medcomp-a", "Asst Prof IR-mdcp-a", "Ast Prof Clin X-mdcp", "Asst Adj Prof-mdcp-a", "Ast Clin Prof-mdcp-a", "Asst Res-11mo", "Asst Proj __, Fy")
    rID = r!DOMID
    txtSearch = ""
    delay = 0
    delay = n!numDelays
    
    For X = LBound(strTitleCodes) To UBound(strTitleCodes)
        txtSearch = txtSearch & strTitleCodes(X) & ","
    Next
    
    txtSearch = Left(txtSearch, Len(txtSearch) - 1)
    
    findEnd = "Function Failed"
    
    If DLookup("[OtherActionTaken]", "[Appointment]", "[DOMID] = " & rID & " And [OtherActionTaken] = 'APT' And [AbbrevTitle] IN (" & txtSearch & ")") = "APT" Then
        startTime = DMax("[PeriodStart]", "[Appointment]", "[DOMID] = " & rID & " And [OtherActionTaken] = 'APT' And [AbbrevTitle] IN (" & txtSearch & ")")
    End If
    If DLookup("[OtherActionTaken]", "[Appointment]", "[DOMID] = " & rID & " And [OtherActionTaken] = 'APPT' And [AbbrevTitle] IN (" & txtSearch & ")") = "APPT" Then
        If startTime < DMax("[PeriodStart]", "[Appointment]", "[DOMID] = " & rID & " And [OtherActionTaken] = 'APPT' And [AbbrevTitle] IN (" & txtSearch & ")") Then
            startTime = DMax("[PeriodStart]", "[Appointment]", "[DOMID] = " & rID & " And [OtherActionTaken] = 'APPT' And [AbbrevTitle] IN (" & txtSearch & ")")
        End If
    End If
 
For strings you need quotes around them.
Code: 
    For X = LBound(strTitleCodes) To UBound(strTitleCodes)
        txtSearch = txtSearch & [B][COLOR=red]Chr(34) & [/COLOR][/B]strTitleCodes(X) [B][COLOR=red]& Chr(34)[/COLOR][/B] & ","
    Next
 
You must log in or register to reply here.

Similar threads

H
help with linking outlook calender to form
Replies
0
Views
922
helphelphelp
H

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom