Dlookup with Multiple Criteria Giving Type Mismatch

[MCTampa]

I have a form with a drop down for Area Code and Month. Both are numbers.
I am trying to populate a text box with the Type which is found on a table called Seasonality.
Here is what the table Seasonality looks like:

Seasonality

Area	Month	Type
330​	1​	Mid
330​	2​	Peak
330​	3​	Peak
330​	4​	Mid
330​	5​	Mid
330​	6​	Mid
330​	7​	Peak
330​	8​	Mid
330​	9​	Low
330​	10​	Low
330​	11​	Low
330​	12​	Low

Area and Month are both numbers and Type is text.
My code is as follows:

Private Sub Month_AfterUpdate()
Seasonality = DLookup("[Type]", "Seasonality", "[Area]=" & AreaCode.Value And "[Month]=" & Month.Value)
End Sub

My issue is that it will function with just Area Code or just Month, but it will not function with the combination of both.
Yes, it returns the wrong value, but I do not get a Type Mismatch error when using one or the other. I get the error when using both.

Thanks,
Mike

 

[snjpverma]

Typed in error

 

[JonXL]

My issue is that it will function with just Area Code or just Month, but it will not function with the combination of both.

Change to this:

Private Sub Month_AfterUpdate()
Seasonality = DLookup("[Type]", "[Seasonality]", "[Area] = " & Me.AreaCode.Value & " AND [Month] = " & Me.Month.Value)
End Sub

Yes, it returns the wrong value, but I do not get a Type Mismatch error when using one or the other. I get the error when using both.

Throw in some Debug.Print to make sure the values are what you expect.

Private Sub Month_AfterUpdate()
Debug.Print Me.AreaCode.Value
Debug.Print Me.Month.Value
Debug.Print TypeName(Me.AreaCode.Value)
Debug.Print TypeName(Me.Month.Value)
Seasonality = DLookup("[Type]", "[Seasonality]", "[Area] = " & Me.AreaCode.Value & " AND [Month] = " & Me.Month.Value)
End Sub

 

[MCTampa]

Change to this:

Private Sub Month_AfterUpdate()
Seasonality = DLookup("[Type]", "[Seasonality]", "[Area] = " & Me.AreaCode.Value & " AND [Month] = " & Me.Month.Value)
End Sub

Throw in some Debug.Print to make sure the values are what you expect.

Private Sub Month_AfterUpdate()
Debug.Print Me.AreaCode.Value
Debug.Print Me.Month.Value
Debug.Print TypeName(Me.AreaCode.Value)
Debug.Print TypeName(Me.Month.Value)
Seasonality = DLookup("[Type]", "[Seasonality]", "[Area] = " & Me.AreaCode.Value & " AND [Month] = " & Me.Month.Value)
End Sub

Worked perfectly, thank you.