Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    44

    Unanswered: Dlookup function

    I'm running a macro to send a report depending on the condition.

    My condition is DLookup("[UnitNo]","BBqry")="01"
    I don't know if this syntax is correct or not but I'm not getting any error messages, nor am I getting the report if that condition is true.

  2. #2
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    If your DLookup line looks like what you put in your message, you have a misplaced parenthesis.

    Not
    DLookup("[UnitNo]","BBqry")="01"

    but
    DLookup("[UnitNo]","BBqry= 01")

    Number vs String
    if the field [UnitNo] is a number then
    DLookup("[UnitNo]","BBqry= 01")

    if the field [UnitNo] is a text field then
    DLookup("[UnitNo]","BBqry= '01'")
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  3. #3
    Join Date
    Mar 2004
    Posts
    44
    UnitNo is a text field, however when i tried your syntax it still didn't work. I thought the name of the query, BBqry, had to be in quotes. That's how the Access help outlines it.
    I know the value is true but no report is being sent.

    How about this: Dlookup("[UnitNo]","BBqry")Is Not NUll

  4. #4
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Forgive me. I was stupid for a second.

    There are three parameters necessary for the DLookup
    1)Field, 2)table, 3)criteria

    DLookup("[UnitNo]","YourTableName", "BBqry= 01")
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  5. #5
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    PS. Why are you running a macro? I guess I've never tried DLookup from a macro.

    Using code is easier to edit, allows more controls, provides a means for adding comments, etc. Consider using VBA.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  6. #6
    Join Date
    Mar 2004
    Posts
    44

    Unhappy

    I'm sorry, it still doesn't work. I know it can't be this hard.

    BBqry is the query name. All I need to know is if when the query is run,UnitNo is null or not. If not, it will display the value "01".

    If it displays "01" then a report will be generated. I've tried at least 10 different ways to word the syntax, but no luck.

  7. #7
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Something that is helpful is to read the help (self apparent). Lookup DLookup - it tells about the parameters and what it returns if it cannot find any results.

    If no record satisfies criteria or if domain contains no records, the DLookup function returns a Null. There are two ways to solve this.
    Apply the NZ function - see help. This returns a zero if null is the result of the function.

    Nz(DLookup("[UnitNo]","YourTableName", "BBqry= 01"))

    I thought you were looking for a known value. Dcount will return zero if no record satisfies the criteria. Otherwise it will return the number of records that meet the critieria.

    DCount("[UnitNo]","YourTableName", "BBqry= 01")

    Let me know if that doesn't make sense.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  8. #8
    Join Date
    Mar 2004
    Posts
    44
    I've read the help and actually had this syntax working last week, but stupid me deleted the macro it was in.

    The value of UnitNo for the query BBqry will always be "01" if there are any records. I'm sorry to keep bothering you but this is driving me crazy.

    i even tried this: DLookUp("BBqry","[UnitNo]"="01")

  9. #9
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Remember you need three parameters.

    Try either of the following. I added the single quotes around the 01 because it is a string (field of text type).

    Nz(DLookup("[UnitNo]","YourTableName", "BBqry= '01'"))

    OR

    DCount("[UnitNo]","YourTableName", "BBqry= '01'")
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  10. #10
    Join Date
    Mar 2004
    Posts
    44
    Thank you for your help. I used this and it worked:

    Nz(DLookup("[UnitNo]","Orders", "BBqry= '01'"))

  11. #11
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303

    Thumbs up

    Groovy!
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

Posting Permissions

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