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

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 ...


"Neil" <(E-Mail Removed)> wrote in message
news:C3E36D2B-3924-47EB-9EDE-(E-Mail Removed)...
>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

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

Neil

"John Spencer" wrote:

> 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 ...
>
>
> "Neil" <(E-Mail Removed)> wrote in message
> news:C3E36D2B-3924-47EB-9EDE-(E-Mail Removed)...
> >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
>
>
>