Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2000
    Location
    Calif
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    End Of Month Date (97 sr1)

    This seems like a simple thing but I worked on it for 3 hours and couldn't get it.
    I have a table "tbl3X8" with two fields "SCRAP" and "DATE"

    I have another table "tblEOM" with three fields "eom" and "date1" and "date2". They are all date fields. eom would be for example "8/1/01" "date1 7/28/01" "date2 8/24/01"
    The eom date falls between date1 and date2. In other words date1 and date2 define the range for eom.

    Im trying to make a query of tbl3X8 that would insert the eom date from tblEOM. I tried the following expression with no luck (this very same expression worked in a report and inserted the eom but will not work in a query, thats what puzzles me.
    =DLookUp('[eom]","tblEOM","DATE Between [date1] And [date2]")
    It just keeps saying that it cant find DATE. But DATE is in the query that this expression is in. This exact expression worked in a report, why won't it work in a query ? I hope this isnt too confusing. Thanks in advance for any help
    Cary

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: End Of Month Date (97 sr1)

    >>=DLookUp('[eom]","tblEOM","DATE Between [date1] And [date2]")<<

    Try this instead:
    =DLookup("eom","tbleom","Date Between #" & date1 & "# AND #" & date2 & "#")

    BTW, it is really not a good idea to name a field DATE, as this can easily conflict with the Access Date function.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Dec 2000
    Location
    Calif
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: End Of Month Date (97 sr1)

    Thanks for the help Mark but it did not work. It said it could not find date1 and date2.
    I did go back and change the field named DATE to DATEE so Access wouldnt get confused. In your original line above it said could not recognize "##" and "##" so all it saw was the number signs without date1 or date2 in between. I tried " and [ ] in different places and all I got was coul not find date1 and date2.
    Thanks anyway
    Cary

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: End Of Month Date (97 sr1)

    Where exactly are you using the DLookup? Is it in the query or in a textbox's controlsource? If a query, don't precede it with the = sign. Access will precede it with something like "Expr1: ". And in my example, I didn't use ##, only a single #.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: End Of Month Date (97 sr1)

    Sorry, but you said you had three fields, eom, date1 and date2, so where does the DATE field come from in your expression? Dlookup can only work with the domain specified, and you specified the tblEOM, which doesn't contain a DATE field. I think what you may want is something like this:

    DLookUp('[eom]","tblEOM","[date1]<=#" & DATE & "# And [date2]>=#" & DATE & "#")
    Charlotte

Posting Permissions

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