REPOST: Datatype mismatch issue

[Rob Parker]

Hi All,

Apologies in advance for a rather long post. I've labelled this as a
repost, but it's not exactly. It's more a follow-up to an initial post I
made about a week ago, in the m.p.a.formscoding group (with the subject line
"Error 3463 with dCount - some computers only") and it's essentially the
same as my last post to that thread, two days ago, to which there has been
no further response. Since there's been no response there, I'm also
cross-posting to several groups this time around.

My initial problem was that a user of one of my applications gets the error
message "Run Time Error 3464 - Data type mismatch in criteria expression"
from the following line of code:
If DCount("*", "qryDocQualityReviewRemind") > 0 Then

This did not occur for all users; in particular, on my development system,
everything worked fine. And on the machine where the code failed, the query
itself (details below) ran OK. I've found the underlying cause of the
problem, but I'm still puzzled as to why certain things happen, and how to
best solve the problem.

Cause:

An Excel spreadsheet, accessed via a linked table, 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 a set of 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:

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.

The Union query is used in the following Select query
(qryDocQualityReviewRemind - the cause of my initial problem):

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 (how to cope with possible
datatype errors in the linked spreadsheet)?

TIA,

Rob

 

[Chris2]

Hi All,

Apologies in advance for a rather long post. I've labelled this as a
repost, but it's not exactly. It's more a follow-up to an initial post I
made about a week ago, in the m.p.a.formscoding group (with the subject line
"Error 3463 with dCount - some computers only") and it's essentially the
same as my last post to that thread, two days ago, to which there has been
no further response. Since there's been no response there, I'm also
cross-posting to several groups this time around.

My initial problem was that a user of one of my applications gets the error
message "Run Time Error 3464 - Data type mismatch in criteria expression"
from the following line of code:
If DCount("*", "qryDocQualityReviewRemind") > 0 Then

This did not occur for all users; in particular, on my development system,
everything worked fine. And on the machine where the code failed, the query
itself (details below) ran OK. I've found the underlying cause of the
problem, but I'm still puzzled as to why certain things happen, and how to
best solve the problem.

Cause:

An Excel spreadsheet, accessed via a linked table, 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.

Rob Parker,

It almost sounds like you have the solution to your problem. Make sure there are no
spaces in the MS Excel spreadsheets.

I have read through your post, and you do not actually discuss any effort to assure that
spaces are removed, either in MS Excel or in MS Access.

Here is a good question to ask yourself:

How has the field in question from the MS Excel spreadsheet been formatted? Date?
Custom? Text? What?

Open up the Linked Table in Design View and see what datatypes MS Access thinks the column
in question is.

I created a blank MS Access spreadsheet.

I formatted field A to date.

I established a linked table to it in MS Access.

I entered 14-Jul-2007 into record 1 and a space into record 2.

I opened the linked table in MS Access, and sure enough, the second row of the table in
Datasheet View showed #Num!

Answer: As noted above, make sure there are no spaces in the spreadsheet.

Situation:

I am using links to a set of 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:

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));

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 TRUE portion of the IIf statement is:

CVDate([Planned Issue Date])

The FASLE protion of the IIf statement is:

Null

The comparison portion of the IIf directly is:

CVDate([Planned Issue Date]) >
CVDate(nz([Actual Issue Date],0))

When [Actual Issue Date] is Null, this returns 0, and how often is [Planned Issue Date]
greater than 0? In almost all cases, I would imagine.

0 it an integer being compared to a date datatype returned by CVDate([Planned Issue
Date]).

In most cases, that is not a proper comparison.


Sincerely,

Chris O.

 

[Rob Parker]

Hi Chris,

Thanks for the response.

Your suggestion of making sure the spreadsheet contains no spaces would
work, but it's not really practical. The sheets are in a shared Excel file,
and several users edit them; despite my best efforts at educating them, some
do enter spaces. I suppose I could put some code in there to check for
invalid entries and remove them, but it hardly seems worthwhile. What I
plan to do is move from linked tables to importing the spreadsheet data into
all text field tables, then inspecting/converting to the appropriate
datatypes in queries; that way, I can detect datatype errors before they get
into the table as #Num! entries, which I can't trap and which propagate into
the queries, causing the eventual failure.

The comparison of a date value to an integer (0, from an NZ function) does
not cause any error (presumably because the underlying data for a datetime
datatype is actually a number). But again, if there are spaces in those
fields in the spreadsheet, the #Num! entries do cause an error.

Again, thanks for taking time to look at this and offer comments,

Rob

message

Hi All,

Apologies in advance for a rather long post. I've labelled this as a
repost, but it's not exactly. It's more a follow-up to an initial post I
made about a week ago, in the m.p.a.formscoding group (with the subject
line
"Error 3463 with dCount - some computers only") and it's essentially the
same as my last post to that thread, two days ago, to which there has
been
no further response. Since there's been no response there, I'm also
cross-posting to several groups this time around.

My initial problem was that a user of one of my applications gets the
error
message "Run Time Error 3464 - Data type mismatch in criteria expression"
from the following line of code:
If DCount("*", "qryDocQualityReviewRemind") > 0 Then

This did not occur for all users; in particular, on my development
system,
everything worked fine. And on the machine where the code failed, the
query
itself (details below) ran OK. I've found the underlying cause of the
problem, but I'm still puzzled as to why certain things happen, and how
to
best solve the problem.

Cause:

An Excel spreadsheet, accessed via a linked table, 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.

Rob Parker,

It almost sounds like you have the solution to your problem. Make sure
there are no
spaces in the MS Excel spreadsheets.

I have read through your post, and you do not actually discuss any effort
to assure that
spaces are removed, either in MS Excel or in MS Access.

Here is a good question to ask yourself:

How has the field in question from the MS Excel spreadsheet been
formatted? Date?
Custom? Text? What?

Open up the Linked Table in Design View and see what datatypes MS Access
thinks the column
in question is.

I created a blank MS Access spreadsheet.

I formatted field A to date.

I established a linked table to it in MS Access.

I entered 14-Jul-2007 into record 1 and a space into record 2.

I opened the linked table in MS Access, and sure enough, the second row of
the table in
Datasheet View showed #Num!

Answer: As noted above, make sure there are no spaces in the spreadsheet.

Situation:

I am using links to a set of 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:

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));

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 TRUE portion of the IIf statement is:

CVDate([Planned Issue Date])

The FASLE protion of the IIf statement is:

Null

The comparison portion of the IIf directly is:

CVDate([Planned Issue Date]) >
CVDate(nz([Actual Issue Date],0))

When [Actual Issue Date] is Null, this returns 0, and how often is
[Planned Issue Date]
greater than 0? In almost all cases, I would imagine.

0 it an integer being compared to a date datatype returned by
CVDate([Planned Issue
Date]).

In most cases, that is not a proper comparison.


Sincerely,

Chris O.

 

[Chris2]

Hi Chris,

Thanks for the response.

Your suggestion of making sure the spreadsheet contains no spaces would
work, but it's not really practical. The sheets are in a shared Excel file,
and several users edit them; despite my best efforts at educating them, some
do enter spaces.

Rob Parker,

I was thinking of programmatically stopping the entry of spaces.

Something like:

Private Sub Worksheet_Change(ByVal Target As Range)

If Trim(CStr(Target.Value)) = "" Then
Target.Value = ""
End If

End Sub

That works, apparently. But how well it works, or what its implications are, I cannot
say, I have limited experience with Excel and Excel VBA. You might find a better solution
to stopping the entry of spaces in an MS Excel document in an MS Excel newsgroup.

I suppose I could put some code in there to check for invalid
entries and remove them, but it hardly seems worthwhile
Ok.


I suppose I could put some code in there to check for
invalid entries and remove them, but it hardly seems worthwhile. What I
plan to do is move from linked tables to importing the spreadsheet data into

You could add a query "cleanup" layer. The cleanup query would use the linked table, but
would use functions and/or criteria to remove undesireable data, and nothing else. Your
other query would then call on the cleanup layer query.

This would actually be my preferred method.

Again, thanks for taking time to look at this and offer comments,

Rob

You're welcome.


Sincerely,

Chris O.

 

[Rob Parker]

Hi Chris,

You could add a query "cleanup" layer. The cleanup query would use the
linked table, but
would use functions and/or criteria to remove undesireable data, and
nothing else. Your
other query would then call on the cleanup layer query.

This would actually be my preferred method.

<snip>

That would be my preferred method, but as I said, there appears no way to
trap the #Num! entries in the linked table. They simply propagate through
any queries. The expression IsError([FieldName]) does not return True when
the field contains #Num!, it returns #Num!. And I can't find any other
method of trapping the #Num! entries. Seems crazy to me, but that's the way
it is.

I've just been testing the import method, and that work OK. I can import
everything into a table with all text fields, then check using
IsNumeric([FieldName]) before converting; my query contains expressions such
as:
NQRDate: iif(IsNumeric([F6]),CVDate([F6]),Null)

Everything is now hunky-dory.

Thanks again for your comments,

Rob

 

[Chris2]

Hi Chris,


<snip>

That would be my preferred method, but as I said, there appears no way to
trap the #Num! entries in the linked table. They simply propagate through
any queries.

Rob Parker,

When I run the following on the linked table I created, I get the #Num! errors caused by
the spaces in my test MS Excel spreadsheet.


SELECT YourDateColumnNameHere
FROM YourLinkedTable


When I run:

SELECT YourDateColumnNameHere
FROM YourLinkedTable
WHERE IsDate(YourDateColumnNameHere)


The rows reporting #Num! are removed.

------------------------

I am glad to hear your other solution appears to be working.


Sincerely,

Chris O.

 

[Rob Parker]

Hi Chris,

Now that you mention it, I remember seeing this issue somewhere else some
time ago. As you say, checking for IsDate(FieldName) in the Where clause of
a query does work. The strange thing, though, is that
Iif(IsDate(FieldName),FieldName,Null) as an expression in the query does not
remove the #Num! entries.

Rob