|
|
Greetings all,
I have a quick question...
I have a simple pop up form designed just to count days with the following
text boxes:
txtStartDays set to short date
txtEndDays set to short date
txtDays set to general number with the control source
=Nz(DateDiff("d",[txtStartDays],[txtEndDays]),0) which works fine counting
the days.
I am trying the ensure that the start date is not greater than the end date
with the following code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.txtStartDays > Me.txtEndDays Then
MsgBox "The start date needs to be before the end date."
Cancel = True
Me.txtStartDays.SetFocus
Exit Sub
End If
End Sub
I does not seem to work...any thoughts much appreciated.
Thanks in advance,
george
|
|
Try
If DateDiff("d",[txtStartDays],[txtEndDays]),0) < 0 then
MsgBox "The start date needs to be before the end date."
Cancel = True
Me.txtStartDays.SetFocus
Exit Sub
End If
george 16-17 wrote:
[Quoted Text] > Greetings all,
>
> I have a quick question...
>
> I have a simple pop up form designed just to count days with the
> following text boxes:
>
> txtStartDays set to short date
> txtEndDays set to short date
> txtDays set to general number with the control source
> =Nz(DateDiff("d",[txtStartDays],[txtEndDays]),0) which works fine
> counting the days.
>
> I am trying the ensure that the start date is not greater than the
> end date with the following code:
> Private Sub Form_BeforeUpdate(Cancel As Integer)
>
> If Me.txtStartDays > Me.txtEndDays Then
> MsgBox "The start date needs to be before the end date."
> Cancel = True
> Me.txtStartDays.SetFocus
> Exit Sub
> End If
>
> End Sub
>
>
> I does not seem to work...any thoughts much appreciated.
> Thanks in advance,
> george
|
|
On Mon, 1 Dec 2008 12:39:01 -0800, george 16-17
<george1617[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >Greetings all,
>
>I have a quick question...
>
>I have a simple pop up form designed just to count days with the following
>text boxes:
>
>txtStartDays set to short date
>txtEndDays set to short date
>txtDays set to general number with the control source
>=Nz(DateDiff("d",[txtStartDays],[txtEndDays]),0) which works fine counting
>the days.
>
>I am trying the ensure that the start date is not greater than the end date
>with the following code:
>Private Sub Form_BeforeUpdate(Cancel As Integer)
>
> If Me.txtStartDays > Me.txtEndDays Then
> MsgBox "The start date needs to be before the end date."
> Cancel = True
> Me.txtStartDays.SetFocus
> Exit Sub
> End If
>
>End Sub
>
>
>I does not seem to work...any thoughts much appreciated.
>Thanks in advance,
>george
What "doesn't work"? If either txtStartDays or txtEndDays is NULL you won't
get a value in txtDays nor will the form's BeforeUpdate event find a problem
(since NULL is not less than or greater than anything, your IF statement will
never be True). Maybe you need default values in the textboxes, or to check
for NULL in the code.
--
John W. Vinson [MVP]
|
|
Thanks Mike and John for helping.
Mike,
The code you offered does not work either (thanks for trying).
John,
It's the validation code that does not work. If the user puts in a greater
start date than end date, I get a negative result in txtDays. I am trying to
write some logic to prevent that.
Thanks again,
george
"John W. Vinson" wrote:
[Quoted Text] > On Mon, 1 Dec 2008 12:39:01 -0800, george 16-17
> <george1617[ at ]discussions.microsoft.com> wrote:
>
> >Greetings all,
> >
> >I have a quick question...
> >
> >I have a simple pop up form designed just to count days with the following
> >text boxes:
> >
> >txtStartDays set to short date
> >txtEndDays set to short date
> >txtDays set to general number with the control source
> >=Nz(DateDiff("d",[txtStartDays],[txtEndDays]),0) which works fine counting
> >the days.
> >
> >I am trying the ensure that the start date is not greater than the end date
> >with the following code:
> >Private Sub Form_BeforeUpdate(Cancel As Integer)
> >
> > If Me.txtStartDays > Me.txtEndDays Then
> > MsgBox "The start date needs to be before the end date."
> > Cancel = True
> > Me.txtStartDays.SetFocus
> > Exit Sub
> > End If
> >
> >End Sub
> >
> >
> >I does not seem to work...any thoughts much appreciated.
> >Thanks in advance,
> >george
>
> What "doesn't work"? If either txtStartDays or txtEndDays is NULL you won't
> get a value in txtDays nor will the form's BeforeUpdate event find a problem
> (since NULL is not less than or greater than anything, your IF statement will
> never be True). Maybe you need default values in the textboxes, or to check
> for NULL in the code.
> --
>
> John W. Vinson [MVP]
>
|
|
george 16-17 wrote:
[Quoted Text] > Thanks Mike and John for helping.
>
> Mike,
> The code you offered does not work either (thanks for trying).
>
If the dates are valid and the datediff function is entered properly then
try revesaing the dates.
|
|
On Mon, 1 Dec 2008 19:00:24 -0800, "Mike Painter" <mddotpainter[ at ]sbcglobal.net>
wrote:
[Quoted Text] >george 16-17 wrote:
>> Thanks Mike and John for helping.
>>
>> Mike,
>> The code you offered does not work either (thanks for trying).
>>
> If the dates are valid and the datediff function is entered properly then
>try revesaing the dates.
>
It may be interpreting the values in the textboxes as strings. Try
If CDate(Me.txtStartDays) > CDate(Me.txtEndDays) Then
--
John W. Vinson [MVP]
|
|
Hi John and Mike,
Again, neither appreciated suggestion produced the message box with a
greater start date. I even tried the following code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.txtDays < 0 Then
MsgBox "The start date needs to be before the end date."
Cancel = True
Me.txtStartDays.SetFocus
Exit Sub
End If
End Sub
Should I be using the form's beforeupdate property? I did not state this
previously, but txtStartdays and txtEndDays are unbound, if that makes a
diffeence.
Thanks again and I appreciate your persistance,
george
|
|
On Tue, 2 Dec 2008 05:13:00 -0800, george 16-17
<george1617[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >Hi John and Mike,
>
>Again, neither appreciated suggestion produced the message box with a
>greater start date. I even tried the following code:
>
>Private Sub Form_BeforeUpdate(Cancel As Integer)
> If Me.txtDays < 0 Then
> MsgBox "The start date needs to be before the end date."
> Cancel = True
> Me.txtStartDays.SetFocus
> Exit Sub
> End If
>
>End Sub
>
>Should I be using the form's beforeupdate property? I did not state this
>previously, but txtStartdays and txtEndDays are unbound, if that makes a
>diffeence.
Is the *FORM* unbound, i.e. just to provide search criteria? If so, the
BeforeUpdate event will never fire, because there's nothing to update. I'd put
a command button on the form to open the report (or run the query or open the
other form or whatever), and put the check in that button's Click event.
--
John W. Vinson [MVP]
|
|
John,
Thanks again. Yes, the form is unbound...no wonder why it is not working. I
am too much of a noob to figure that out.
I added the command button as suggested with a little more coding, and it is
working perfectly.
Much appreciated,
george
|
|
|