Why are my Msxbox's not appearing?

[Dave]

Here is my code

On Error GoTo Err_cmdFilter_Click
On Error Resume Next
Dim stDocName As String

DoCmd.ShowAllRecords
If Forms!frmInmates.txtStartDate = "" Or Forms!frmInmates.txtEndDate =
"" Then
MsgBox "You Must enter Dates in the Date Fields"

Else
Me.Filter = "SentenceStartDate >= #" & txtStartDate & "# And
ReimburseEndDate <= #" & txtEndDate & "#"
End If
Me.FilterOn = True
'SendKeys "{F2}"
txtStartDate.SetFocus
Exit_cmdFilter_Click:
Exit Sub

Err_cmdFilter_Click:
MsgBox Err.Description
Resume Exit_cmdFilter_Click


txtStartDate and txtEndDate are date fields and if they are empty I want the
msgbox to pop up but as I have coded this, it does not.

What have I done wrong?

Any help here will be appreciated.

Thanks in advance

Dave

 

[Beetle]

You're trapping for a zero length string but your date fields may be actually
be Null, so you need to trap for that also.

If IsNull (Forms!frmInmates.txtStartDate) Or Forms!frmInmates.txtStartDate
= "" Or IsNull (Forms!frmInmates.txtEndDate) Or Forms!frmInmates.txtEndDate
=
"" Then

BTW - If the code is contained within the form you are referring to, you can
use the Me reference to simplify things a little

Me.txtStartDate , Me.txtEndDate

HTH

 

[Rob Parker]

Hi Dave,

You are testing whether the controls contain a zero-length string; you
should be testing whether they are null. Try:
...
If IsNull(Forms!frmInmates.txtStartDate) Or
IsNull(Forms!frmInmates.txtEndDate) Then
...

HTH,

Rob

 

[Dave]

Thanks guys - that worked.
However it brought up another problem

I also have a button to show all records

with only this code:

DoCmd.ShowAllRecords
Me.txtStartDate = ""
Me.txtEndDate = ""

If I hit this button to re-display the complete list
then hit the button with the filter code the msgbox does not come up even if
the field is empty

I have to close and reopen the form

dave

 

[Linq Adams via AccessMonster.com]

That's because you're now checking for Null and the fields aren't Null,
you've assigned a zero length string to them!

What exactly is the point in showing all the records then removing data from
txtStartDate and txtEndDate in the first record? Just to test the code?

 

[Rob Parker]

Well, maybe the two controls (txtStartDate and txtEndDate) are not bound
controls; they could be unbound controls used to set parametere for a
filter. If that's the case then Dave could use the longer alternative code
posted in another reply (which checked for both nulls and zero-length
strings) or he could set the unbound controls to null, rather than a
zero-length string via code such as:
Me.txtStartDate = Null

Rob

 

[BruceM]

An alternative to checking separately for both null and zero-length is to
use the Nz function:
If Nz(Me.txtStartDate) = "" Then
etc.
End If

Note that the Value If Null defaults to a zero-length string if it is not
specified; however, in a query it needs to be specified:
IIf(Nz([StartDate],"") = "",etc.,etc.)

Another option is to add a zero-length string to the value, and check
whether the result is still a zero-length string:
If Me.txtStartDate & "" = "" Then
etc.
End If

 

[Dave]

The point of striping the data out of the two fields after showing all
records is so that a new date range can be added

Dave

 

[Dave]

Rob,
that was the ticket.
thanks

Well, maybe the two controls (txtStartDate and txtEndDate) are not bound
controls; they could be unbound controls used to set parametere for a
filter. If that's the case then Dave could use the longer alternative
code posted in another reply (which checked for both nulls and zero-length
strings) or he could set the unbound controls to null, rather than a
zero-length string via code such as:
Me.txtStartDate = Null

Rob