Handling Null DLookup result

[j6sander]

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.

[pbaldy]

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".