testing a field in a table for Null within an IF statement

[Foe]

**anywhere you see the word BANANA, I really meant e-m-a-i-l. Apparently I can't post e-m-a-i-l addresses because I haven't posted 10 times** Lets disregard the fact that there isn't a single @ in this post (well, there is now!)

I have a form with two list boxes on it: listAllBANANA and listActiveBANANA
The listboxes are populated from tblBANANA. Active is defined by the tblBANANA field "ResponseDate" being Null.

Also on the form is a CloseBANANA command button. When pressed with no selection, it opens frmCloseBANANA to the first record. If a BANANA is selected in listActiveBANANA, the form opens to the selected record.

Now comes the tricky part: if a BANANA is selected in listAllBANANA, I first need to verify they have an Active BANANA selected, before opening the form. I've poked and tested and searched and cut and pasted my little fingers off with no joy. What I've deduced is that I'm either messing up the syntax of the DLookup or I'm incorrectly referencing the selected record. There's also the third option that I'm going about this in entirely the wrong way.

All other code below works. If I delete or comment out the line in red, I have no issues other than I may open up a blank form because I'm trying to close a BANANA that is already closed.

'If a BANANA is selected in Active BANANA or All BANANA, open Close BANANA to that record. If not, open form to most recent record
Private Sub cmdCloseBANANA_Click()
  If Not IsNull(Forms!frmGUI!listActiveBANANA) Then
    DoCmd.OpenForm "frmCloseBANANA", acNormal, , "[BANANAID] = " & Forms!frmGUI!listActiveBANANA.Column(0) & ""
  ElseIf Not IsNull(Forms!frmGUI!listAllBANANA) Then
    'Make sure selected BANANA is Active
    If [COLOR="Red"]IsNull(DLookup(ResponseDate, tblBANANA, "[BANANAID] = " & Forms!frmGUI!listAllBANANA.Column(0) & ""))[/COLOR] Then
        DoCmd.OpenForm "frmCloseBANANA", acNormal, , "[BANANAID] = " & Forms!frmGUI!listAllBANANA.Column(0) & ""
    Else: msgbox "The selected BANANA is already closed. Please choose an Active BANANA.", vbOKOnly + vbCritical, "Invalid Selection"
    End If
  Else: DoCmd.OpenForm "frmCloseBANANA"
  End If
End Sub

help?

 

[pbaldy]

Well, if banana is really email, that would imply that the field in the criteria is text. If so, the syntax is a little different:

http://www.mvps.org/access/general/gen0018.htm

 

[Foe]

OK, I now have:

If IsNull(DLookup("ResponseDate", "tblBANANA", "[BANANAID] = '" & Forms!frmGUI!listAllBANANA.Column(0) & "'")) Then

But, now the debugger is giving me a type mismatch

 

[Foe]

I understand your response better now. In the context of this database, email is synonymous with Trouble Ticket. I'm not actually dealing with email address here.

The ResponseDate field is a date field.
listAllEmails.Column(0) is a number: the EmailID from tblEmails

my intent is to tell the DLookup which record I'm referring to, and for it to tell me if the ResponseDate of that record is Null

Does that make sense?

 

[pbaldy]

If BananaID is numeric, your original code was right for the criteria, but you were missing quotes that I hadn't noticed. Try

If IsNull(DLookup("ResponseDate", "tblBANANA", "[BANANAID] = " & Forms!frmGUI!listAllBANANA.Column(0))) Then

 

[Foe]

so it was the quotes for the field and table prior to my criteria that was causing the issue?

I added those after I viewed the page you referred me to.

 

[pbaldy]

The field and table names also have to be in quotes, yes (unless they're variables).

 

[Foe]

Your code worked.

Can presume that the

& ""

was superfluous?

And would that make it superfluous everywhere else I'm using it?

Thanks for taking the time to help me with this. I wrote my first line of VBA less than 2 weeks ago and Google has been my only teacher.

 

[pbaldy]

Happy to help. Yes, that bit was superfluous. Hesitant to say "everywhere" , but likely yes. Doesn't hurt anything, just doesn't add anything either. The only place it has meaning that comes to mind is on a test like this one:

If Len(Whatever & "") = 0 Then

which is an often-used method to check for both Null and a zero length string (""). Adding the & "" bit there makes the test work for a Null.