Hi All, hope someone can assist me?

I have a form where the user enters the date that a sale is DUE on

Later he will review records and will view this record.

I want to check if the DUE date previously entered is within 14 days either
side of today's date and will then pop up an alert.

I have so far created the following in the Current Sub of the form:

Private Sub Form_Current()
'Check due date & alert!
If Due = Date Then
Beep
MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
End If
End Sub

Can anyone tell me how I can expand this?

I thought of converting both DATE and Due to numbers, (DueNum & DateNum),
subtracting 14 from DateNum to get DateNumStart and Adding 14 to get
DateNumEnd then checking if DueNum is BETWEEN DateNumStart AND DateNumEnd

How could I achieve this?

All help gratefully acknowledged.
--
Cheers
Chas

***************
* Spectrum is Green *
***************

Hi Chas - have you just tried subtracting one date from the other? Dates are
held internally as numbers so you can id - today is the number 38775 and 2nd
March is 38778 etc. Hope I'm not teaching you to suck eggs. Yrs - Dika

"Chas Large" wrote:

> Hi All, hope someone can assist me?
>
> I have a form where the user enters the date that a sale is DUE on
>
> Later he will review records and will view this record.
>
> I want to check if the DUE date previously entered is within 14 days either
> side of today's date and will then pop up an alert.
>
> I have so far created the following in the Current Sub of the form:
>
> Private Sub Form_Current()
> 'Check due date & alert!
> If Due = Date Then
> Beep
> MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
> End If
> End Sub
>
> Can anyone tell me how I can expand this?
>
> I thought of converting both DATE and Due to numbers, (DueNum & DateNum),
> subtracting 14 from DateNum to get DateNumStart and Adding 14 to get
> DateNumEnd then checking if DueNum is BETWEEN DateNumStart AND DateNumEnd
>
> How could I achieve this?
>
> All help gratefully acknowledged.
> --
> Cheers
> Chas
>
> ***************
> * Spectrum is Green *
> ***************

Hi, Kernow Girl,

I know this is possible and would like to try it but I don't quite know how.
The dates in the table are in the UK format so today is 27/02/2006.

How would I change this to the number format and then test the due date
stored within 14 days either side of it?
--
Cheers
Chas

***************
* Spectrum is Green *
***************


"Kernow Girl" wrote:

> Hi Chas - have you just tried subtracting one date from the other? Dates are
> held internally as numbers so you can id - today is the number 38775 and 2nd
> March is 38778 etc. Hope I'm not teaching you to suck eggs. Yrs - Dika
>
> "Chas Large" wrote:
>
> > Hi All, hope someone can assist me?
> >
> > I have a form where the user enters the date that a sale is DUE on
> >
> > Later he will review records and will view this record.
> >
> > I want to check if the DUE date previously entered is within 14 days either
> > side of today's date and will then pop up an alert.
> >
> > I have so far created the following in the Current Sub of the form:
> >
> > Private Sub Form_Current()
> > 'Check due date & alert!
> > If Due = Date Then
> > Beep
> > MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
> > End If
> > End Sub
> >
> > Can anyone tell me how I can expand this?
> >
> > I thought of converting both DATE and Due to numbers, (DueNum & DateNum),
> > subtracting 14 from DateNum to get DateNumStart and Adding 14 to get
> > DateNumEnd then checking if DueNum is BETWEEN DateNumStart AND DateNumEnd
> >
> > How could I achieve this?
> >
> > All help gratefully acknowledged.
> > --
> > Cheers
> > Chas
> >
> > ***************
> > * Spectrum is Green *
> > ***************

Hi Chas - it is easier than is seems! It makes no difference how your dates
are formatted, the underlying number remains the same. You cound be formatted
for the date to display as 27th Feb or 27/02/2006 or 2/27/2006 - the number
is still 38775.
Obviously it depends on which you subtract from which (does that sounds
Irish?) and what you are doing. But anywhere you can compare
I'm afraid I do very little coding, so can't show you exactly how, but have
added the logic below. I normally add a field to my query to hold the
difference, then use that field to test on.

Private Sub Form_Current()
'Check due date & alert!
if (due-date >=14)
Beep
MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
End If
End Sub

Does that help?

"Chas Large" wrote:

> Hi, Kernow Girl,
>
> I know this is possible and would like to try it but I don't quite know how.
> The dates in the table are in the UK format so today is 27/02/2006.
>
> How would I change this to the number format and then test the due date
> stored within 14 days either side of it?
> --
> Cheers
> Chas
>
> ***************
> * Spectrum is Green *
> ***************
>
>
> "Kernow Girl" wrote:
>
> > Hi Chas - have you just tried subtracting one date from the other? Dates are
> > held internally as numbers so you can id - today is the number 38775 and 2nd
> > March is 38778 etc. Hope I'm not teaching you to suck eggs. Yrs - Dika
> >
> > "Chas Large" wrote:
> >
> > > Hi All, hope someone can assist me?
> > >
> > > I have a form where the user enters the date that a sale is DUE on
> > >
> > > Later he will review records and will view this record.
> > >
> > > I want to check if the DUE date previously entered is within 14 days either
> > > side of today's date and will then pop up an alert.
> > >
> > > I have so far created the following in the Current Sub of the form:
> > >
> > > Private Sub Form_Current()
> > > 'Check due date & alert!
> > > If Due = Date Then
> > > Beep
> > > MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
> > > End If
> > > End Sub
> > >
> > > Can anyone tell me how I can expand this?
> > >
> > > I thought of converting both DATE and Due to numbers, (DueNum & DateNum),
> > > subtracting 14 from DateNum to get DateNumStart and Adding 14 to get
> > > DateNumEnd then checking if DueNum is BETWEEN DateNumStart AND DateNumEnd
> > >
> > > How could I achieve this?
> > >
> > > All help gratefully acknowledged.
> > > --
> > > Cheers
> > > Chas
> > >
> > > ***************
> > > * Spectrum is Green *
> > > ***************

Dear Kernow Girl,

Many thanks for the reply. Of course I see what you are saying and it works,
sort of. I did not think of the other way of looking at it, check the
difference rather than the rang. Good thinking. I used your code as is:

Private Sub Form_Current()
'Check due date & alert!
If (Due - Date >= 14) Then
Beep
MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
End If
End Sub

Unfortunately the formatting does appear to be important as the modified
code now alerts me to any date beginning 01 eg 01/05/2006 or 01/09/2006. I
think this is using the US format to calculate the number. However 02/08/2006
and 22/06/2006 also trigger the alert!

Any ideas?
--
Cheers
Chas

***************
* Spectrum is Green *
***************


"Kernow Girl" wrote:

> Hi Chas - it is easier than is seems! It makes no difference how your dates
> are formatted, the underlying number remains the same. You cound be formatted
> for the date to display as 27th Feb or 27/02/2006 or 2/27/2006 - the number
> is still 38775.
> Obviously it depends on which you subtract from which (does that sounds
> Irish?) and what you are doing. But anywhere you can compare
> I'm afraid I do very little coding, so can't show you exactly how, but have
> added the logic below. I normally add a field to my query to hold the
> difference, then use that field to test on.
>
> Private Sub Form_Current()
> 'Check due date & alert!
> if (due-date >=14)
> Beep
> MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
> End If
> End Sub
>
> Does that help?
>
> "Chas Large" wrote:
>
> > Hi, Kernow Girl,
> >
> > I know this is possible and would like to try it but I don't quite know how.
> > The dates in the table are in the UK format so today is 27/02/2006.
> >
> > How would I change this to the number format and then test the due date
> > stored within 14 days either side of it?
> > --
> > Cheers
> > Chas
> >
> > ***************
> > * Spectrum is Green *
> > ***************
> >
> >
> > "Kernow Girl" wrote:
> >
> > > Hi Chas - have you just tried subtracting one date from the other? Dates are
> > > held internally as numbers so you can id - today is the number 38775 and 2nd
> > > March is 38778 etc. Hope I'm not teaching you to suck eggs. Yrs - Dika
> > >
> > > "Chas Large" wrote:
> > >
> > > > Hi All, hope someone can assist me?
> > > >
> > > > I have a form where the user enters the date that a sale is DUE on
> > > >
> > > > Later he will review records and will view this record.
> > > >
> > > > I want to check if the DUE date previously entered is within 14 days either
> > > > side of today's date and will then pop up an alert.
> > > >
> > > > I have so far created the following in the Current Sub of the form:
> > > >
> > > > Private Sub Form_Current()
> > > > 'Check due date & alert!
> > > > If Due = Date Then
> > > > Beep
> > > > MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
> > > > End If
> > > > End Sub
> > > >
> > > > Can anyone tell me how I can expand this?
> > > >
> > > > I thought of converting both DATE and Due to numbers, (DueNum & DateNum),
> > > > subtracting 14 from DateNum to get DateNumStart and Adding 14 to get
> > > > DateNumEnd then checking if DueNum is BETWEEN DateNumStart AND DateNumEnd
> > > >
> > > > How could I achieve this?
> > > >
> > > > All help gratefully acknowledged.
> > > > --
> > > > Cheers
> > > > Chas
> > > >
> > > > ***************
> > > > * Spectrum is Green *
> > > > ***************

Hi Chas - what dates are you working with? I'll try them here. If you
subtract 1 from another you should get a number, not a date. Try your dates
in a simple query to see what I mean. Dika

"Chas Large" wrote:

> Dear Kernow Girl,
>
> Many thanks for the reply. Of course I see what you are saying and it works,
> sort of. I did not think of the other way of looking at it, check the
> difference rather than the rang. Good thinking. I used your code as is:
>
> Private Sub Form_Current()
> 'Check due date & alert!
> If (Due - Date >= 14) Then
> Beep
> MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
> End If
> End Sub
>
> Unfortunately the formatting does appear to be important as the modified
> code now alerts me to any date beginning 01 eg 01/05/2006 or 01/09/2006. I
> think this is using the US format to calculate the number. However 02/08/2006
> and 22/06/2006 also trigger the alert!
>
> Any ideas?
> --
> Cheers
> Chas
>
> ***************
> * Spectrum is Green *
> ***************
>
>
> "Kernow Girl" wrote:
>
> > Hi Chas - it is easier than is seems! It makes no difference how your dates
> > are formatted, the underlying number remains the same. You cound be formatted
> > for the date to display as 27th Feb or 27/02/2006 or 2/27/2006 - the number
> > is still 38775.
> > Obviously it depends on which you subtract from which (does that sounds
> > Irish?) and what you are doing. But anywhere you can compare
> > I'm afraid I do very little coding, so can't show you exactly how, but have
> > added the logic below. I normally add a field to my query to hold the
> > difference, then use that field to test on.
> >
> > Private Sub Form_Current()
> > 'Check due date & alert!
> > if (due-date >=14)
> > Beep
> > MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
> > End If
> > End Sub
> >
> > Does that help?
> >
> > "Chas Large" wrote:
> >
> > > Hi, Kernow Girl,
> > >
> > > I know this is possible and would like to try it but I don't quite know how.
> > > The dates in the table are in the UK format so today is 27/02/2006.
> > >
> > > How would I change this to the number format and then test the due date
> > > stored within 14 days either side of it?
> > > --
> > > Cheers
> > > Chas
> > >
> > > ***************
> > > * Spectrum is Green *
> > > ***************
> > >
> > >
> > > "Kernow Girl" wrote:
> > >
> > > > Hi Chas - have you just tried subtracting one date from the other? Dates are
> > > > held internally as numbers so you can id - today is the number 38775 and 2nd
> > > > March is 38778 etc. Hope I'm not teaching you to suck eggs. Yrs - Dika
> > > >
> > > > "Chas Large" wrote:
> > > >
> > > > > Hi All, hope someone can assist me?
> > > > >
> > > > > I have a form where the user enters the date that a sale is DUE on
> > > > >
> > > > > Later he will review records and will view this record.
> > > > >
> > > > > I want to check if the DUE date previously entered is within 14 days either
> > > > > side of today's date and will then pop up an alert.
> > > > >
> > > > > I have so far created the following in the Current Sub of the form:
> > > > >
> > > > > Private Sub Form_Current()
> > > > > 'Check due date & alert!
> > > > > If Due = Date Then
> > > > > Beep
> > > > > MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
> > > > > End If
> > > > > End Sub
> > > > >
> > > > > Can anyone tell me how I can expand this?
> > > > >
> > > > > I thought of converting both DATE and Due to numbers, (DueNum & DateNum),
> > > > > subtracting 14 from DateNum to get DateNumStart and Adding 14 to get
> > > > > DateNumEnd then checking if DueNum is BETWEEN DateNumStart AND DateNumEnd
> > > > >
> > > > > How could I achieve this?
> > > > >
> > > > > All help gratefully acknowledged.
> > > > > --
> > > > > Cheers
> > > > > Chas
> > > > >
> > > > > ***************
> > > > > * Spectrum is Green *
> > > > > ***************

Hi Kernow Girl or is it Dika?

The problem was that some dates were well in the past and so it was
generating minus values which could not be handled directly.

I got it working by using a variable to do the math:

Private Sub Form_Current()
On Error Resume Next
Dim CheckDate As Integer

'Check due date & alert!
CheckDate = Due - Date

'Check for all previous and +30 day date range
If CheckDate >= 0 And CheckDate <= 30 Or CheckDate < 0 Then
Beep
MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
End If
End Sub

The on error resume is used as sometimes no dat is in the Due field and this
generated errors, I might do a fix for this but it's working fine now.

Thanks for your help
--
Cheers
Chas

***************
* Spectrum is Green *
***************


"Kernow Girl" wrote:

> Hi Chas - what dates are you working with? I'll try them here. If you
> subtract 1 from another you should get a number, not a date. Try your dates
> in a simple query to see what I mean. Dika
>
> "Chas Large" wrote:
>
> > Dear Kernow Girl,
> >
> > Many thanks for the reply. Of course I see what you are saying and it works,
> > sort of. I did not think of the other way of looking at it, check the
> > difference rather than the rang. Good thinking. I used your code as is:
> >
> > Private Sub Form_Current()
> > 'Check due date & alert!
> > If (Due - Date >= 14) Then
> > Beep
> > MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
> > End If
> > End Sub
> >
> > Unfortunately the formatting does appear to be important as the modified
> > code now alerts me to any date beginning 01 eg 01/05/2006 or 01/09/2006. I
> > think this is using the US format to calculate the number. However 02/08/2006
> > and 22/06/2006 also trigger the alert!
> >
> > Any ideas?
> > --
> > Cheers
> > Chas
> >
> > ***************
> > * Spectrum is Green *
> > ***************
> >
> >
> > "Kernow Girl" wrote:
> >
> > > Hi Chas - it is easier than is seems! It makes no difference how your dates
> > > are formatted, the underlying number remains the same. You cound be formatted
> > > for the date to display as 27th Feb or 27/02/2006 or 2/27/2006 - the number
> > > is still 38775.
> > > Obviously it depends on which you subtract from which (does that sounds
> > > Irish?) and what you are doing. But anywhere you can compare
> > > I'm afraid I do very little coding, so can't show you exactly how, but have
> > > added the logic below. I normally add a field to my query to hold the
> > > difference, then use that field to test on.
> > >
> > > Private Sub Form_Current()
> > > 'Check due date & alert!
> > > if (due-date >=14)
> > > Beep
> > > MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
> > > End If
> > > End Sub
> > >
> > > Does that help?
> > >
> > > "Chas Large" wrote:
> > >
> > > > Hi, Kernow Girl,
> > > >
> > > > I know this is possible and would like to try it but I don't quite know how.
> > > > The dates in the table are in the UK format so today is 27/02/2006.
> > > >
> > > > How would I change this to the number format and then test the due date
> > > > stored within 14 days either side of it?
> > > > --
> > > > Cheers
> > > > Chas
> > > >
> > > > ***************
> > > > * Spectrum is Green *
> > > > ***************
> > > >
> > > >
> > > > "Kernow Girl" wrote:
> > > >
> > > > > Hi Chas - have you just tried subtracting one date from the other? Dates are
> > > > > held internally as numbers so you can id - today is the number 38775 and 2nd
> > > > > March is 38778 etc. Hope I'm not teaching you to suck eggs. Yrs - Dika
> > > > >
> > > > > "Chas Large" wrote:
> > > > >
> > > > > > Hi All, hope someone can assist me?
> > > > > >
> > > > > > I have a form where the user enters the date that a sale is DUE on
> > > > > >
> > > > > > Later he will review records and will view this record.
> > > > > >
> > > > > > I want to check if the DUE date previously entered is within 14 days either
> > > > > > side of today's date and will then pop up an alert.
> > > > > >
> > > > > > I have so far created the following in the Current Sub of the form:
> > > > > >
> > > > > > Private Sub Form_Current()
> > > > > > 'Check due date & alert!
> > > > > > If Due = Date Then
> > > > > > Beep
> > > > > > MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
> > > > > > End If
> > > > > > End Sub
> > > > > >
> > > > > > Can anyone tell me how I can expand this?
> > > > > >
> > > > > > I thought of converting both DATE and Due to numbers, (DueNum & DateNum),
> > > > > > subtracting 14 from DateNum to get DateNumStart and Adding 14 to get
> > > > > > DateNumEnd then checking if DueNum is BETWEEN DateNumStart AND DateNumEnd
> > > > > >
> > > > > > How could I achieve this?
> > > > > >
> > > > > > All help gratefully acknowledged.
> > > > > > --
> > > > > > Cheers
> > > > > > Chas
> > > > > >
> > > > > > ***************
> > > > > > * Spectrum is Green *
> > > > > > ***************

Hi Chas - glad you got there in the end. Name is Dika (blame my parents) -
alias is Kernow Girl. Glad I could help. Yrs - Dika (Kernow Girl)

"Chas Large" wrote:

> Hi Kernow Girl or is it Dika?
>
> The problem was that some dates were well in the past and so it was
> generating minus values which could not be handled directly.
>
> I got it working by using a variable to do the math:
>
> Private Sub Form_Current()
> On Error Resume Next
> Dim CheckDate As Integer
>
> 'Check due date & alert!
> CheckDate = Due - Date
>
> 'Check for all previous and +30 day date range
> If CheckDate >= 0 And CheckDate <= 30 Or CheckDate < 0 Then
> Beep
> MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
> End If
> End Sub
>
> The on error resume is used as sometimes no dat is in the Due field and this
> generated errors, I might do a fix for this but it's working fine now.
>
> Thanks for your help
> --
> Cheers
> Chas
>
> ***************
> * Spectrum is Green *
> ***************
>
>
> "Kernow Girl" wrote:
>
> > Hi Chas - what dates are you working with? I'll try them here. If you
> > subtract 1 from another you should get a number, not a date. Try your dates
> > in a simple query to see what I mean. Dika
> >
> > "Chas Large" wrote:
> >
> > > Dear Kernow Girl,
> > >
> > > Many thanks for the reply. Of course I see what you are saying and it works,
> > > sort of. I did not think of the other way of looking at it, check the
> > > difference rather than the rang. Good thinking. I used your code as is:
> > >
> > > Private Sub Form_Current()
> > > 'Check due date & alert!
> > > If (Due - Date >= 14) Then
> > > Beep
> > > MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
> > > End If
> > > End Sub
> > >
> > > Unfortunately the formatting does appear to be important as the modified
> > > code now alerts me to any date beginning 01 eg 01/05/2006 or 01/09/2006. I
> > > think this is using the US format to calculate the number. However 02/08/2006
> > > and 22/06/2006 also trigger the alert!
> > >
> > > Any ideas?
> > > --
> > > Cheers
> > > Chas
> > >
> > > ***************
> > > * Spectrum is Green *
> > > ***************
> > >
> > >
> > > "Kernow Girl" wrote:
> > >
> > > > Hi Chas - it is easier than is seems! It makes no difference how your dates
> > > > are formatted, the underlying number remains the same. You cound be formatted
> > > > for the date to display as 27th Feb or 27/02/2006 or 2/27/2006 - the number
> > > > is still 38775.
> > > > Obviously it depends on which you subtract from which (does that sounds
> > > > Irish?) and what you are doing. But anywhere you can compare
> > > > I'm afraid I do very little coding, so can't show you exactly how, but have
> > > > added the logic below. I normally add a field to my query to hold the
> > > > difference, then use that field to test on.
> > > >
> > > > Private Sub Form_Current()
> > > > 'Check due date & alert!
> > > > if (due-date >=14)
> > > > Beep
> > > > MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
> > > > End If
> > > > End Sub
> > > >
> > > > Does that help?
> > > >
> > > > "Chas Large" wrote:
> > > >
> > > > > Hi, Kernow Girl,
> > > > >
> > > > > I know this is possible and would like to try it but I don't quite know how.
> > > > > The dates in the table are in the UK format so today is 27/02/2006.
> > > > >
> > > > > How would I change this to the number format and then test the due date
> > > > > stored within 14 days either side of it?
> > > > > --
> > > > > Cheers
> > > > > Chas
> > > > >
> > > > > ***************
> > > > > * Spectrum is Green *
> > > > > ***************
> > > > >
> > > > >
> > > > > "Kernow Girl" wrote:
> > > > >
> > > > > > Hi Chas - have you just tried subtracting one date from the other? Dates are
> > > > > > held internally as numbers so you can id - today is the number 38775 and 2nd
> > > > > > March is 38778 etc. Hope I'm not teaching you to suck eggs. Yrs - Dika
> > > > > >
> > > > > > "Chas Large" wrote:
> > > > > >
> > > > > > > Hi All, hope someone can assist me?
> > > > > > >
> > > > > > > I have a form where the user enters the date that a sale is DUE on
> > > > > > >
> > > > > > > Later he will review records and will view this record.
> > > > > > >
> > > > > > > I want to check if the DUE date previously entered is within 14 days either
> > > > > > > side of today's date and will then pop up an alert.
> > > > > > >
> > > > > > > I have so far created the following in the Current Sub of the form:
> > > > > > >
> > > > > > > Private Sub Form_Current()
> > > > > > > 'Check due date & alert!
> > > > > > > If Due = Date Then
> > > > > > > Beep
> > > > > > > MsgBox "Due Date needs Revising", vbOKOnly, "ALERT"
> > > > > > > End If
> > > > > > > End Sub
> > > > > > >
> > > > > > > Can anyone tell me how I can expand this?
> > > > > > >
> > > > > > > I thought of converting both DATE and Due to numbers, (DueNum & DateNum),
> > > > > > > subtracting 14 from DateNum to get DateNumStart and Adding 14 to get
> > > > > > > DateNumEnd then checking if DueNum is BETWEEN DateNumStart AND DateNumEnd
> > > > > > >
> > > > > > > How could I achieve this?
> > > > > > >
> > > > > > > All help gratefully acknowledged.
> > > > > > > --
> > > > > > > Cheers
> > > > > > > Chas
> > > > > > >
> > > > > > > ***************
> > > > > > > * Spectrum is Green *
> > > > > > > ***************