Type Mis-Match Error, I'm Out of ideas

J

james.eacret

Hello all, this subject has been beat to death, and countless hits on
Google regarding it, but alas I still need some direction.

I am working with an Access database that was converted from 97. I am
using Access 2003 on a Windows XP machine.

There is a report that requires a start date, end date, and title.
After those fields are filled it brings up the rptAnnual report...or at
least should bring it up. While running the query it returns an Error
13 Type Mis-Match. When I click debug it highlights the line
generating the error.

Code:

Private Sub RightPAL()

If Not IsNull(EndingDate) Then
If Me!EndDate = EndingDate Then<-----This is the Highlighted Line
If Not IsNull(ParameterCode) Then
If Me!ParamCode = ParameterCode Then
Exit Sub 'If everything is the same, the PAL stays the same
Else 'If anything has changed, though, run the lookup again
LookUpPAL
End If
Else
LookUpPAL
End If
Else
LookUpPAL
End If
Else
LookUpPAL
End If

End Sub

EndingDate is declared in:

Option Compare Database 'Use database order for string comparisons

Dim PAL, EndingDate, ParameterCode As Variant

So, what I am curious is going from 97 to 2000, something changed and
caused this to no longer work. I have not been successful at locating
what part of the code to change.

Any help would better appreciated very much. If you need me to provide
any more information I will be glad to do so.
 
D

Dirk Goldgar

Hello all, this subject has been beat to death, and countless hits on
Google regarding it, but alas I still need some direction.

I am working with an Access database that was converted from 97. I am
using Access 2003 on a Windows XP machine.

There is a report that requires a start date, end date, and title.
After those fields are filled it brings up the rptAnnual report...or
at least should bring it up. While running the query it returns an
Error 13 Type Mis-Match. When I click debug it highlights the line
generating the error.

Code:

Private Sub RightPAL()

If Not IsNull(EndingDate) Then
If Me!EndDate = EndingDate Then<-----This is the Highlighted Line
If Not IsNull(ParameterCode) Then
If Me!ParamCode = ParameterCode Then
Exit Sub 'If everything is the same, the PAL stays the
same Else 'If anything has changed, though, run the lookup
again LookUpPAL
End If
Else
LookUpPAL
End If
Else
LookUpPAL
End If
Else
LookUpPAL
End If

End Sub

EndingDate is declared in:

Option Compare Database 'Use database order for string comparisons

Dim PAL, EndingDate, ParameterCode As Variant

So, what I am curious is going from 97 to 2000, something changed and
caused this to no longer work. I have not been successful at locating
what part of the code to change.

Any help would better appreciated very much. If you need me to
provide any more information I will be glad to do so.
Click to expand...

What has been assigned to EndingDate, and what value is in Me!EndDate?
I don't know what's wrong, but you might investigate by setting a
breakpoint on the line that raises the error, running the code, and then
when it stops at the breakpoint, entering the following in the Immediate
Window:

?TypeName(EndingDate), EndingDate

?TypeName(Me!EndDate.Value), Me!EndDate.Value
 
J

james.eacret

?TypeName(EndingDate), EndingDate
?TypeName(Me!EndDate.Value), Me!EndDate.Value


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...

Dirk, Thank you for your response. I ran the commands and below are
the results. I ran it in both the 2000 version and 97 version to
compare details.

2000 Version:
?TypeName(EndingDate), EndingDate
Empty
?TypeName(Me!EndDate.Value), Me!EndDate.Value
Byte() 12/31/2005

97 Version:
?TypeName(EndingDate), EndingDate
Empty
?TypeName(Me!EndDate.Value), Me!EndDate.Value
String 12/31/2005

Could the fact that EndDate is being stored as a Byte in 2000 and a
String in 97 be what is causing the problem?

Thanks again.
 
D

Dirk Goldgar

Dirk, Thank you for your response. I ran the commands and below are
the results. I ran it in both the 2000 version and 97 version to
compare details.

2000 Version:
?TypeName(EndingDate), EndingDate
Empty
?TypeName(Me!EndDate.Value), Me!EndDate.Value
Byte() 12/31/2005

97 Version:
?TypeName(EndingDate), EndingDate
Empty
?TypeName(Me!EndDate.Value), Me!EndDate.Value
String 12/31/2005

Could the fact that EndDate is being stored as a Byte in 2000 and a
String in 97 be what is causing the problem?
Click to expand...

That's my guess. But EndDate is not being stored as a Byte; it's being
stored as a Byte *array*. What made that happen? Is this control bound
to a table field? If so, what is the data type of that field in the
table's design view?
 
J

james.eacret

Dirk,

The control is bound to a query I believe.

The report calls a query to generate data based on the given start and
end date.

It apears this way in the query:

Field: BegDate: [Forms]![frmAnnual].[Beginning Date]

Field: EndDate: [Forms]![frmAnnual].[Ending Date]

Field: DateCollected
Table: tblAnavalue
Total: Group By
Sort: Ascending
Criteria: >=[Forms]![frmAnnual].[Beginning Date] And
<=[Forms]![frmAnnual].[Ending Date]

The SQL version of the query is as follows:
SELECT DISTINCTROW tblAnavalue.DateCollected,
[Forms]![frmAnnual].[Beginning Date] AS BegDate,
[Forms]![frmAnnual].[Ending Date] AS EndDate,
TrueValue([tblAnavalue]![Anavalue],[tblAnavalue]![ProjectAnavalue]) AS
Anaval, IIf(TrueQual([DNRQualifier],[ProjectQualifier]) Like
"U*",TrueValue([Anavalue],[ProjectAnavalue])/2,TrueValue([Anavalue],[ProjectAnavalue]))
AS WholeOrHalfAnaval, tblWellDescriptions.WellNameDescription,
tblWellDescriptions.WDNRWellNo,
tlkpParameterDictionary.WDNRParameterCode,
tlkpParameterDictionary.MinIncrease,
tlkpParameterDictionary.ParameterDesc,
tlkpParameterDictionary.WDNRParameterCode,
tlkpParameterDictionary.Type, tblWellDescriptions.WellLocation,
tlkpParameterDictionary.Units, tblWellDescriptions.Unit,
TrueQual([DNRQualifier],[ProjectQualifier]) AS Qual,
tlkpParameterDictionary.ChemicalGroup
FROM tlkpParameterDictionary INNER JOIN (tblWellDescriptions INNER JOIN
tblAnavalue ON tblWellDescriptions.WDNRWellNo = tblAnavalue.WDNRWellNo)
ON tlkpParameterDictionary.WDNRParameterCode =
tblAnavalue.WDNRParameterCode
GROUP BY tblAnavalue.DateCollected, [Forms]![frmAnnual].[Beginning
Date], [Forms]![frmAnnual].[Ending Date],
TrueValue([tblAnavalue]![Anavalue],[tblAnavalue]![ProjectAnavalue]),
IIf(TrueQual([DNRQualifier],[ProjectQualifier]) Like
"U*",TrueValue([Anavalue],[ProjectAnavalue])/2,TrueValue([Anavalue],[ProjectAnavalue])),
tblWellDescriptions.WellNameDescription,
tblWellDescriptions.WDNRWellNo,
tlkpParameterDictionary.WDNRParameterCode,
tlkpParameterDictionary.MinIncrease,
tlkpParameterDictionary.ParameterDesc,
tlkpParameterDictionary.WDNRParameterCode,
tlkpParameterDictionary.Type, tblWellDescriptions.WellLocation,
tlkpParameterDictionary.Units, tblWellDescriptions.Unit,
TrueQual([DNRQualifier],[ProjectQualifier]),
tlkpParameterDictionary.ChemicalGroup
HAVING (((tblAnavalue.DateCollected)>=[Forms]![frmAnnual].[Beginning
Date] And (tblAnavalue.DateCollected)<=[Forms]![frmAnnual].[Ending
Date]) AND ((tlkpParameterDictionary.WDNRParameterCode)<>"00001" And
(tlkpParameterDictionary.WDNRParameterCode)<>"00002" And
(tlkpParameterDictionary.WDNRParameterCode)<>"00003" And
(tlkpParameterDictionary.WDNRParameterCode)<>"00007" And
(tlkpParameterDictionary.WDNRParameterCode)<>"00402") AND
((tblWellDescriptions.WellLocation) Like "1*") AND
((TrueQual([DNRQualifier],[ProjectQualifier]))="=" Or
(TrueQual([DNRQualifier],[ProjectQualifier]))="J")) OR
(((tblAnavalue.DateCollected)>=[Forms]![frmAnnual].[Beginning Date] And
(tblAnavalue.DateCollected)<=[Forms]![frmAnnual].[Ending Date]) AND
((tlkpParameterDictionary.WDNRParameterCode)<>"00001" And
(tlkpParameterDictionary.WDNRParameterCode)<>"00002" And
(tlkpParameterDictionary.WDNRParameterCode)<>"00003" And
(tlkpParameterDictionary.WDNRParameterCode)<>"00007" And
(tlkpParameterDictionary.WDNRParameterCode)<>"00402") AND
((tblWellDescriptions.WellLocation) Like "1*") AND
((TrueQual([DNRQualifier],[ProjectQualifier]))="=" Or
(TrueQual([DNRQualifier],[ProjectQualifier]))="J"))
ORDER BY tblAnavalue.DateCollected;


I think this is what you were asking. I checked the 97 version and it
is the exact same.
So the EndingDate is given in the text field in the report, this is
plugged in to filter the DateCollected field, and EndDate is the Column
that merely lists the EndingDate entered.
 
D

Dirk Goldgar

Dirk,

The control is bound to a query I believe.

The report calls a query to generate data based on the given start and
end date.

It apears this way in the query:

Field: BegDate: [Forms]![frmAnnual].[Beginning Date]

Field: EndDate: [Forms]![frmAnnual].[Ending Date]

Field: DateCollected
Table: tblAnavalue
Total: Group By
Sort: Ascending
Criteria: >=[Forms]![frmAnnual].[Beginning Date] And
<=[Forms]![frmAnnual].[Ending Date]

The SQL version of the query is as follows:
SELECT DISTINCTROW tblAnavalue.DateCollected,
[Forms]![frmAnnual].[Beginning Date] AS BegDate,
[Forms]![frmAnnual].[Ending Date] AS EndDate,
TrueValue([tblAnavalue]![Anavalue],[tblAnavalue]![ProjectAnavalue]) AS
Anaval, IIf(TrueQual([DNRQualifier],[ProjectQualifier]) Like
"U*",TrueValue([Anavalue],[ProjectAnavalue])/2,TrueValue([Anavalue],[Pro
jectAnavalue]))
AS WholeOrHalfAnaval, tblWellDescriptions.WellNameDescription,
tblWellDescriptions.WDNRWellNo,
tlkpParameterDictionary.WDNRParameterCode,
tlkpParameterDictionary.MinIncrease,
tlkpParameterDictionary.ParameterDesc,
tlkpParameterDictionary.WDNRParameterCode,
tlkpParameterDictionary.Type, tblWellDescriptions.WellLocation,
tlkpParameterDictionary.Units, tblWellDescriptions.Unit,
TrueQual([DNRQualifier],[ProjectQualifier]) AS Qual,
tlkpParameterDictionary.ChemicalGroup
FROM tlkpParameterDictionary INNER JOIN (tblWellDescriptions INNER
JOIN tblAnavalue ON tblWellDescriptions.WDNRWellNo =
tblAnavalue.WDNRWellNo) ON tlkpParameterDictionary.WDNRParameterCode =
tblAnavalue.WDNRParameterCode
GROUP BY tblAnavalue.DateCollected, [Forms]![frmAnnual].[Beginning
Date], [Forms]![frmAnnual].[Ending Date],
TrueValue([tblAnavalue]![Anavalue],[tblAnavalue]![ProjectAnavalue]),
IIf(TrueQual([DNRQualifier],[ProjectQualifier]) Like
"U*",TrueValue([Anavalue],[ProjectAnavalue])/2,TrueValue([Anavalue],[Pro
jectAnavalue])),
tblWellDescriptions.WellNameDescription,
tblWellDescriptions.WDNRWellNo,
tlkpParameterDictionary.WDNRParameterCode,
tlkpParameterDictionary.MinIncrease,
tlkpParameterDictionary.ParameterDesc,
tlkpParameterDictionary.WDNRParameterCode,
tlkpParameterDictionary.Type, tblWellDescriptions.WellLocation,
tlkpParameterDictionary.Units, tblWellDescriptions.Unit,
TrueQual([DNRQualifier],[ProjectQualifier]),
tlkpParameterDictionary.ChemicalGroup
HAVING (((tblAnavalue.DateCollected)>=[Forms]![frmAnnual].[Beginning
Date] And (tblAnavalue.DateCollected)<=[Forms]![frmAnnual].[Ending
Date]) AND ((tlkpParameterDictionary.WDNRParameterCode)<>"00001" And
(tlkpParameterDictionary.WDNRParameterCode)<>"00002" And
(tlkpParameterDictionary.WDNRParameterCode)<>"00003" And
(tlkpParameterDictionary.WDNRParameterCode)<>"00007" And
(tlkpParameterDictionary.WDNRParameterCode)<>"00402") AND
((tblWellDescriptions.WellLocation) Like "1*") AND
((TrueQual([DNRQualifier],[ProjectQualifier]))="=" Or
(TrueQual([DNRQualifier],[ProjectQualifier]))="J")) OR
(((tblAnavalue.DateCollected)>=[Forms]![frmAnnual].[Beginning Date]
And (tblAnavalue.DateCollected)<=[Forms]![frmAnnual].[Ending Date])
AND ((tlkpParameterDictionary.WDNRParameterCode)<>"00001" And
(tlkpParameterDictionary.WDNRParameterCode)<>"00002" And
(tlkpParameterDictionary.WDNRParameterCode)<>"00003" And
(tlkpParameterDictionary.WDNRParameterCode)<>"00007" And
(tlkpParameterDictionary.WDNRParameterCode)<>"00402") AND
((tblWellDescriptions.WellLocation) Like "1*") AND
((TrueQual([DNRQualifier],[ProjectQualifier]))="=" Or
(TrueQual([DNRQualifier],[ProjectQualifier]))="J"))
ORDER BY tblAnavalue.DateCollected;


I think this is what you were asking. I checked the 97 version and it
is the exact same.
So the EndingDate is given in the text field in the report, this is
plugged in to filter the DateCollected field, and EndDate is the
Column that merely lists the EndingDate entered.
Click to expand...

I'm using Access 2002, not 2003, but I have read that A2003 is pickier
about determining the data type of parameters (which is what your form
controls [Forms]![frmAnnual].[Beginning Date] and
[Forms]![frmAnnual].[Ending Date] are). If you haven't done so, you may
be able to correct this problem by specifying one of the date formats in
the Format property of each of those controls -- the [Beginning Date]
and [Ending Date] on frmAnnual, I mean.

Best, though, is to explicitly specify the parameter types in the query.
You can do this in the Parameters dialog in query design view: click
menu items Query -> Parameters, put in each of the control references in
the [Forms]![frmAnnual]![controlname] format, and choose data type
Date/Time for them. Or you can modify your SQL directly and add the
parameters declaration:

PARAMETERS
[Forms]![frmAnnual].[Beginning Date] DateTime,
[Forms]![frmAnnual].[Ending Date] DateTime;
SELECT DISTINCTROW
tblAnavalue.DateCollected,
[Forms]![frmAnnual].[Beginning Date] AS BegDate,
[Forms]![frmAnnual].[Ending Date] AS EndDate,
... and so on ...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

OpenQueryDef Causing Errors 5
Building A Filter 3
Type Mismatch generated by code. 2
type mismatch error 13 1
MS Access Report Error Message 2
data type mis match error 2
Error Message - Please Help 2
Code error to query with 6 Combo boxes 2

Top