CODEWITHSUNDEEP
sqlvbams-access
Wilhelmroentgen
Wilhelmroentgen

Reputation: 163

Syntax using Dlookup in a loop with strings and dates

I am trying to get a command working to lookup if objects have been already selected for certain dates. I would like to do this with the Dlookup method, problem is, I am still getting used to SQL syntax. This is the code I am trying to get working:

    Dim i, coun as Integer
        For i = 1 to 10
             If IsNull(DLookup("[Column1]", "Table1", "[Column1] LIKE '*AT 1/*' & i  " 
              And (("[Date_Beg] BETWEEN #" & Me.txtDate1 & "#" AND "#" & Me.txtDate2 & "#") 
                  Or ("[Date_End] BETWEEN #" & Me.txtDate1 & "#" AND "#" & Me.txtDAte2 & "#")))) = True Then
             coun = coun
        else
             coun = coun + 1
        End If

When it gets executed, I get an error

Run-time error '13': Type mismatch

EDIT: Please go see my answer to see the code that ended up working for me.

Upvotes: 0

Views: 113

Answers (2)

Wilhelmroentgen
Wilhelmroentgen

Reputation: 163

Taking the suggestion from @pheeper and modifying his code a little bit I got the program working. This is what worked:

Dim i, coun as Integer     
For i = 1 To 10
       strSQL = "[Column1] LIKE ""*AT 1/" & i & "*"""
       strSQL = strSQL & " And (([Date_Beg] BETWEEN #" & Me.txtDate1 & "# AND #" & Me.txtDate2 & "#)"
       strSQL = strSQL & " Or ([Date_End] BETWEEN #" & Me.txtDate1 & "# AND #" & Me.txtDate2 & "#))"
       'Debug.Print strSQL
    
       If IsNull(DLookup("[Column1]", "Table1", strSQL)) = True Then
           coun = coun
       Else
           coun = coun + 1
       End If
Next i

Upvotes: 0

pheeper
pheeper

Reputation: 1527

The problem is with the SQL string you are trying to pass into the DLookup function. When you need SQL strings like this I find it best to store them as variable and then pass the variable into the function. Let me know if this works for you.

Sub QueryExample()

    Dim i, coun As Integer
    Dim strSQL As String
    
    For i = 1 To 10
         strSQL = "[Column1] LIKE *AT 1/*" & i
         strSQL = strSQL & " And (([Date_Beg] BETWEEN #" & Me.txtDate1 & "# AND #" & Me.txtDAte2 & "#)"
         strSQL = strSQL & " Or ([Date_End] BETWEEN #" & Me.txtDate1 & "# AND #" & Me.txtDAte2 & "#))"
         'Debug.Print strSQL

         If IsNull(DLookup("[Column1]", "Table1", strSQL)) = True Then
             coun = coun
         Else
             coun = coun + 1
         End If
    Next i
End Sub

Upvotes: 2

Related Questions