Results 1 to 3 of 3

Thread: DLookup (A2K)

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DLookup (A2K)

    I have the following code on a report that:
    If I change the DLOOKUP
    from--
    <pre>nPreviousYrValue = DLookup("yrTotal", "q30YrlyTotal", "Year = nMinYear")
    nCurrentYrValue = DLookup("yrTotal", "q30YrlyTotal", "Year = nMaxYear")</pre>

    to
    <pre>nPreviousYrValue = DLookup("yrTotal", "q30YrlyTotal", "Year = 2006")
    nCurrentYrValue = DLookup("yrTotal", "q30YrlyTotal", "Year = 2007")</pre>


    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:
    <pre>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</pre>



    Thanks.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookup (A2K)

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

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup (A2K)

    I solved it. It's as follows:

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


    Thanks everyone
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •