Query date errors

[kurt.baker]

Hi All,

I have many queries in an Access DB which for the most part works fine.
Except when I have any that use dates, several, not all of the PC's
here get the error:
Microsoft Access has encountered a problem and needs to close. We are
sorry for the inconvenience.

The info from the error:
AppName: msaccess.exe AppVer: 10.0.6771.0 ModName: vbe6.dll
ModVer: 6.4.99.72 Offset: 001b2b61

One example of a query (this is the shortest one, but others are
similar where they compare dates in one way or another).
(Query CompCount)
SELECT Job.Complete
FROM Job
WHERE (((Job.Complete)=1) AND ((Job.CompleteDate) Between #8/1/2006#
And #8/16/2006#));

Like I mentioned, not all of the PC's have this problem. They all have
Windows XP with current updates. We did put the latest Office SP on it
and it was no help. Even just opening the query to view the data causes
the error, it doesn't have to be run from any specific program.

I have tried to use Format() and that doesn't work. However, if I used
CVDate( #8/1/2006#), I can open the query, but when I run the
application that uses the dates, it changes it back to the original
query shown above, and up comes the error. The code that gets the error
is:

strSql = "SELECT DISTINCTROW [CompCount2].[CountOfComplete] AS Stuff
FROM [CompCount2];"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot) ' <---actual error
line

CompCount2 is just another query to total the count found in the
failing query above
(SELECT Count(CompCount.Complete) AS CountOfComplete
FROM CompCount

So, does anyone have any ideas how to correctly use dates in a query?
All suggestions are appreciated. Thanks!

Kurt

 

[John Spencer]

Don't know what is the cause. However, when I see an Access application
work correctly on one computer and fail on others, I always suspect that
there is a references problem. The vbe6.dll ModVer: 6.4.99.72 may be
different on the computers where you are getting the failure.

As I said this is just a guess.


To do its job, Access makes use of various external program and object
libraries. If you move a database from one machine to another, these
references may be "broken".

When this happens, you need to take steps to let Access repair the
reference(s) ON THE COMPUTER WHERE THE FAILURE IS OCCURING.

Here are MVP Doug Steele's instructions for how to do it:

*** Quote ***

Any time functions that previously worked suddenly don't, the first thing to
suspect is a references problem.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Just so you know: the problem will occur even if the library that contains
the specific function that's failing doesn't have a problem.

**** End Quote ****

Hi All,

I have many queries in an Access DB which for the most part works fine.
Except when I have any that use dates, several, not all of the PC's
here get the error:
Microsoft Access has encountered a problem and needs to close. We are
sorry for the inconvenience.

The info from the error:
AppName: msaccess.exe AppVer: 10.0.6771.0 ModName: vbe6.dll
ModVer: 6.4.99.72 Offset: 001b2b61

One example of a query (this is the shortest one, but others are
similar where they compare dates in one way or another).
(Query CompCount)
SELECT Job.Complete
FROM Job
WHERE (((Job.Complete)=1) AND ((Job.CompleteDate) Between #8/1/2006#
And #8/16/2006#));

Like I mentioned, not all of the PC's have this problem. They all have
Windows XP with current updates. We did put the latest Office SP on it
and it was no help. Even just opening the query to view the data causes
the error, it doesn't have to be run from any specific program.

I have tried to use Format() and that doesn't work. However, if I used
CVDate( #8/1/2006#), I can open the query, but when I run the
application that uses the dates, it changes it back to the original
query shown above, and up comes the error. The code that gets the error
is:

strSql = "SELECT DISTINCTROW [CompCount2].[CountOfComplete] AS Stuff
FROM [CompCount2];"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot) ' <---actual error
line

CompCount2 is just another query to total the count found in the
failing query above
(SELECT Count(CompCount.Complete) AS CountOfComplete
FROM CompCount

So, does anyone have any ideas how to correctly use dates in a query?
All suggestions are appreciated. Thanks!

Kurt

 

[kurt.baker]

John,

Thank you very much for the reply. I have Googled for over a day on
this and didn't see any info as such. I does look like this could very
well take care of the problem. I have limited access (access..?....no
pun intended) to the PC in question, but I'll definitely go over all of
this and give it a try. It might take some time, since I am limited to
these PC's but, I'll post back and let you know how it turns out.
Again, thank you very much for the info!!!

Regards,

Kurt

Don't know what is the cause. However, when I see an Access application
work correctly on one computer and fail on others, I always suspect that
there is a references problem. The vbe6.dll ModVer: 6.4.99.72 may be
different on the computers where you are getting the failure.

As I said this is just a guess.


To do its job, Access makes use of various external program and object
libraries. If you move a database from one machine to another, these
references may be "broken".

When this happens, you need to take steps to let Access repair the
reference(s) ON THE COMPUTER WHERE THE FAILURE IS OCCURING.

Here are MVP Doug Steele's instructions for how to do it:

*** Quote ***

Any time functions that previously worked suddenly don't, the first thing to
suspect is a references problem.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Just so you know: the problem will occur even if the library that contains
the specific function that's failing doesn't have a problem.

**** End Quote ****

Hi All,

I have many queries in an Access DB which for the most part works fine.
Except when I have any that use dates, several, not all of the PC's
here get the error:
Microsoft Access has encountered a problem and needs to close. We are
sorry for the inconvenience.

The info from the error:
AppName: msaccess.exe AppVer: 10.0.6771.0 ModName: vbe6.dll
ModVer: 6.4.99.72 Offset: 001b2b61

One example of a query (this is the shortest one, but others are
similar where they compare dates in one way or another).
(Query CompCount)
SELECT Job.Complete
FROM Job
WHERE (((Job.Complete)=1) AND ((Job.CompleteDate) Between #8/1/2006#
And #8/16/2006#));

Like I mentioned, not all of the PC's have this problem. They all have
Windows XP with current updates. We did put the latest Office SP on it
and it was no help. Even just opening the query to view the data causes
the error, it doesn't have to be run from any specific program.

I have tried to use Format() and that doesn't work. However, if I used
CVDate( #8/1/2006#), I can open the query, but when I run the
application that uses the dates, it changes it back to the original
query shown above, and up comes the error. The code that gets the error
is:

strSql = "SELECT DISTINCTROW [CompCount2].[CountOfComplete] AS Stuff
FROM [CompCount2];"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot) ' <---actual error
line

CompCount2 is just another query to total the count found in the
failing query above
(SELECT Count(CompCount.Complete) AS CountOfComplete
FROM CompCount

So, does anyone have any ideas how to correctly use dates in a query?
All suggestions are appreciated. Thanks!

Kurt

 

[kurt.baker]

John,

Thanks again for your reply. The information was very interesting and I
think will help in several areas. But for this problem, I didn't have
access to the PC's to do some of the checking in articles that were
mentioned, so I tried to use CDate() on all the dates referenced
throughout the application, and it looks to work(which might just be
putting a bandage on it and not actually correcting the real problem).
It should work until something else causes it to break. But like I
said, I do think that this info will be helpful in other problems I
have (or will get).

Regards,

Kurt