Results 1 to 9 of 9
  1. #1
    Join Date
    May 2011
    Posts
    47

    DLookup with multiple conditions

    I have a DLookup with multiple conditions that appears to be evaluating values from different records in the db (i.e. evaluating True for condition 1 on record 5, and evaluating True on condition 2 on record 10). Is the the proper way for DLookup to work? If that's so, is it true that DLookup does not work like a SQL WHERE clause, where each record is evaluated as true or false?

    thanks for any help

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,275
    I have had no problems with Dlookup
    did you know
    you can do

    aaa = DLookup("this","Fromthistable","bla='bla' and thisis=false and that='this'")


    are you looking at the samething data as dlookup is looking at
    I know I have spent a number of hours telling the dlookup it was wrong when it was me
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE

  3. #3
    Join Date
    May 2011
    Posts
    47

    DLookup

    I've been through it many times and I understand what you're saying. Here it is:

    If Not IsNull(DLookup("[field_1]", "tbl_detail_all", "field_1 = '" & Me.cmbo_scenario & "'")) And _
    Not IsNull(DLookup("[field_2]", "tbl_detail_all", "field_2 = 'Accounts Receivable'")) Then

    Update...

    Else

    Insert...

    db looks like this:

    field_1 XXX YYY ZZZ
    AAA BBB CCC field_2

    I believe my logic (condition on DLookuo) would INSERT my new data. Instead it skips over the insert (when I debug). Again, it appears that it is evaluating on two seperate rows in the db.

    I know I'm wrong but I just can't figure this one out.

    Thanks for any help.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,253
    DLookUp returns the value of the specified column for one row matching the criteria (if any). Nothing garantees that the same row will be selected if you call the function twice successively with a different criteria, except if the criteria concerns the primary key column or a column with a UNIQUE constraint.

    In your case, you should combine both conditions into a single criteria and call DLookUp only once (provided that the combination of both conditions constitutes a unique condition for the table):
    Code:
        Dim strCriteria As String
        
        strCriteria = "field_1 = '" & Me.cmbo_scenario & "' AND field_2 = 'Accounts Receivable'"
        If Not IsNull(DLookup("[field_1]", "tbl_detail_all", strCriteria)) Then
    Have a nice day!

  5. #5
    Join Date
    May 2011
    Posts
    47

    DLookup

    Thanks so much. I now see why I had the problem (i.e. using the DLookup function twice).
    Again, Thank you.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,253
    You're welcome!
    Have a nice day!

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,275
    I would use a Dcount as it would return a 0 if not there
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE

  8. #8
    Join Date
    May 2011
    Posts
    47

    DLookup

    Thanks much.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,253
    Good idea
    Have a nice day!

Posting Permissions

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