Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DLookup function (Off2003)

    I am trying to return the value of a Year based on the event date. The start and end dates for the Years are in QryYears - the query I wish to DLookup to.
    The DLookup expression is in the QryChgEvent.
    The start and end dates are in the QryYears
    I have an old Access book which uses some extra [ ] in the expression and MS Hel which doesn't! I'd appreciate some help here please to get it right.

    taxyrlu: DLookUp("Year","QryYears"," [QryChgEvent]![chgevdate]=>[YrStart] and [QryChgEvent]![chgevdate]=< [YrEnd]")
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

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

    Re: DLookup function (Off2003)

    If I interpret your description correctly, you should use

    taxyrlu: DLookUp("Year","QryYears","YrStart <= #" & Format([chgevdate],"mm/dd/yyyy") & "# AND YrEnd >= #" & Format([chgevdate], "mm/dd/yyyy") & "#")

    Note the use of <= and >= instead of =< and =>
    E-mail notification of this post will probably contain errors, so please view the thread in the Lounge itself.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup function (Off2003)

    Hi Hans,
    That what exactly what I was looking for and it works too - many thanks indeed. In evaluating the function you provided it is markedly different to that I was toying with, back to the learning board - again.
    Regards
    - Stephen
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

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

    Re: DLookup function (Off2003)

    The value from the query itself (chevgdate) has to be outside the quotes so that it is used as a literal value.
    Date values *must* be specified in US date format, regardless of your system settings, so I used the Format function to convert chevgdate to US format.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup function (Off2003)

    Hi again Hans,
    May I trouble you once again, please?
    I have built a subform on the query above to be used in a form. The main form has a field that displays the Year.
    I want to link the query year to the main form year but the error is received "you are trying to link fields with incompatible data types" I can see that the main for field TRYear is described ans an Integer fiels and the subform field year is displayed as text. In the query field we discussed above I have it formatted as General Number. The format of the field in the base query qryYears is Integer. How can i change the field type to integer please?
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

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

    Re: DLookup function (Off2003)

    Try

    taxyrlu: Val(DLookUp("Year","QryYears","YrStart <= #" & Format([chgevdate],"mm/dd/yyyy") & "# AND YrEnd >= #" & Format([chgevdate], "mm/dd/yyyy") & "#"))

    This will fail, however, if DLookup returns a null (blank) value. You can get around that by using Nz:

    taxyrlu: Val(Nz(DLookUp("Year","QryYears","YrStart <= #" & Format([chgevdate],"mm/dd/yyyy") & "# AND YrEnd >= #" & Format([chgevdate], "mm/dd/yyyy") & "#"),9999))

    This will return 9999 (a non-existing year) if the DLookup function returns null.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup function (Off2003)

    Very impressive Hans,
    Thank you. Works good.
    Regards
    - Stephen
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

Posting Permissions

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