Results 1 to 9 of 9
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    Using DLookup to find value of a Yess/No field

    Dim varTag as variant


    varTag = DLookup("DelTag", "tblDisplay", "LinkID = " & RClickSel)

    As per Debug, varTag is False regardless of the current True/False state of "DelTag" where the field "DelTag" is defined as Yes/No. Is DLookup the inappropriate method to be using when fields are essentially boolean? I've failed to find a function that converts variant to boolean............ unless one has to use multiple conversions like Variant to Number to boolean?

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So "DelTag" is a Boolean type (Yes/No) field in table "tblDisplay".
    Table "tblDisplay" also has a field "LinkID" that is LongInt??

    What is the value of RClickSel when the code (DLookup) executes?
    And there is a record in table "tblDisplay" that is the value of "RClickSel"?

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    RClickSel is the record ID "LinkID" in tblDisplay as verified in Debug. But IsNull(VarTag) returns "False" no matter what the state of "DelTag".

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Very strange.....

    I created a table and a form. On the form I added an unbound text box named "RClickSel". I added a button with the click event code of
    Code:
        Dim varTag As Variant
        varTag = DLookup("DelTag", "tblDisplay", "LinkID = " & RClickSel)
        Debug.Print varTag
    I added a few records to the table, both true and false.

    I entered a "LinkID" number (2) that had "DelTag" as false and clicked the button. 0 was returned.
    I changed the text box "RClickSel" to 4 (a true value) and clicked the button. 0 was returned. (Hmmmm)
    I changed "Dim varTag As Variant" to "Dim varTag As Boolean" and clicked the button. -1 was returned.
    I changed the text box "RClickSel" to 2 (a false value) and clicked the button. 0 was returned.

    I changed "Dim varTag As Boolean" back to "Dim varTag As Variant".
    I set text box "RClickSel" to 2 (a false value) and clicked the button. 0 was returned.
    I set text box "RClickSel" to 4 (a true value) and clicked the button. -1 was returned. (what????)


    Deleted that dB and created another dB - set it up the same way..
    Tried the same things.
    But Debug.Print varTag prints the correct values now.

    Don't know what to suggest now.......

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Thanks for all your efforts. Don't spin your wheels on this. I'm going to take the easy way out.

    The general module that hosts an assortment of right-click functions is immediately preceded by code in a MouseDown event upon a bound text box where acRightBotton is tested. It's at that point that the event code sets a global variable giving the functions access to the various fields within the record like the current DelTag setting. Since the event code has ready access to Me.chkDelTag within the event, I'll "throw that over the wall" like I do with the record ID.

    Thanks again,
    Bill

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Perhaps Steve's tests act as a further proof of why using variants should be avoided in most cases.
    I use them in arrays but otherwise almost never.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Hi Colin,
    Yes, I too avoid the use of variants. As I think of it, my use is pretty much limited to the DLookup target variable to avoid the error when Null is returned.
    Bill

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If you wrap your DLookup with the Nz function, there's no need to use a variant to handle nulls
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    An excellent idea and a practice I'll work towards adopting in efforts to avoid the use of variant.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 01-20-2018, 03:40 PM
  2. Using DLookup to find a value in a field.
    By julianholworth in forum Access
    Replies: 6
    Last Post: 05-13-2016, 06:35 PM
  3. Replies: 3
    Last Post: 08-24-2015, 03:30 PM
  4. Dlookup to find value on another form
    By burrina in forum Forms
    Replies: 16
    Last Post: 11-04-2012, 07:39 PM
  5. Dlookup to find value in a range
    By jobrien4 in forum Access
    Replies: 1
    Last Post: 09-26-2011, 11:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums