Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    4

    Unanswered: ms access dlookup criteria question

    Greetings,
    This is my first post.. I am relatively novice with access vba. My questions relates to dlookup; I would like to compare a field in my form with the contents of a duplicate query. when I use dlookup without any criteria, the expression always only looks at the first record of the query. What would the syntax of dlookup be if I want to compare to ANY record of the query?
    Many thanks, toulouse1402

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Do you mean that you would like to know if a value entered in a textbox already exist in a column of a row in a table (or query)?

    If so, you can indeed use the third argument (criteria) of the DLookUp() function. Let's suppose that the textbox name is TextBox_1, the Table Table_1 and the column where to search Column_1. You can use:
    Code:
    If IsNull(DLookUp("Column_1", "Table_1", "Column_1 = '" & Me.TextBox_1.Value & "'")) Then
        ' DLookUp returns Null --> The value does not exist.
    Else
        ' DLookUp returns the value.
    End If
    If the value (and the definition of Column_1) is numeric, you don't use the single quotes around the value:
    Code:
    If IsNull(DLookUp("Column_1", "Table_1", "Column_1 = " & Me.TextBox_1.Value)) Then
    However, as you don't need the value that DLookUp would return (you already know it: it's in TextBox_1), you can also use:
    Code:
    If DCount("Column_1", "Table_1", "Column_1 = '" & Me.TextBox_1.Value & "'") = 0 Then
        ' DCount returns zero --> The value does not exist.
    This method is easier to use in a complex expression because you don't have to care for Null values (DCount always returns a number >= 0).

    Notice that these domain functions (DCount, DLookUp, DMax, DMin, etc.) are rather slow and can degrade the performances of your application if they are used extensively and if their domain of application (the data set: Table or query) is large (has many rows), but only a test can tell for sure. It is then advisable to create an index on the column submitted to the search to improve the performance.

    If they are called repeatedly in a procedure or a class module, the alternative to these functions consists in performing the search on a RecordSet object that remains open for the whole duration of the procedure or the existence of the instance of the class.
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    4

    thanks, it works with small variation

    Thanks for your quick response! You helped me figure it out. Here is my vba:
    the query is finddup (find duplicates), number field is the column name of the query contains the values of the field number that I want to check... the msg box is my way of debugging...

    for some reason, i need to use the [ ] or it's not happy.. using ms access 2000.
    Why would that be? thanks. toulouse1402

    If IsNull(DLookup("[number field]", "finddup", "[number field] = " & Me.number)) Then
    MsgBox "is null, unique"
    Me.duplicateMA = False
    Else
    MsgBox "not null, duplicate"
    Me.duplicateMA = True
    End If

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You need to enclose the names of objects (tables, columns, queries, controls, etc.) in square brackets ([]) if those names contain spaces or other non-alphanumeric characters, with the exception of the underscore (_).

    It's usually advisable to limit your naming conventions to valid characters (e.g. number_field instead of [number field]). Doing so will make your code more readable and less prone to errors.
    Have a nice day!

  5. #5
    Join Date
    Sep 2011
    Posts
    4

    thanks

    Awesome!! Many thanks again. Consider this thread complete.
    Toulouse1402

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    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
  •