none
use of AND operator in Dlookup Function RRS feed

  • Question

  • Is it possible to use the AND operator in the dlookup function?

    I want to use dlookup to see if a record exists in a table that meets criteria for a users clocknumber AND a specific other value for a field named DocumentNumber.

    I can make it work for the users clocknumber, but cannot get the and part of the criteria to work for the DocumentNumber field.

    Friday, May 31, 2019 12:05 AM

Answers

  • Apparently DLookup returns a string value, not a True/False value as required for If … Then.

    Try this:

    V_NullTest = IsNull(DLookup("[Training_Method]", "TrainingDocsQueueTable", "[Training_Method] <> 'Class Room' AND [CLOCK_NUMBER] = " & Me.UserID))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by tkosel Sunday, June 2, 2019 5:07 PM
    Sunday, June 2, 2019 2:47 PM
  • Try:

    DLookup("[Training_Method]", "TrainingDocsQueueTable", "[Training_Method] <> 'Class Room' " AND [CLOCK_NUMBER] = ' " & Me.UserID)

    • Marked as answer by tkosel Sunday, June 2, 2019 5:07 PM
    Sunday, June 2, 2019 4:56 PM

All replies

  • Sure you can.

    AirCode Sample

    DLookUp("FieldName", "TableName", "[OtherField]=12 AND [AnotherField]=45")

    Don't forget Text criteria need to be surrounded by single quotes, date by pound signs...


    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Friday, May 31, 2019 1:16 AM
  • Daniel,

    Thanks for your insight.  I almost have it working.  In the immediate window, the code below works, yields what I expect.

    ? DLookup("[Training_Method]", "TrainingDocsQueueTable", "[Training_Method] <> 'Class Room' AND [CLOCK_NUMBER] = "& ME.UserID)
    Read and Understand

    In the forms Load Event this code fails.

    If DLookup("[Training_Method]", "TrainingDocsQueueTable", "[Training_Method] <> 'Class Room' AND [CLOCK_NUMBER] = " & Me.UserID) Then
            V_NullTest = False
        Else
            V_NullTest = True
        End If

    What am I doing wrong?

    Sunday, June 2, 2019 2:41 PM
  • Apparently DLookup returns a string value, not a True/False value as required for If … Then.

    Try this:

    V_NullTest = IsNull(DLookup("[Training_Method]", "TrainingDocsQueueTable", "[Training_Method] <> 'Class Room' AND [CLOCK_NUMBER] = " & Me.UserID))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by tkosel Sunday, June 2, 2019 5:07 PM
    Sunday, June 2, 2019 2:47 PM
  • Try:

    DLookup("[Training_Method]", "TrainingDocsQueueTable", "[Training_Method] <> 'Class Room' " AND [CLOCK_NUMBER] = ' " & Me.UserID)

    • Marked as answer by tkosel Sunday, June 2, 2019 5:07 PM
    Sunday, June 2, 2019 4:56 PM
  • Hans, Lawrence, and Daniel,

    Again, thanks for your help.  You all gave great advice.  I learned a lot from you all, which always welcome.

    Sunday, June 2, 2019 10:02 PM
  • Yes, it is possible to use the AND operator in the dlookup function. In MS Access application DLookup function is used to get value of particular field from specified set of records (a domain). You can use this DLookup function in Visual Basic for Applications (VBA) module, a query expression, a macro, or a calculated control on a form or report.




    Wednesday, July 10, 2019 9:51 AM