date comparison not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have users in Access 2k who suddenly can't run a date comparison in an
..mde file. This worked for at least a year previous to this. I hoped that
upgrading them to Access 2003 would help, but no luck. Of course, the code
works on my pc. I suspect a patch would fix it for them or a .dll, but can't
find one addressing this issue.
The code should check that the renewal date is more a year ago. On her pc,
however, the error code 3075 pops up. (I trapped for that now, but before I
did, it merrily marked all records as needing renewal.)
Here's the code:

Dim strSQL As String
strSQL = "SELECT * FROM qry_for_Reports WHERE ([status] = 'CP' and
[RenewalDate] > (DATE() - 365) " 'renewed in the past year
strSQL = strSQL & " And Month([CertExpireDate]) =" & cycle 'expires this
cycle (cycle is an integer, 3 or 9)
strSQL = strSQL & " And (([RenewalLetter] < (DATE() - 365)) Or
IsNull([RenewalLetter]))) " 'renewal letter is not current or not sent in
the past year
Any help you can give me is appreciated.
Thanks!
 
Hi Joanne,

If the code used to work but now doesn't, and still works on other
machines, the cause is extremely unlikely to be in a SQL statement that
presumably hasn't changed.

The first thing I'd do is check for broken references on the problematic
machines. See
http://www.accessmvp.com/DJSteele/AccessReferenceErrors.html for
details.

Otherwise, please post the error message are you getting, and the code
that's triggering it.

Hi, I have users in Access 2k who suddenly can't run a date comparison in an
.mde file. This worked for at least a year previous to this. I hoped that
upgrading them to Access 2003 would help, but no luck. Of course, the code
works on my pc. I suspect a patch would fix it for them or a .dll, but can't
find one addressing this issue.
The code should check that the renewal date is more a year ago. On her pc,
however, the error code 3075 pops up. (I trapped for that now, but before I
did, it merrily marked all records as needing renewal.)
Here's the code:

Dim strSQL As String
strSQL = "SELECT * FROM qry_for_Reports WHERE ([status] = 'CP' and
[RenewalDate] > (DATE() - 365) " 'renewed in the past year
strSQL = strSQL & " And Month([CertExpireDate]) =" & cycle 'expires this
cycle (cycle is an integer, 3 or 9)
strSQL = strSQL & " And (([RenewalLetter] < (DATE() - 365)) Or
IsNull([RenewalLetter]))) " 'renewal letter is not current or not sent in
the past year
Any help you can give me is appreciated.
Thanks!
Click to expand...
 
I understand what you're saying, but they are running compiled code--the
reference should be included. When I check for broken/missing references on
my pc, in the source code, it is just fine. I suspect they are missing a dll
on their machine, but they seem to have the usual suspects, like MSMAPPI.ocx.
But I see that there's some advice in the link you posted for dealing with
"non-problems" like this, so I'll check that out. Thanks!
 
JoanneB said:
I understand what you're saying, but they are running compiled
code--the reference should be included. When I check for
broken/missing references on my pc, in the source code, it is just
fine.
Click to expand...

When people in these groups talk about "missing references" they don't
actually mean that the reference is missing. You're correct that the
references are part of your file and will always be there. What they mean
is that the library file that the reference is pointing at is missing (or
the wrong version or in the wrong location).

Open a brand new file on your machine and look at the handful of references
that are added by default. Then look at the references of the file in
question. Are there more? Any time you intend to distribute an Access app
to multiple PCs every effort should be made to avoid adding any additional
references or you will constantly be dealing with this issue. Most of the
time additional references are not necessary because the same functionality
can be achieved without external libraries at all or by using late binding
rather then adding references.
 
Interesting. I did as you suggested and found four additional references in
my program:
Microsoft MAPI Controls 6.0
Microsoft Common Dialog Control 6.0 (SP6)
Microsoft Windows Common Controls 6.0 (SP6)
Visual Basic for Applications Extensibility 5.3

MAPI & I are old friends--I know it needs to be on the users' pc and we
install it with the program. Most of the functions I need seem to be
there--MID, LEFT, etc. I was kind of surprised that those wouldn't be in VBA,
since they are so basic. Am I wrong about that? The common dialog control is
used in this app, but not most of my others. I have no idea what the other
two references are there for...makes me wonder if I really need them.
This program, and most of the others here, have migrated from Access 97 to
2000 and now to 2003. I'm sure we've got some baggage we don't need that way.
Thanks for your help!
 
JoanneB said:
Interesting. I did as you suggested and found four additional
references in my program:
Microsoft MAPI Controls 6.0
Microsoft Common Dialog Control 6.0 (SP6)
Microsoft Windows Common Controls 6.0 (SP6)
Visual Basic for Applications Extensibility 5.3

MAPI & I are old friends--I know it needs to be on the users' pc and
we install it with the program. Most of the functions I need seem to
be there--MID, LEFT, etc. I was kind of surprised that those wouldn't
be in VBA, since they are so basic. Am I wrong about that? The common
dialog control is used in this app, but not most of my others. I have
no idea what the other two references are there for...makes me wonder
if I really need them.
This program, and most of the others here, have migrated from Access
97 to 2000 and now to 2003. I'm sure we've got some baggage we don't
need that way. Thanks for your help!
Click to expand...

The same built in functions malfunctioning is a symptom of ANY broken
reference. The functions you are having problems with need not have
anything to do with the reference giving you the problems.

The Common dialog control is one of the worst libraries for introducing this
problem becase of the various (incompatible) versions that MS has released
over the years. Direct API calls can replace almost everything that you are
using that control for.

You can remove (uncheck) any reference you are not sure about and then
attempt a compile on the project. If it compiles without error then you
don't need that reference. If you get an error then you have code somewhere
in the app that is using that reference. Here is where I would recommend
switching to late binding. This not only makes the reference unnecessary,
but usually makes your code version-independent for that particular library
on the target PC.
 
My bet is on:
Visual Basic for Applications Extensibility 5.3

When you convert to a newer version, Access insists on adding additional
references for "backwards" compatibility.
As a result, a lot of "stuff" gets broken.

Top of my head (I hate not having Access on this PC) a basic database
only needs the following references:
Access
VBA
ADO or DAO (or both, depending on what you are doing).
So there should only be 3 or 4 entries.
After these 3 (or 4) references come the ones specific to your needs:
MAPI (but not for adding functions, as per below)
Windows Common Controls
Common Dialog Control

Regarding Left, Right, Mid, Instr, shortcut functionality like Ctrl ; -
they are all available with the first 3 (or 4) references. They only
don't work if you get the darned "compatibility" library (Visual Basic
for Applications Extensibility 5.3)

Regards,
Andreas
 
Back
Top