Syntax issue with the filter in DlookUp

[Ben_Entrew]

Hi all,

something is wrong with my syntax.
Does anyone has a clue? If I type 2014 instead of b everything works out.
RepMonth is 012014.

Dim a As Double
Dim b As String

b = Right(RepMonth, 4)

a = DLookup("[EUR/TRY]", "Plan_FX_RATES", "YEAR = '" & b & "'")
Debug.Print a
Debug.Print b
Debug.Print RepMonth

Thanks in advance.

Regards,
Ben

 

[pr2-eugin]

Put the Debug.Print b, Debug.Print RepMonth statements before DLookUp statement, and show what you get, you will for sure find they are wrong.

 

[Ben_Entrew]

Hello Paul,
Debug Print give me the right results:

b = 2014

RepMonth = 012014

Coulmn YEAR in the Plan_FX_RATES table is a number column,
is this maybe the issue?

 

[Ben_Entrew]

Paul,

I found it out. The issue was with the Column YEAR, I changed it in Text format.
Now it works.

Thank you anyway, you helped me out many time.

Regards,

Ben

 

[pr2-eugin]

I would have not gone through the hassle of changing the data type in the table, but simply changed the CODE to make the variable Long, Anyway ! I am also going to spare the lecture of YEAR is an awful field name, I am hoping you will change it !

Told you, you can figure it on your own ! Good lad !