Help with code please

You need to test the table or at least all the records in the record source of
the form.

You can use DCount function if the forms record source is a table or a query.
Use the DCount to count the number of records with a date within the last 30
days.

IF DCOUNT("*","SomeTableName","Monthly_Date Between Date()-30) and Date()") =
0 THEN
MsgBox "test overdue,please schedule asap", vbCritical, "Monthly Checks"
End IF

I used the range in case you should accidentally get a record with a date in
the future. If you do enter records for future dates then you can change the
criteria clause to
"Monthly_Date Date()-30)"
if you want to avoid the message if the dates are in the future.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

steve goodrich wrote:
John,

I'm ignoring the check boxes for this exercise.
There is one date field on my form that is bound to a table.
I want the message to appear when I open my form and every date is more than
30 days old (i.e. if the last date entered is less than 30 days old then the
message does not appear).
I have the code set in the forms 'on open' event

Steve

I
"John Spencer" wrote in message
...
First, WHERE are you using the code. Presumably on a form, but in what
event are you using it (a control's after update event, the form's Current
event, ...)?

And what do you want to test. Do you want to test if there is no field
with a check, a specific field without a check, etc.

Right now, you are checking whether or not the value of the control on the
form is more than 30 days ago. Does the form have the date and all five
checkboxes on it?


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

steve goodrich wrote:
Thanks for all the advice - I now have a query set up that will give me
what I want - I would also like to amend the code below, mainly as an
exercise as I am trying to learn a little VBA.
The code below always prompts me with the message although my last entry
is less than 30 days old - It's obviously checking all the dates and all
but the last entry is older than 30 days.

How do I amend the code so that all dates must be 30 days old before
getting the message
Many thanks
Steve

If Me.Monthly_Date = Date - 30 Then
MsgBox "test overdue,please schedule asap", vbCritical, "Monthly Checks"
End If


"steve goodrich" wrote in message
...
I have a form with six fields bound to a table.
one field is a date field and the other 5 are check boxes.
These boxes need to be checked once per month (not the same date)
I would like to be prompted with a message box if any of the check boxes
haven't been checked for a month or more

I put this code in the 'on open' event of my form which prompts me if
the last date entered is over 30 days old.

If Me.Monthly_Date = Date - 30 Then
MsgBox "test overdue,please schedule asap", vbCritical, "Monthly Checks"
End If

How do I enter the code if I want the message to appear based on the
check box not being ticked and the date being 30 days old.

I.e. If check box 1 was ticked on 1 July then with the code I've got I
wouldn't be prompted again until 1 Aug but check boxed 2 to5 would be
more than 30 days old

Any help would be much appreciated

Steve

John,
I copied & pasted the text and placed my table name in the code, but got the
following error
Run time error 3075, extra ) in qery expression 'Monthly_date between
date()-30)and date()'.

Steve

If DCount("*", "tblMonthly", "Monthly_Date Between Date()-30) and Date()") =
0 Then
MsgBox "test overdue,please schedule asap", vbCritical, "Monthly Checks"
End If


"John Spencer" wrote in message
...
You need to test the table or at least all the records in the record
source of the form.

You can use DCount function if the forms record source is a table or a
query. Use the DCount to count the number of records with a date within
the last 30 days.

IF DCOUNT("*","SomeTableName","Monthly_Date Between Date()-30) and
Date()") = 0 THEN
MsgBox "test overdue,please schedule asap", vbCritical, "Monthly Checks"
End IF

I used the range in case you should accidentally get a record with a date
in the future. If you do enter records for future dates then you can
change the criteria clause to
"Monthly_Date Date()-30)"
if you want to avoid the message if the dates are in the future.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

steve goodrich wrote:
John,

I'm ignoring the check boxes for this exercise.
There is one date field on my form that is bound to a table.
I want the message to appear when I open my form and every date is more
than 30 days old (i.e. if the last date entered is less than 30 days old
then the message does not appear).
I have the code set in the forms 'on open' event

Steve

I
"John Spencer" wrote in message
...
First, WHERE are you using the code. Presumably on a form, but in what
event are you using it (a control's after update event, the form's
Current event, ...)?

And what do you want to test. Do you want to test if there is no field
with a check, a specific field without a check, etc.

Right now, you are checking whether or not the value of the control on
the form is more than 30 days ago. Does the form have the date and all
five checkboxes on it?


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

steve goodrich wrote:
Thanks for all the advice - I now have a query set up that will give me
what I want - I would also like to amend the code below, mainly as an
exercise as I am trying to learn a little VBA.
The code below always prompts me with the message although my last
entry is less than 30 days old - It's obviously checking all the dates
and all but the last entry is older than 30 days.

How do I amend the code so that all dates must be 30 days old before
getting the message
Many thanks
Steve

If Me.Monthly_Date = Date - 30 Then
MsgBox "test overdue,please schedule asap", vbCritical, "Monthly
Checks"
End If


"steve goodrich" wrote in message
...
I have a form with six fields bound to a table.
one field is a date field and the other 5 are check boxes.
These boxes need to be checked once per month (not the same date)
I would like to be prompted with a message box if any of the check
boxes haven't been checked for a month or more

I put this code in the 'on open' event of my form which prompts me if
the last date entered is over 30 days old.

If Me.Monthly_Date = Date - 30 Then
MsgBox "test overdue,please schedule asap", vbCritical, "Monthly
Checks"
End If

How do I enter the code if I want the message to appear based on the
check box not being ticked and the date being 30 days old.

I.e. If check box 1 was ticked on 1 July then with the code I've got I
wouldn't be prompted again until 1 Aug but check boxed 2 to5 would be
more than 30 days old

Any help would be much appreciated

Steve

On Fri, 31 Jul 2009 18:21:58 +0100, "steve goodrich"
wrote:

John,
I copied & pasted the text and placed my table name in the code, but got the
following error
Run time error 3075, extra ) in qery expression 'Monthly_date between
date()-30)and date()'.

Steve

If DCount("*", "tblMonthly", "Monthly_Date Between Date()-30) and Date()") =
0 Then
MsgBox "test overdue,please schedule asap", vbCritical, "Monthly Checks"
End If

Just a typo. Remove the ) after 30.
--

John W. Vinson [MVP]

John,
I removed the offending )

I am still getting the message displayed if any one of the dates is more
than 30 days old!
I have deleted all but 2 records and carried out this test:

I entered 1 June 09 for both records and the message pops up because they
are both more than 30 days old.
I entered 20 July 09 for both records and the message doesn't pop up because
they are both less than 30 days old.
I changed one of the dates to 20 July 09 and the other date to 1 June 09 and
the message still pops up.

If any date is less than 30 days old I don't want the message to pop up
What am I doing wrong?

Steve


"John W. Vinson" wrote in message
...
On Fri, 31 Jul 2009 18:21:58 +0100, "steve goodrich"
wrote:

John,
I copied & pasted the text and placed my table name in the code, but got
the
following error
Run time error 3075, extra ) in qery expression 'Monthly_date between
date()-30)and date()'.

Steve

If DCount("*", "tblMonthly", "Monthly_Date Between Date()-30) and Date()")
=
0 Then
MsgBox "test overdue,please schedule asap", vbCritical, "Monthly
Checks"
End If

Just a typo. Remove the ) after 30.
--

John W. Vinson [MVP]

IF DCOUNT("*","SomeTableName", _
"Monthly_Date Between Date()-30 and Date()") = 0 THEN

MsgBox "test overdue,please schedule asap", vbCritical, "Monthly Checks"
End IF

That should work if you have any record in the database that has a date
that is less than 30 days ago. So I am puzzled on why you got the
results you did.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


steve goodrich wrote:
John,
I removed the offending )

I am still getting the message displayed if any one of the dates is more
than 30 days old!
I have deleted all but 2 records and carried out this test:

I entered 1 June 09 for both records and the message pops up because they
are both more than 30 days old.
I entered 20 July 09 for both records and the message doesn't pop up because
they are both less than 30 days old.
I changed one of the dates to 20 July 09 and the other date to 1 June 09 and
the message still pops up.

If any date is less than 30 days old I don't want the message to pop up
What am I doing wrong?

Steve

I don't think I have explained it very well
It does work if there dates over 30 days old.
If there are many dates that are 30 days old and only one date that is not
30 days old then I don't want the message to appear
Steve


"John Spencer" wrote in message
...
IF DCOUNT("*","SomeTableName", _
"Monthly_Date Between Date()-30 and Date()") = 0 THEN

MsgBox "test overdue,please schedule asap", vbCritical, "Monthly Checks"
End IF

That should work if you have any record in the database that has a date
that is less than 30 days ago. So I am puzzled on why you got the results
you did.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


steve goodrich wrote:
John,
I removed the offending )

I am still getting the message displayed if any one of the dates is more
than 30 days old!
I have deleted all but 2 records and carried out this test:

I entered 1 June 09 for both records and the message pops up because they
are both more than 30 days old.
I entered 20 July 09 for both records and the message doesn't pop up
because they are both less than 30 days old.
I changed one of the dates to 20 July 09 and the other date to 1 June 09
and the message still pops up.

If any date is less than 30 days old I don't want the message to pop up
What am I doing wrong?

Steve

Somehow I am missing something you are doing. The message should only
be triggered if there are NO records in the database with a date later
than 30 days ago. So for today if there is a record between July 3 and
August 1, 2009 the message would not be displayed.

Try entering the expression in the immediate window AND see what it returns.
?DCOUNT("*","SomeTableName","Monthly_Date Between Date()-30 and Date()")

The try this expression and see what it returns.
?DCOUNT("*","SomeTableName","Monthly_Date Date()-30")

This one should return the number of records in the table
?DCount("*","SomeTableName")

This one should return the number of records in the database where
Monthly_Date is not null
?DCount("Monthly_Date","SomeTableName")

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


steve goodrich wrote:
I don't think I have explained it very well
It does work if there dates over 30 days old.
If there are many dates that are 30 days old and only one date that is not
30 days old then I don't want the message to appear
Steve


"John Spencer" wrote in message
...
IF DCOUNT("*","SomeTableName", _
"Monthly_Date Between Date()-30 and Date()") = 0 THEN

MsgBox "test overdue,please schedule asap", vbCritical, "Monthly Checks"
End IF

That should work if you have any record in the database that has a date
that is less than 30 days ago. So I am puzzled on why you got the results
you did.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


steve goodrich wrote:
John,
I removed the offending )

I am still getting the message displayed if any one of the dates is more
than 30 days old!
I have deleted all but 2 records and carried out this test:

I entered 1 June 09 for both records and the message pops up because they
are both more than 30 days old.
I entered 20 July 09 for both records and the message doesn't pop up
because they are both less than 30 days old.
I changed one of the dates to 20 July 09 and the other date to 1 June 09
and the message still pops up.

If any date is less than 30 days old I don't want the message to pop up
What am I doing wrong?

Steve

Got that wrong.

The message will be triggered as long as there is no record between a
date 30 days ago and today.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


John Spencer wrote:
Somehow I am missing something you are doing. The message should only
be triggered if there are NO records in the database with a date later
than 30 days ago. So for today if there is a record between July 3 and
August 1, 2009 the message would not be displayed.

Try entering the expression in the immediate window AND see what it
returns.
?DCOUNT("*","SomeTableName","Monthly_Date Between Date()-30 and Date()")

The try this expression and see what it returns.
?DCOUNT("*","SomeTableName","Monthly_Date Date()-30")

This one should return the number of records in the table
?DCount("*","SomeTableName")

This one should return the number of records in the database where
Monthly_Date is not null
?DCount("Monthly_Date","SomeTableName")

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


steve goodrich wrote:
I don't think I have explained it very well
It does work if there dates over 30 days old.
If there are many dates that are 30 days old and only one date that is
not 30 days old then I don't want the message to appear
Steve


"John Spencer" wrote in message
...
IF DCOUNT("*","SomeTableName", _
"Monthly_Date Between Date()-30 and Date()") = 0 THEN

MsgBox "test overdue,please schedule asap", vbCritical, "Monthly
Checks"
End IF

That should work if you have any record in the database that has a
date that is less than 30 days ago. So I am puzzled on why you got
the results you did.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===

Sorry John,

I am just starting to learn VBA , what is the immediate window? and what
should I do once I enter the text
Steve

"John Spencer" wrote in message
...
Got that wrong.

The message will be triggered as long as there is no record between a date
30 days ago and today.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


John Spencer wrote:
Somehow I am missing something you are doing. The message should only be
triggered if there are NO records in the database with a date later than
30 days ago. So for today if there is a record between July 3 and August
1, 2009 the message would not be displayed.

Try entering the expression in the immediate window AND see what it
returns.
?DCOUNT("*","SomeTableName","Monthly_Date Between Date()-30 and Date()")

The try this expression and see what it returns.
?DCOUNT("*","SomeTableName","Monthly_Date Date()-30")

This one should return the number of records in the table
?DCount("*","SomeTableName")

This one should return the number of records in the database where
Monthly_Date is not null
?DCount("Monthly_Date","SomeTableName")

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


steve goodrich wrote:
I don't think I have explained it very well
It does work if there dates over 30 days old.
If there are many dates that are 30 days old and only one date that is
not 30 days old then I don't want the message to appear
Steve


"John Spencer" wrote in message
...
IF DCOUNT("*","SomeTableName", _
"Monthly_Date Between Date()-30 and Date()") = 0 THEN

MsgBox "test overdue,please schedule asap", vbCritical, "Monthly
Checks"
End IF

That should work if you have any record in the database that has a date
that is less than 30 days ago. So I am puzzled on why you got the
results you did.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===

In database design view, Type control+g.

That should open up Access VBA and the immediate window. If for some reason
you don't see the VBA window, select it from the view menu.

Then type in one of the lines in the Immediate window including the question
mark and press return
The computer should return a number on the next line that corresponds to the
number of records that met the criteria

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

steve goodrich wrote:
Sorry John,

I am just starting to learn VBA , what is the immediate window? and what
should I do once I enter the text
Steve

"John Spencer" wrote in message
...
Got that wrong.

The message will be triggered as long as there is no record between a date
30 days ago and today.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


John Spencer wrote:
Somehow I am missing something you are doing. The message should only be
triggered if there are NO records in the database with a date later than
30 days ago. So for today if there is a record between July 3 and August
1, 2009 the message would not be displayed.

Try entering the expression in the immediate window AND see what it
returns.
?DCOUNT("*","SomeTableName","Monthly_Date Between Date()-30 and Date()")

The try this expression and see what it returns.
?DCOUNT("*","SomeTableName","Monthly_Date Date()-30")

This one should return the number of records in the table
?DCount("*","SomeTableName")

This one should return the number of records in the database where
Monthly_Date is not null
?DCount("Monthly_Date","SomeTableName")

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


steve goodrich wrote:
I don't think I have explained it very well
It does work if there dates over 30 days old.
If there are many dates that are 30 days old and only one date that is
not 30 days old then I don't want the message to appear
Steve


"John Spencer" wrote in message
...
IF DCOUNT("*","SomeTableName", _
"Monthly_Date Between Date()-30 and Date()") = 0 THEN

MsgBox "test overdue,please schedule asap", vbCritical, "Monthly
Checks"
End IF

That should work if you have any record in the database that has a date
that is less than 30 days ago. So I am puzzled on why you got the
results you did.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===