DLOOKUP with multiple AND, and OR Criteria (1 Viewer)

[astrodude2183]

Hi, I am struggling to return a record using DLookup (ELookup, actually, but if I can get it to work with DLookup I am pretty sure I can get it to work with ELookup) and with convoluted Critera.

The DLookup line is:

DLookup ("Name", "tableCreatures", strCriteria)

The strCriteria logic is:

• Where Level = intLevel, AND
• Where Climate = (strClimate OR "Any"), AND
• Where Terrain = (strTerrain OR "Any")

strCriteria is a string.
intLevel is an integer. The data type for this field in the table is set to Number.
strClimate and strTerrain are strings. The data type for this field in the table is set to Short Text.

I have tried a couple of variations of formatting strCriteria, including the following:

• strCriteria = "Climate LIKE '*" & strClimate & "*'" <-- This works, but is a single criteria
• strCriteria = "Terrain Like '*" & varTerrain & "*' And Climate Like '*" & varClimate & "*'" <-- This works as well.
• strCriteria = "Level =" & intLevel <-- This works, too.

My first problem is that when I try to combine the Level criteria with either the Climate or Terrain criteria, I get a various errors (Data Mismatch, Syntax Error, etc.
My second problem, is I can't figure out how to get that "or" part of my logic to work, when I try to include Or strClimate = 'Any' for example, it seems to just return the first record in the tableCreatures table regardless of if it meets my Criteria.

Option Compare Database

Public Function p2eBuildEncounter(intLevel As Integer, strClimate As String, strTerrain As String)
    
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim intLevel As Integer
    Dim strCriteria As String
    Dim varQuery As Variant

 
 ' Plain English of what strCriteria should be:
    ' WHERE Level = intLevel
        'AND Climate includes either strClimate or the word "Any"
        'AND Terrain includes either strTerrain or the word "Any"
 
 '   strCriteria = "Level =" & intLevelPlus4                     
 '   strCriteria = "Climate LIKE '*" & strClimate & "*'"       
    strCriteria = "Level = & intLevel & And Climate Like '*" & strClimate & "*' Or Climate = 'Any' And Terrain Like '*" & strTerrain & "*'" Or Terrain = 'Any'"
    Debug.Print ; strCriteria
 
    varQuery = DLookup("Name", "tableCreatures", strCriteria)   
    
End Function

 

[SHANEMAC51]

The strCriteria logic is:

• Where Level = intLevel, AND
• Where Climate = (strClimate OR "Any"), AND
• Where Terrain = (strTerrain OR "Any")

strCriteria = "Level =" & intLevel _
& " And (Climate Like '*" & strClimate & "*' Or Climate = 'Any')" _
& " And (Terrain Like '*" & strTerrain & "*' Or Terrain = 'Any')"
Debug.Print strCriteria

 

[astrodude2183]

That worked! Thank you so much. It was the parentheses that I was screwing up, but now I've learned something!