Help with code please

John,
The code below worked ok - it returned two records which is all I have in my
database
This one should return the number of records in the table
?DCount("*","tblMonthly")

The other 3 examples displayed the following error
Run time error 64479, can't find the name Monthly_date you entered in your
expression

Steve



"John Spencer" wrote in message
...
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
'================================================= ===

On Mon, 3 Aug 2009 22:24:30 +0100, "steve goodrich"
wrote:

Run time error 64479, can't find the name Monthly_date you entered in your
expression

Does your table contain a field named Monthly_date (with the underscore)?

If it contains a field named "Monthly date", replace Monthly_date with
[Monthly date] including the square brackets.
--

John W. Vinson [MVP]

John,
Amended the code and the following results wre produced

?DCOUNT("*","tblmonthly","[Monthly Date] Date()-30")
1

?DCOUNT("*","tblmonthly","[Monthly Date] Between Date()-30 and Date()")
1
?DCount("[Monthly Date]","tblmonthly")
2

?DCount("*","tblmonthly")
2

There are only 2 records in my db - dates a
25 June 09 & 27 July 09

Steve

"John W. Vinson" wrote in message
...
On Mon, 3 Aug 2009 22:24:30 +0100, "steve goodrich"
wrote:

Run time error 64479, can't find the name Monthly_date you entered in your
expression

Does your table contain a field named Monthly_date (with the underscore)?

If it contains a field named "Monthly date", replace Monthly_date with
[Monthly date] including the square brackets.
--

John W. Vinson [MVP]

Good, then it seems as if you need to use the test with the correct field
name and hopefully you will get the results you are looking for.

IF DCOUNT("[Monthly Date]","tblmonthly","[Monthly Date] Between Date()-30 and
Date()") = 0 THEN
MsgBox "Test overdue,please schedule asap", vbCritical, "Monthly Checks"
End If

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

steve goodrich wrote:
John,
Amended the code and the following results wre produced

?DCOUNT("*","tblmonthly","[Monthly Date] Date()-30")
1

?DCOUNT("*","tblmonthly","[Monthly Date] Between Date()-30 and Date()")
1
?DCount("[Monthly Date]","tblmonthly")
2

?DCount("*","tblmonthly")
2

There are only 2 records in my db - dates a
25 June 09 & 27 July 09

Steve

"John W. Vinson" wrote in message
...
On Mon, 3 Aug 2009 22:24:30 +0100, "steve goodrich"
wrote:

Run time error 64479, can't find the name Monthly_date you entered in your
expression
Does your table contain a field named Monthly_date (with the underscore)?

If it contains a field named "Monthly date", replace Monthly_date with
[Monthly date] including the square brackets.
--

John W. Vinson [MVP]

John,

Yes, that did the trick - works perfectly.
Many thanks for your time and patience.

I have just bought a copy of Access 97 Programming for dummies (We use 97 at
work) to try to understand VBA
Could you recommend any web sites for absolute beginners that have a basic
understanding of Access but are totally clueless when it comes to code!!

"John Spencer" wrote in message
...
Good, then it seems as if you need to use the test with the correct field
name and hopefully you will get the results you are looking for.

IF DCOUNT("[Monthly Date]","tblmonthly","[Monthly Date] Between Date()-30
and Date()") = 0 THEN
MsgBox "Test overdue,please schedule asap", vbCritical, "Monthly
Checks"
End If

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

steve goodrich wrote:
John,
Amended the code and the following results wre produced

?DCOUNT("*","tblmonthly","[Monthly Date] Date()-30")
1

?DCOUNT("*","tblmonthly","[Monthly Date] Between Date()-30 and Date()")
1
?DCount("[Monthly Date]","tblmonthly")
2

?DCount("*","tblmonthly")
2

There are only 2 records in my db - dates a
25 June 09 & 27 July 09

Steve

"John W. Vinson" wrote in message
...
On Mon, 3 Aug 2009 22:24:30 +0100, "steve goodrich"
wrote:

Run time error 64479, can't find the name Monthly_date you entered in
your
expression
Does your table contain a field named Monthly_date (with the
underscore)?

If it contains a field named "Monthly date", replace Monthly_date with
[Monthly date] including the square brackets.
--

John W. Vinson [MVP]