DLookup (A2K)

Topic

New Reply

I have the following code on a report that:
If I change the DLOOKUP
from—

nPreviousYrValue = DLookup("yrTotal", "q30YrlyTotal", "Year = nMinYear")
nCurrentYrValue = DLookup("yrTotal", "q30YrlyTotal", "Year = nMaxYear")

to

nPreviousYrValue = DLookup("yrTotal", "q30YrlyTotal", "Year = 2006")
nCurrentYrValue = DLookup("yrTotal", "q30YrlyTotal", "Year = 2007")

it works fine, however it won’t work unless I hard code the year in.

I’m certain it’s in the syntax, but apparently I’ve looked at it far to long and don’t see it. Could someone offer an idea?

Below is the entire code I’m working with:

Dim nMinYear As Integer, nMaxYear As Integer
Dim nPreviousYrValue As Integer, nCurrentYrValue As Integer

nMinYear = DMin("Year", "q30YrlyTotal")
nMaxYear = DMax("Year", "q30YrlyTotal")

nPreviousYrValue = DLookup("yrTotal", "q30YrlyTotal", "Year = 2006")
nCurrentYrValue = DLookup("yrTotal", "q30YrlyTotal", "Year = 2007")

Me.Previous = nPreviousYrValue
Me.Current = nCurrentYrValue

Thanks.

Reply | Quote

Replies

You cannot include a VBA variable directly in the WhereCondition – it is evaluated by the Jet Engine, and the Jet Engine doesn’t know about your variables. Use this:

nPreviousYrValue = DLookup(“yrTotal”, “q30YrlyTotal”, “Year = ” & nMinYear)
nCurrentYrValue = DLookup(“yrTotal”, “q30YrlyTotal”, “Year = ” & nMaxYear)

The variables nMinYear and nMaxYear have been placed outside the quotes and concatenated with the expression “Year = “.

Reply | Quote

I solved it. It’s as follows:

nPreviousYrValue = DLookup("[yrTotal]", "q30YrlyTotal", "[Year] = " & nMinYear)
nCurrentYrValue = DLookup("[yrTotal]", "q30YrlyTotal", "[Year] = " & nMaxYear)

Thanks everyone

Reply | Quote