Dlookup with Multiple Criteria Giving Type Mismatch

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
73
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


AreaMonthType
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:

VBA Code:
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
482
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

VBA Code:
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.

VBA Code:
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

Board Regular
Joined
Apr 14, 2016
Messages
73
Change to this:

VBA Code:
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.

VBA Code:
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.
 

Forum statistics

Threads
1,141,405
Messages
5,706,264
Members
421,437
Latest member
GijoeBlack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top