Results 1 to 9 of 9

Using DLookup to find value of a Yess/No field

  1. #1
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,059

    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 online now Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,574
    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"?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  3. #3
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,059
    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 online now Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,574
    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.......
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,059
    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
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,007
    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 (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment
    nil illegitimi carborundum est

  7. #7
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,059
    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
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,007
    If you wrap your DLookup with the Nz function, there's no need to use a variant to handle nulls
    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment
    nil illegitimi carborundum est

  9. #9
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,059
    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, 02:40 PM
  2. Using DLookup to find a value in a field.
    By julianholworth in forum Access
    Replies: 6
    Last Post: 05-13-2016, 05:35 PM
  3. Replies: 3
    Last Post: 08-24-2015, 02:30 PM
  4. Dlookup to find value on another form
    By burrina in forum Forms
    Replies: 16
    Last Post: 11-04-2012, 06:39 PM
  5. Dlookup to find value in a range
    By jobrien4 in forum Access
    Replies: 1
    Last Post: 09-26-2011, 10: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
  •  
Tech Forums: Microsoft Office Forums