Results 1 to 2 of 2
  1. #1
    j6sander is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    6

    Handling Null DLookup result

    I am tracking the movements of people in my database. I have a Log table that stores all the recorded moves and multiple other tables that provided the data entry options, such as a clients table, actions, address, ect.. Part of the client table is an Yes/No field that I have converted to In/Out (-1 & 0 either way you look at it). Based on the users "action" selection in the Form for the Log table I would like the clients In/Out status to be updated accordingly. Each action has an associated numerical value, for example "calls in from" have a value of 1 as I have a call in counter for each client. "signs in from" has a -1 value and "signs out to" has a 0 value because of the Yes/No field. So I am doing this is a Dlookup, which works great, except for when it doesn't fit the criteria and returns a null value which the Yes/No always fills with a value and tables the clients as "Out". I can't figure out how to deal with this. I will show you what I've done so far, my brain is fried right now. I tried the NZ(Dlookup and IF Else and combos. Nothing works right.

    Private Sub Action1_AfterUpdate()


    Dim InOutT As String

    InOutT = DLookup("[Effect]", "ActionT", _


    "[Action] = '" & Action1 & "' And [Effect] = -1 Or 0")
    If InOutT = 0 Then
    Cancel = True
    Else
    Me.InOut = InOutT
    End If


    I also tried...


    Private Sub Action1_AfterUpdate(Cancel As Boolean)


    Dim InOutT As String

    InOutT = Nz(DLookup("[Effect]", "ActionT", _
    "[Action] = '" & Action1 & "' And [Effect] = -1 Or 0"))
    If InOutT = "" Then
    Cancel = True
    Else
    Me.InOut = InOut
    End If

    End Sub


    Thank you very much for your interest. I know im missing more info that you might need to solve this. I've been staring at the screen for hours. My apologies.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    For starters, this syntax

    [Effect] = -1 Or 0

    would need to be

    [Effect] = -1 Or [Effect] = 0

    That said, since those are the only possible values, why have it in the criteria at all? You can't cancel the after update event, so that code isn't really doing anything. If you want to stop the update, you'd use the before update event.

    FYI, I'm going to change your thread title to something more meaningful to future users than "racking my brain, please help".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Null Value for calculation stops whole result
    By Chelcone in forum Queries
    Replies: 11
    Last Post: 04-16-2012, 03:49 AM
  2. Help with Null value handling
    By Thumbs in forum Programming
    Replies: 3
    Last Post: 03-05-2012, 10:55 AM
  3. Replies: 1
    Last Post: 02-23-2012, 02:27 PM
  4. dlookup put the result in form
    By hamish mather in forum Programming
    Replies: 3
    Last Post: 01-05-2012, 03:10 PM
  5. If Query result Is Null...MsgBox..Okl
    By Bruce in forum Forms
    Replies: 28
    Last Post: 03-10-2010, 10:57 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