Using The In Function In Criteria

[DMT Dave]

Hi Guy's, hope all is good with you all



I have had a quick search and can't find the correct syntax for using the In Function within a search criteria

A: Finding a result based on a single number is fine ie: 7

B: Finding a result between 2 numbers is fine ie: 1 to 15

C: Now need to find the result based on a break of numbers trying to use the In function ie: 10,11,13,14 (12 not included)

A: Single Number OK

intID = Me.ID

Select Case intID


Case Is = 22
    blNew = True
    iSort = 10
    
    iQty = DSum("StartQty", "tblStock", "[NewType] = " & blNew & " And [SortNo] = " & iSort)
    
    Debug.Print "ID: " & intID & vbCrLf & "Qty: " & iQty

B: Between Numbers

Case Is = 28

    blNew = True
    iSortStart = 1
    iSortEnd = 15
    
    iQty = DSum("StartQty", "tblStock", "[NewType] = " & blNew & " And [SortNo] Between " & iSortStart & " And " & iSortEnd)
    
    Debug.Print "ID: " & intID & vbCrLf & "Qty: " & iQty

C: Use the In Function as there is a break in what to find (can't find syntax

Case Is = 23


    blNew = True
    
    iQty = DSum("StartQty", "tblStock", "[NewType] = " & blNew & " And [SortNo] In (11,12,14,15))
    
    Debug.Print "ID: " & intID & vbCrLf & "Qty: " & iQty

As always thank you for help

[Welshgasman]

Dave,
How many times do I have to say it?

Put your criteria into a string variable.
Debug.Print it until you get it correct.
Then use that in the function.

Applies to SQL statements as well.

You have not ended the DSum with quotes, have you?

? dsum("Carbscalc","tbldaily","DailyID IN (7,9,11)")
 32.26

[DMT Dave]

Hi WGM, i was trying to debug.Print but couldn't get the syntax correct, i assumed i would need to get that correct before result would appear in immediate window !!!!

Thanks for input

[DMT Dave]

Thanks WGM, it was only that closing Quote i missed, this works fine now, i also needed to add in that if Qty = 0

Now get the correct answer in Debug Window, i was wrong to think that the syntax need to be correct before results appear perhaps....

Thanks to Sunny Wales again

    Select Case blNew        
        Case Is = False
            iQty = 0
        Case Else
            iQty = DSum("StartQty", "tblStock", "[NewType] = " & blNew & " And [SortNo] In (11,12,14,15)")
        End Select
    
    Debug.Print "ID: " & intID & vbCrLf & "Qty: " & iQty

[Micron]

AFAIK in every domain function the parameters are strings. You began the last parameter with a double quote but left off the ending quote. Obvious now I guess but it seems you were going to do that because you had " And:

DSum("StartQty", "tblStock", "[NewType] = " & blNew & " And [SortNo] In (11,12,14,15))

Moral of the story is pay particular attention to your delimiting. At first, I thought IN was strictly for SQL and went looking and found this

Most VBA operators may be used in SQL too. However, Between and In may only be used in SQL:

I guess they are wrong about IN and perhaps Between as well when it comes to domain functions.

[DMT Dave]

Hi micron, as per WGM Pauls nicely spotted schoolboy error of mine, i missed the closing quote off, but as far as result of the search, i just thought i would never get an answer in the debug window if i don't have the correct syntax.

All works using the In Function as per WGM correction of the closing quote

[Welshgasman]

A string is just a string?
When you Debug.Print it you will see what you actually have, not what you think you have.

That is why I do it myself and I tell everyone to do this. Not just you, multiple times.

[señor_sriracha]

I think I picked this up from Stack Overflow, but it really takes the guesswork out of string concatenation.
It doesn't include all of the alignment codes that you can do in .NET but it works well for basic concatenation.

' Format string using the .NET way
Public Function StringFormat(ByVal strValue As String, ParamArray arrParames() As Variant) As String
    Dim i As Integer
    ' Replace parameters
    For i = LBound(arrParames()) To UBound(arrParames())
        strValue = Replace(strValue, "{" & CStr(i) & "}", CStr(Nz(arrParames(i), "")))
    Next


    ' Get the value
    StringFormat = strValue
End Function

A1 = "foo"
A2 = "bar"
A3 = StringFormat("Some text {0}, more text: {1}", A1, A2)

' A3 = "Some text foo, more text: bar"

[DMT Dave]

Hi Guys, thank you, will check it all out