Error 3464 with dCount - some computers only

[Rob Parker]

A user of one of my applications gets the error message "Run Time
Error 3464 - Data type mismatch in criteria expression" from this line
of code:
If DCount("*", "qryDocReviewRemind") > 0 Then

The error also arises if I try to print this dcount expression in the
immediate window, on the user's computer.

The query itself runs fine, and produces a recordset with 36 records
from the current data.

If I run the same database (the same physical file - not a copy of it)
on my computer, this code (and the print in the immediate window)
works fine.

I'm at a loss here; and a search of the Access newsgroups has not
produced anything of assistance (although it has produced lots of
hits). Any suggestions would be gratefully appreciated.

TIA,

Rob

 

[Steve]

Open your application on a computer that you know does not get this error.
Go to the code window behind one of your forms. Click on Tools - References
and write down the list of references. Now go to the computer that sees this
error and look at the references there. If the list is different in any way
make it the same. If the list is the same, uncheck all the references you
can and then scroll the list and reinstall them.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Rob Parker]

The references on both systems appeared to be identical (both
application and version).

Unchecked all (except VBA and MS Access) on the problem computer.
Exited from Access, restarted and re-installed all references
(one .ocx reference was missing, so installed from system32 folder).
Reference lists on both computers again identical.

Problem still exists.

Any other suggestions?

Rob

 

[Steve]

Look at the field(s) in qryDocReviewRemind where there is criteria. Is the
criteria a date where the field is a number or text? Is the criteria text
where the field is numeric? Is the criteria numeric where the field is text?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Jana]

Look at the field(s) in qryDocReviewRemind where there is criteria. Is the
criteria a date where the field is a number or text? Is the criteria text
where the field is numeric? Is the criteria numeric where the field is text?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)









- Show quoted text -

Rob:

Can you post the SQL for the query qryDocReviewRemind? Perhaps there
is something odd in the underlying query that is messing you up.

Thanks!
Jana

 

[Rob Parker]

First, a minor correction to my original post. The line producing the error
is actually:
If DCount("*", "qryDocQualityReviewRemind") > 0 Then
[NB: the only change from my original post is the name of the query - I
inadvertently cut/pasted the wrong line from my code.]

The SQL for the query which is producing the error in the dcount statement
is as follows:

SELECT qryDocReview.DocGroup, qryDocReview.ID, qryDocReview.Title,
qryDocReview.Author, qryDocReview.NQRDate, tblDocQRevReminders.Printed
FROM qryDocReview LEFT JOIN tblDocQRevReminders ON (qryDocReview.NQRDate =
tblDocQRevReminders.NQRDate) AND (qryDocReview.ID = tblDocQRevReminders.ID)
WHERE (((tblDocQRevReminders.Printed)=False) AND
((DateAdd("d",-getconst("conDocRevRemind"),[qryDocReview].[NQRDate]))<=Date()))
OR (((tblDocQRevReminders.Printed) Is Null) AND
((DateAdd("d",-getconst("conDocRevRemind"),[qryDocReview].[NQRDate]))<=Date()))
ORDER BY qryDocReview.DocGroup, qryDocReview.ID;

The query itself runs OK on the computer which gives the dcount error.

The line of code I originally posted was:
If DCount("*", "qryDocReviewRemind") > 0 Then
This occurs a little earlier in the same routine, and causes no error. Its
SQL is:

SELECT qryDocReview.DocGroup, qryDocReview.ID, qryDocReview.Title,
qryDocReview.Author, qryDocReview.NRDate AS [Next Review Date],
tblDocReminders.Printed
FROM qryDocReview LEFT JOIN tblDocReminders ON (qryDocReview.ID =
tblDocReminders.ID) AND (qryDocReview.NRDate =
tblDocReminders.NextReviewDate)
WHERE (((tblDocReminders.Printed)=False) AND
((DateAdd("d",-getconst("conDocRevRemind"),[NRDate]))<=Date())) OR
(((tblDocReminders.Printed) Is Null) AND
((DateAdd("d",-getconst("conDocRevRemind"),[NRDate]))<=Date()))
ORDER BY qryDocReview.DocGroup, qryDocReview.ID;

The only difference between these queries is that the one that fails uses a
field NQRDate, and the other uses a field NRDate. Both fields initially
come from linked spreadsheets, and are obtained by qryDocReview (a union
query which combines records from all the spreadsheets) from a series of
select queries which are all of the form:
SELECT "Technical Reports" AS DocGroup, [Technical Reports].ID, [Technical
Reports].Title, [Technical Reports].[Author/Prepared By] AS Author,
IIf(CVDate([Planned Issue Date])>CVDate(nz([Actual Issue
Date],0)),CVDate([Planned Issue Date]),Null) AS NRDate, CVDate([Next Quality
Review Date]) AS NQRDate
FROM [Technical Reports]
WHERE ((([Technical Reports].ID) Is Not Null));

Again, TIA for anything which will help solve this,

Rob

 

[John W. Vinson]

The only difference between these queries is that the one that fails uses a
field NQRDate, and the other uses a field NRDate. Both fields initially
come from linked spreadsheets, and are obtained by qryDocReview (a union
query which combines records from all the spreadsheets) from a series of
select queries which are all of the form:

Doublecheck that the field is in fact of Date/Time datatype and is not NULL.
IMports from Excel may be the wrong datatype... and that might be giving this
error. UNION queries (particularly if they include an IIF() function call for
a calculated field) may do the same.

John W. Vinson [MVP]

 

[Rob Parker]

Thanks for the suggestion, John.

I'm not at work today, so I can't check that this will cure the problem on
the problematic machine. However, when I checked the linked tables for the
spreadsheets in a copy of the database which I have at home (and where I
also do not get an error from the code line), I found that in one case the
NQRDate field was Text type, rather than Date/Time. I'll post what happens
after I fix at work tomorrow, and check with the problematic machine.

If it is the problem, do you know (or would you care to speculate) why it
only appeared on one machine; both the machines at work appear to have the
same versions of everything - certainly all the references, which I checked
following a previous reply to my original post. My home system, where I've
just checked the linked table field properties, is actually running earlier
(Office XP) versions of everything.

Rob

 

[Jana]

Thanks for the suggestion, John.

I'm not at work today, so I can't check that this will cure the problem on
the problematic machine. However, when I checked the linked tables for the
spreadsheets in a copy of the database which I have at home (and where I
also do not get an error from the code line), I found that in one case the
NQRDate field was Text type, rather than Date/Time. I'll post what happens
after I fix at work tomorrow, and check with the problematic machine.

If it is the problem, do you know (or would you care to speculate) why it
only appeared on one machine; both the machines at work appear to have the
same versions of everything - certainly all the references, which I checked
following a previous reply to my original post. My home system, where I've
just checked the linked table field properties, is actually running earlier
(Office XP) versions of everything.

Rob







- Show quoted text -

Rob:

I think John is on the right track there. Somewhere, one of your
CVDate calls appears to be failing the conversion process. I have
seen some 'oddness' where a query can recover better from a failed
conversion than an aggregate function does. Are all of these dates
that you're gathering from Excel pulling from the exact same Excel
files? Or does each machine have different Excel data source files?

Jana

 

[Rob Parker]

Hi Jana,

Yes, the different machines are all running exactly the same .mdb file (I
copy the live file to my machine for testing/development - it's essentially
a single-user application), and the links to the various spreadsheets are
exactly the same. The sheets themselves are in a single file on a server.
The exception is the test copy on my home system, where I found a problem
with the datatype for the NQRDate in one of the linked tables; I expect that
is the same on the live system, but I will confirm (and fix) if that's the
case. And that may be in a few days, since I won't be back to the work site
probably until next week now.

Moreover, as I said in an my original post, the query itself runs OK on the
machine which throws the error with the dCount expression. And it's that
bit that still has me absolutely perplexed.

Rob

 

[Rob Parker]

More info on my problem (and apologies for the long post):

I've found the underlying cause of my problem, but I'm still puzzled as to
why certain things happen, and how to best solve the problem.

Cause:

One of the linked Excel sheets contains cells with a space character, in a
column which should contain date entries. The linked table does not have a
problem with the datatype for this field (table design view of the linked
table show it to be a DateTime field), but it does show entries of #Num! for
the cells which contain a space character in the linked sheet.

Situation:

I am using links to the Excel sheets, not importing via code. The links use
the first row of the sheet as the field names.

The linked tables are used as the source for SELECT queries, which are then
combined in a Union query. Each Select query is similar to this (posted
previously, repeated here for convenience):
SELECT "Technical Reports" AS DocGroup, [Technical Reports].ID, [Technical
Reports].Title, [Technical Reports].[Author/Prepared By] AS Author,
IIf(CVDate([Planned Issue Date])>CVDate(nz([Actual Issue
Date],0)),CVDate([Planned Issue Date]),Null) AS NRDate, CVDate([Next Quality
Review Date]) AS NQRDate
FROM [Technical Reports]
WHERE ((([Technical Reports].ID) Is Not Null));

The #Num! error from the linked table propagates into the output of this
query. A dCount on this query (in the Immediate window on my home system)
runs OK (unlike the dCount on another query (see below), which fails on some
systems - the reason for my original post).

The Select query above is one of several which are combined in this Union
query:
SELECT * FROM qryDocPlans
UNION ALL
SELECT * FROM qryDocProcedures
UNION ALL
SELECT * FROM qryDocChecklists
UNION ALL
SELECT * FROM qryDocSpecifications
UNION ALL
SELECT * FROM qryDocTechNotes
UNION ALL
SELECT * FROM qryDocTechReports
UNION ALL
SELECT * FROM qryDocWorkInstructions;

This Union query fails to run, giving the "Data type mismatch in criteria
expression" error.

This Union query is used in the following Select query (also posted
previously, and again repeated here for convenience):
SELECT qryDocReview.DocGroup, qryDocReview.ID, qryDocReview.Title,
qryDocReview.Author, qryDocReview.NQRDate, tblDocQRevReminders.Printed
FROM qryDocReview LEFT JOIN tblDocQRevReminders ON (qryDocReview.NQRDate =
tblDocQRevReminders.NQRDate) AND (qryDocReview.ID = tblDocQRevReminders.ID)
WHERE (((tblDocQRevReminders.Printed)=False) AND
((DateAdd("d",-getconst("conDocRevRemind"),[qryDocReview].[NQRDate]))<=Date()))
OR (((tblDocQRevReminders.Printed) Is Null) AND
((DateAdd("d",-getconst("conDocRevRemind"),[qryDocReview].[NQRDate]))<=Date()))
ORDER BY qryDocReview.DocGroup, qryDocReview.ID;

This query does not fail on most of the systems I've tested it on, and a
dCount based on this query fails (with the "data type mismatch" error) on
some (but not all) systems.

Questions:

Why does the last query run on some systems and fail on some systems?
[Thought: is this possibly a Jet update/service pack issue?] And why, on at
least one system, does the query run but the dCount on the query fail?

How can I test for the #Num! entries (errors) in the first query (based on
the linked tables)? That would be the simplest method of preventing data
issues in the Excel sheets propagating into my queries, but I can't seem to
find anything that works - in particular, none of the error functions seem
to find anything wrong.

If I can't test for the #Num! entries in the linked tables, what other
alternatives do I have to solve this issue?

TIA,

Rob

 

[Jana]

More info on my problem (and apologies for the long post):

I've found the underlying cause of my problem, but I'm still puzzled as to
why certain things happen, and how to best solve the problem.

Cause:

One of the linked Excel sheets contains cells with a space character, in a
column which should contain date entries. The linked table does not have a
problem with the datatype for this field (table design view of the linked
table show it to be a DateTime field), but it does show entries of #Num! for
the cells which contain a space character in the linked sheet.

Situation:

I am using links to the Excel sheets, not importing via code. The links use
the first row of the sheet as the field names.

The linked tables are used as the source for SELECT queries, which are then
combined in a Union query. Each Select query is similar to this (posted
previously, repeated here for convenience):
SELECT "Technical Reports" AS DocGroup, [Technical Reports].ID, [Technical
Reports].Title, [Technical Reports].[Author/Prepared By] AS Author,
IIf(CVDate([Planned Issue Date])>CVDate(nz([Actual Issue
Date],0)),CVDate([Planned Issue Date]),Null) AS NRDate, CVDate([Next Quality
Review Date]) AS NQRDate
FROM [Technical Reports]
WHERE ((([Technical Reports].ID) Is Not Null));

The #Num! error from the linked table propagates into the output of this
query. A dCount on this query (in the Immediate window on my home system)
runs OK (unlike the dCount on another query (see below), which fails on some
systems - the reason for my original post).

The Select query above is one of several which are combined in this Union
query:
SELECT * FROM qryDocPlans
UNION ALL
SELECT * FROM qryDocProcedures
UNION ALL
SELECT * FROM qryDocChecklists
UNION ALL
SELECT * FROM qryDocSpecifications
UNION ALL
SELECT * FROM qryDocTechNotes
UNION ALL
SELECT * FROM qryDocTechReports
UNION ALL
SELECT * FROM qryDocWorkInstructions;

This Union query fails to run, giving the "Data type mismatch in criteria
expression" error.

This Union query is used in the following Select query (also posted
previously, and again repeated here for convenience):
SELECT qryDocReview.DocGroup, qryDocReview.ID, qryDocReview.Title,
qryDocReview.Author, qryDocReview.NQRDate, tblDocQRevReminders.Printed
FROM qryDocReview LEFT JOIN tblDocQRevReminders ON (qryDocReview.NQRDate =
tblDocQRevReminders.NQRDate) AND (qryDocReview.ID = tblDocQRevReminders..ID)
WHERE (((tblDocQRevReminders.Printed)=False) AND
((DateAdd("d",-getconst("conDocRevRemind"),[qryDocReview].[NQRDate]))<=Date­()))
OR (((tblDocQRevReminders.Printed) Is Null) AND
((DateAdd("d",-getconst("conDocRevRemind"),[qryDocReview].[NQRDate]))<=Date­()))
ORDER BY qryDocReview.DocGroup, qryDocReview.ID;

This query does not fail on most of the systems I've tested it on, and a
dCount based on this query fails (with the "data type mismatch" error) on
some (but not all) systems.

Questions:

Why does the last query run on some systems and fail on some systems?
[Thought: is this possibly a Jet update/service pack issue?] And why, onat
least one system, does the query run but the dCount on the query fail?

How can I test for the #Num! entries (errors) in the first query (based on
the linked tables)? That would be the simplest method of preventing data
issues in the Excel sheets propagating into my queries, but I can't seem to
find anything that works - in particular, none of the error functions seem
to find anything wrong.

If I can't test for the #Num! entries in the linked tables, what other
alternatives do I have to solve this issue?

TIA,

Rob

Hi Jana,

Yes, the different machines are all running exactly the same .mdb file (I
copy the live file to my machine for testing/development - it's
essentially a single-user application), and the links to the various
spreadsheets are exactly the same. The sheets themselves are in a single
file on a server. The exception is the test copy on my home system, where
I found a problem with the datatype for the NQRDate in one of the linked
tables; I expect that is the same on the live system, but I will confirm
(and fix) if that's the case. And that may be in a few days, since I
won't be back to the work site probably until next week now.

Moreover, as I said in an my original post, the query itself runs OK on
the machine which throws the error with the dCount expression. And it's
that bit that still has me absolutely perplexed.

- Show quoted text -

Rob:

TBH, I'm not all that 'up' on Jet Version issues, but I did a google
groups search for:
"#Num!" excel (in this group)
and found a plethora of postings that seem to indicate that linking
Excel is not really the best way to go on your solution. It just
doesn't give you the control you need. I've tried a couple of things
to try and trap for the #Num! error, but can't get a reliable solution
for you. My best suggestion would be to create an import function
that grabs the dates from your Excel files and tests as you import the
dates. Probably not the answer you're looking for, but it appears the
only way to really deal with anomalous data entry on your
spreadsheets.

HTH,
Jana

 

[Jana]

More info on my problem (and apologies for the long post):

I've found the underlying cause of my problem, but I'm still puzzled as to
why certain things happen, and how to best solve the problem.

Cause:

One of the linked Excel sheets contains cells with a space character, in a
column which should contain date entries. The linked table does not have a
problem with the datatype for this field (table design view of the linked
table show it to be a DateTime field), but it does show entries of #Num! for
the cells which contain a space character in the linked sheet.

Situation:

I am using links to the Excel sheets, not importing via code. The links use
the first row of the sheet as the field names.

The linked tables are used as the source for SELECT queries, which are then
combined in a Union query. Each Select query is similar to this (posted
previously, repeated here for convenience):
SELECT "Technical Reports" AS DocGroup, [Technical Reports].ID, [Technical
Reports].Title, [Technical Reports].[Author/Prepared By] AS Author,
IIf(CVDate([Planned Issue Date])>CVDate(nz([Actual Issue
Date],0)),CVDate([Planned Issue Date]),Null) AS NRDate, CVDate([Next Quality
Review Date]) AS NQRDate
FROM [Technical Reports]
WHERE ((([Technical Reports].ID) Is Not Null));

The #Num! error from the linked table propagates into the output of this
query. A dCount on this query (in the Immediate window on my home system)
runs OK (unlike the dCount on another query (see below), which fails on some
systems - the reason for my original post).

The Select query above is one of several which are combined in this Union
query:
SELECT * FROM qryDocPlans
UNION ALL
SELECT * FROM qryDocProcedures
UNION ALL
SELECT * FROM qryDocChecklists
UNION ALL
SELECT * FROM qryDocSpecifications
UNION ALL
SELECT * FROM qryDocTechNotes
UNION ALL
SELECT * FROM qryDocTechReports
UNION ALL
SELECT * FROM qryDocWorkInstructions;

This Union query fails to run, giving the "Data type mismatch in criteria
expression" error.

This Union query is used in the following Select query (also posted
previously, and again repeated here for convenience):
SELECT qryDocReview.DocGroup, qryDocReview.ID, qryDocReview.Title,
qryDocReview.Author, qryDocReview.NQRDate, tblDocQRevReminders.Printed
FROM qryDocReview LEFT JOIN tblDocQRevReminders ON (qryDocReview.NQRDate =
tblDocQRevReminders.NQRDate) AND (qryDocReview.ID = tblDocQRevReminders..ID)
WHERE (((tblDocQRevReminders.Printed)=False) AND
((DateAdd("d",-getconst("conDocRevRemind"),[qryDocReview].[NQRDate]))<=Date­()))
OR (((tblDocQRevReminders.Printed) Is Null) AND
((DateAdd("d",-getconst("conDocRevRemind"),[qryDocReview].[NQRDate]))<=Date­()))
ORDER BY qryDocReview.DocGroup, qryDocReview.ID;

This query does not fail on most of the systems I've tested it on, and a
dCount based on this query fails (with the "data type mismatch" error) on
some (but not all) systems.

Questions:

Why does the last query run on some systems and fail on some systems?
[Thought: is this possibly a Jet update/service pack issue?] And why, onat
least one system, does the query run but the dCount on the query fail?

How can I test for the #Num! entries (errors) in the first query (based on
the linked tables)? That would be the simplest method of preventing data
issues in the Excel sheets propagating into my queries, but I can't seem to
find anything that works - in particular, none of the error functions seem
to find anything wrong.

If I can't test for the #Num! entries in the linked tables, what other
alternatives do I have to solve this issue?

TIA,

Rob

Hi Jana,

Yes, the different machines are all running exactly the same .mdb file (I
copy the live file to my machine for testing/development - it's
essentially a single-user application), and the links to the various
spreadsheets are exactly the same. The sheets themselves are in a single
file on a server. The exception is the test copy on my home system, where
I found a problem with the datatype for the NQRDate in one of the linked
tables; I expect that is the same on the live system, but I will confirm
(and fix) if that's the case. And that may be in a few days, since I
won't be back to the work site probably until next week now.

Moreover, as I said in an my original post, the query itself runs OK on
the machine which throws the error with the dCount expression. And it's
that bit that still has me absolutely perplexed.

- Show quoted text -

Ack, my previous post seems not to have taken!

Rob:

After searching this group for #Num! Excel, I see a ton of posts that
seem to indicate that your best solution is to NOT link to Excel
sheets, but to run a function to import the necessary dates, and deal
with anomalous data during the import. I tried several things to try
to trap for the #Num! with a linked sheet, and just wasn't able to
come up with anything for you. I was, however, able to duplicate
exactly the issue that you're having on my own machine. The problem
is that Excel is designed to allow any type of data entry on a per-
cell level, while Access is expecting consistency on a per-column
basis. Without pulling each date in and testing as you go, I don't
think you'll ever have a fool proof solution.

I admit that I am not 'up' on Jet Version issues, so my input there is
nil.

HTH,
Jana

 

[Rob Parker]

Thanks for the comments, Jana.

I reposted my problem a couple of days ago (with a different subject line
"REPOST: Datatype mismatch issue", and cross-posted to several groups), but
I've had no response as yet. Beginning to think my problem is too hard.

And yes, I'm also thinking along the lines you suggested - changing from
links to the sheets to importing the data into tables with all text fields,
and then examining/converting each field in each record via a query. I've
done that in a different application and it works well in coping with
inconsistencies in the Excel data.

Thanks again for your input,

Rob

More info on my problem (and apologies for the long post):

I've found the underlying cause of my problem, but I'm still puzzled as to
why certain things happen, and how to best solve the problem.

Cause:

One of the linked Excel sheets contains cells with a space character, in a
column which should contain date entries. The linked table does not have a
problem with the datatype for this field (table design view of the linked
table show it to be a DateTime field), but it does show entries of #Num!
for
the cells which contain a space character in the linked sheet.

Situation:

I am using links to the Excel sheets, not importing via code. The links
use
the first row of the sheet as the field names.

The linked tables are used as the source for SELECT queries, which are
then
combined in a Union query. Each Select query is similar to this (posted
previously, repeated here for convenience):
SELECT "Technical Reports" AS DocGroup, [Technical Reports].ID, [Technical
Reports].Title, [Technical Reports].[Author/Prepared By] AS Author,
IIf(CVDate([Planned Issue Date])>CVDate(nz([Actual Issue
Date],0)),CVDate([Planned Issue Date]),Null) AS NRDate, CVDate([Next
Quality
Review Date]) AS NQRDate
FROM [Technical Reports]
WHERE ((([Technical Reports].ID) Is Not Null));

The #Num! error from the linked table propagates into the output of this
query. A dCount on this query (in the Immediate window on my home system)
runs OK (unlike the dCount on another query (see below), which fails on
some
systems - the reason for my original post).

The Select query above is one of several which are combined in this Union
query:
SELECT * FROM qryDocPlans
UNION ALL
SELECT * FROM qryDocProcedures
UNION ALL
SELECT * FROM qryDocChecklists
UNION ALL
SELECT * FROM qryDocSpecifications
UNION ALL
SELECT * FROM qryDocTechNotes
UNION ALL
SELECT * FROM qryDocTechReports
UNION ALL
SELECT * FROM qryDocWorkInstructions;

This Union query fails to run, giving the "Data type mismatch in criteria
expression" error.

This Union query is used in the following Select query (also posted
previously, and again repeated here for convenience):
SELECT qryDocReview.DocGroup, qryDocReview.ID, qryDocReview.Title,
qryDocReview.Author, qryDocReview.NQRDate, tblDocQRevReminders.Printed
FROM qryDocReview LEFT JOIN tblDocQRevReminders ON (qryDocReview.NQRDate =
tblDocQRevReminders.NQRDate) AND (qryDocReview.ID =
tblDocQRevReminders.ID)
WHERE (((tblDocQRevReminders.Printed)=False) AND
((DateAdd("d",-getconst("conDocRevRemind"),[qryDocReview].[NQRDate]))<=Date­()))
OR (((tblDocQRevReminders.Printed) Is Null) AND
((DateAdd("d",-getconst("conDocRevRemind"),[qryDocReview].[NQRDate]))<=Date­()))
ORDER BY qryDocReview.DocGroup, qryDocReview.ID;

This query does not fail on most of the systems I've tested it on, and a
dCount based on this query fails (with the "data type mismatch" error) on
some (but not all) systems.

Questions:

Why does the last query run on some systems and fail on some systems?
[Thought: is this possibly a Jet update/service pack issue?] And why, on
at
least one system, does the query run but the dCount on the query fail?

How can I test for the #Num! entries (errors) in the first query (based on
the linked tables)? That would be the simplest method of preventing data
issues in the Excel sheets propagating into my queries, but I can't seem
to
find anything that works - in particular, none of the error functions seem
to find anything wrong.

If I can't test for the #Num! entries in the linked tables, what other
alternatives do I have to solve this issue?

TIA,

Rob

message

Hi Jana,

Yes, the different machines are all running exactly the same .mdb file
(I
copy the live file to my machine for testing/development - it's
essentially a single-user application), and the links to the various
spreadsheets are exactly the same. The sheets themselves are in a
single
file on a server. The exception is the test copy on my home system,
where
I found a problem with the datatype for the NQRDate in one of the linked
tables; I expect that is the same on the live system, but I will confirm
(and fix) if that's the case. And that may be in a few days, since I
won't be back to the work site probably until next week now.

Moreover, as I said in an my original post, the query itself runs OK on
the machine which throws the error with the dCount expression. And it's
that bit that still has me absolutely perplexed.

- Show quoted text -

Ack, my previous post seems not to have taken!

Rob:

After searching this group for #Num! Excel, I see a ton of posts that
seem to indicate that your best solution is to NOT link to Excel
sheets, but to run a function to import the necessary dates, and deal
with anomalous data during the import. I tried several things to try
to trap for the #Num! with a linked sheet, and just wasn't able to
come up with anything for you. I was, however, able to duplicate
exactly the issue that you're having on my own machine. The problem
is that Excel is designed to allow any type of data entry on a per-
cell level, while Access is expecting consistency on a per-column
basis. Without pulling each date in and testing as you go, I don't
think you'll ever have a fool proof solution.

I admit that I am not 'up' on Jet Version issues, so my input there is
nil.

HTH,
Jana