Dlookup Problem

[Guest]

I have a small problem with a Dlookup I'm tryin to use.

Scenario is:
A table called tblYearCodes has the fields Year, and YearCode, both set as
text.
Year contanis Years From 2006 to 2027.
YearCode Contains letter from C to Z (Except I and O)

I've used the code below in an attempt to find the current year, and then
lookup the appropriate code for the year.

strYear = Right(Date$, 4)
strYearCode = DLookup("[YearCode]", "[tblYearCodes]", "[Year]=strYear")

The first line works ok and returns '2006' but I get an error with the
Dlookup line the says

Error 2001 - you cancelled the previous operation.

From my research I've managed to work out that there's a problem with the
syntax of the Dlookup line, but I can't for the life of me work out what it
is!

Can anyone put me out of my misery?

Thanks in advance.

Neil

 

[John Spencer]

strYearCode = DLookup("[YearCode]", "[tblYearCodes]", "[Year]=""" & strYear
& """")

You need to pass DLookup the Value of strYear, not a reference to the
variable. DLookup has no idea what strYear is.
Since StrYear is a string and so is [Year] then you also need to delimit the
strYear value with quote marks.

By the way, Year is not a good field name, since Access has a function Year
that returns the year number of a date. In other words, "Year" is a
reserved word. You probably won't have any problems, but ...

 

[Guest]

Thanks for the solution and advice John, I'll make some changes just in case.

Neil

strYearCode = DLookup("[YearCode]", "[tblYearCodes]", "[Year]=""" & strYear
& """")

You need to pass DLookup the Value of strYear, not a reference to the
variable. DLookup has no idea what strYear is.
Since StrYear is a string and so is [Year] then you also need to delimit the
strYear value with quote marks.

By the way, Year is not a good field name, since Access has a function Year
that returns the year number of a date. In other words, "Year" is a
reserved word. You probably won't have any problems, but ...

I have a small problem with a Dlookup I'm tryin to use.

Scenario is:
A table called tblYearCodes has the fields Year, and YearCode, both set as
text.
Year contanis Years From 2006 to 2027.
YearCode Contains letter from C to Z (Except I and O)

I've used the code below in an attempt to find the current year, and then
lookup the appropriate code for the year.

strYear = Right(Date$, 4)
strYearCode = DLookup("[YearCode]", "[tblYearCodes]", "[Year]=strYear")

The first line works ok and returns '2006' but I get an error with the
Dlookup line the says

Error 2001 - you cancelled the previous operation.

From my research I've managed to work out that there's a problem with the
syntax of the Dlookup line, but I can't for the life of me work out what
it
is!

Can anyone put me out of my misery?

Thanks in advance.

Neil