Solved Data Type Mismatch in Criteria Expression (1 Viewer)

[pbuethe]

My source table is tbl011_Level1_8. CaseNbr, ReviewLevel, ReviewerID, ReviewDate are all of type Text. Column is type Number. There is no primary key.

My destination table is tblRevResults. CaseNbr, ReviewLevel, and Reviewer are of type Text. ReviewDate is of type Date/Time. These four fields together are the PK.

I made the query qrytbl011_Level1_8_CDate based on the source table, to turn the ReviewDate into a date type by using CDate, i.e. RevDate: CDate([tbl011_Level1_8].[ReviewDate]) .I thought I would be able to link on all four fields that way.

My append query, which gives me “data type mismatch in criteria expression” error, is:

INSERT INTO tblRevResults ( CaseNbr, ReviewLevel, ReviewType, Reviewer, ReviewDate, ADM, LOS, DRG, DISCH, NYPORTS, QUAL, DOC, ALC, TRAN, MORT, [COMP], SAE, COSTO, UpdateDate, [User] )

SELECT qrytbl011_Level1_8_CDate.CaseNbr, qrytbl011_Level1_8_CDate.ReviewLevel, qrytbl011_Level1_8_CDate.ReviewType, qrytbl011_Level1_8_CDate.ReviewerID, qrytbl011_Level1_8_CDate.RevDate, IIf(Len([Admission])=1,[Admission],IIf([Admission] In ("Reversed","RV"),"RV",Left([Admission],1))) AS ADM, IIf(Len([LengStay])=1,[LengStay],IIf([LengStay] In ("Reversed","RV"),"RV",Left([LengStay],1))) AS LOS, IIf(Len([DRGRev])=1,[DRGRev],IIf([DRGRev] In ("Reversed","RV"),"RV",Left([DRGRev],1))) AS DRG, IIf(Len([Discharge])=1,[Discharge],IIf([Discharge] In ("Reversed","RV"),"RV",Left([Discharge],1))) AS DISCH, IIf(Len([qrytbl011_Level1_8_CDate].[NYPORTS])=1,[qrytbl011_Level1_8_CDate].[NYPORTS],IIf([qrytbl011_Level1_8_CDate].[NYPORTS] In ("Reversed","RV"),"RV",Left([qrytbl011_Level1_8_CDate].[NYPORTS],1))) AS NYP, IIf(Len([Quality])=1,[Quality],IIf([Quality] In ("Reversed","RV"),"RV",Left([Quality],1))) AS QUAL, IIf(Len([Documentation])=1,[Documentation],IIf([Documentation] In ("Reversed","RV"),"RV",Left([Documentation],1))) AS DOC, IIf(Len([qrytbl011_Level1_8_CDate].[ALC])=1,[qrytbl011_Level1_8_CDate].[ALC],IIf([qrytbl011_Level1_8_CDate].[ALC] In ("Reversed","RV"),"RV",Left([qrytbl011_Level1_8_CDate].[ALC],1))) AS ALCRev, IIf(Len([Transfer])=1,[Transfer],IIf([Transfer] In ("Reversed","RV"),"RV",Left([Transfer],1))) AS TRAN, IIf(Len([Mortality])=1,[Mortality],IIf([Mortality] In ("Reversed","RV"),"RV",Left([Mortality],1))) AS MORT, IIf(Len([Complication])=1,[Complication],IIf([Complication] In ("Reversed","RV"),"RV",Left([Complication],1))) AS [COMP], IIf(Len([SAEReview])=1,[SAEReview],IIf([SAEReview] In ("Reversed","RV"),"RV",Left([SAEReview],1))) AS SAE, IIf(Len([CostOutlier])=1,[CostOutlier],IIf([CostOutlier] In ("Reversed","RV"),"RV",Left([CostOutlier],1))) AS COSTO, Date() AS UpdateDate, CurrentUser() AS [User]

FROM qrytbl011_Level1_8_CDate LEFT JOIN tblRevResults ON (qrytbl011_Level1_8_CDate.RevDate = tblRevResults.ReviewDate) AND (qrytbl011_Level1_8_CDate.ReviewerID = tblRevResults.Reviewer) AND (qrytbl011_Level1_8_CDate.ReviewLevel = tblRevResults.ReviewLevel) AND (qrytbl011_Level1_8_CDate.CaseNbr = tblRevResults.CaseNbr)

WHERE (((qrytbl011_Level1_8_CDate.CaseNbr) Is Not Null) AND ((qrytbl011_Level1_8_CDate.ReviewLevel) Is Not Null) AND ((qrytbl011_Level1_8_CDate.ReviewerID) Is Not Null) AND ((qrytbl011_Level1_8_CDate.RevDate) Is Not Null) AND ((qrytbl011_Level1_8_CDate.Column)>1) AND ((tblRevResults.CaseNbr)="" Or (tblRevResults.CaseNbr) Is Null) AND ((tblRevResults.ReviewLevel)="" Or (tblRevResults.ReviewLevel) Is Null) AND ((tblRevResults.Reviewer)="" Or (tblRevResults.Reviewer) Is Null) AND ((tblRevResults.ReviewDate) Is Null)); ((tblRevResults.Reviewer)="" Or (tblRevResults.Reviewer) Is Null) AND ((tblRevResults.ReviewDate) Is Null));

Thanks for your help.

 

[Ranman256]

you cant join a string field to a number field. (even tho they are the same value).
you CAN build an intermediate query ,Q1, to convert the field:
select Cstr(field1) as MyString, CInt(field2) as MyNum from table

THEN use Q1 to join to the other table now that the field types match.

 

[pbuethe]

I am not joining a string field to a number field. I only mentioned the number field since I am using it in criteria. My query qrytbl011_Level1_8_CDate is to convert the ReviewDate in tbl011_Level1_8 (text) to RevDate (Date) so I can link it to ReviewDate (Date) in tblRevResults.

qrytbl011_Level1_8_CDate:
SELECT tbl011_Level1_8.MaxOfID, tbl011_Level1_8.CaseNbr, tbl011_Level1_8.Column, tbl011_Level1_8.ReviewLevel, tbl011_Level1_8.ReviewType, tbl011_Level1_8.ReviewerID, CDate([tbl011_Level1_8].[ReviewDate]) AS RevDate, tbl011_Level1_8.StartTime1, tbl011_Level1_8.StopTime1, tbl011_Level1_8.StartTime2, tbl011_Level1_8.StopTime2, tbl011_Level1_8.Admission, tbl011_Level1_8.LengStay, tbl011_Level1_8.DRGRev, tbl011_Level1_8.Discharge, tbl011_Level1_8.NYPORTS, tbl011_Level1_8.Quality, tbl011_Level1_8.Documentation, tbl011_Level1_8.ALC, tbl011_Level1_8.Transfer, tbl011_Level1_8.Mortality, tbl011_Level1_8.Complication, tbl011_Level1_8.SAEReview, tbl011_Level1_8.CostOutlier, tbl011_Level1_8.Specialty1, tbl011_Level1_8.Specialty2, tbl011_Level1_8.Specialty3, tbl011_Level1_8.Specialty4
FROM tbl011_Level1_8;

 

[Gasman]

Would you not need the CDate() function then?

You appear to just be trying to put a text value into a date field, if I understand you correctly?

 

[pbuethe]

Right. So I am using CDate(). So why wouldn't this work? Or is something wrong with the criteria?

 

[Gasman]

Right. So I am using CDate(). So why wouldn't this work? Or is something wrong with the criteria?

Really?, where?

qrytbl011_Level1_8_CDate.RevDate

 

[MajP]

INSERT INTO tblrevresults
            (
                        casenbr,
                        reviewlevel,
                        reviewtype,
                        reviewer,
                        reviewdate,
                        adm,
                        los,
                        drg,
                        disch,
                        nyports,
                        qual,
                        doc,
                        alc,
                        tran,
                        mort,
                        [comp],
                        sae,
                        costo,
                        updatedate,
                        [user]
            )
SELECT    qrytbl011_level1_8_cdate.casenbr,
          qrytbl011_level1_8_cdate.reviewlevel,
          qrytbl011_level1_8_cdate.reviewtype,
          qrytbl011_level1_8_cdate.reviewerid,
          qrytbl011_level1_8_cdate.revdate,
          Iif(
                    Len([admission])=1,[admission],
                    Iif(
                              [admission] IN ("reversed",
                                              "rv"),"rv",LEFT([admission],1))) AS ADM,
          Iif(
                    Len([lengstay])=1,[lengstay],
                    Iif(
                              [lengstay] IN ("reversed",
                                             "rv"),"rv",LEFT([lengstay],1))) AS LOS,
          Iif(
                    Len([drgrev])=1,[drgrev],
                    Iif(
                              [drgrev] IN ("reversed",
                                           "rv"),"rv",LEFT([drgrev],1))) AS DRG,
          Iif(
                    Len([discharge])=1,[discharge],
                    Iif(
                              [discharge] IN ("reversed",
                                              "rv"),"rv",LEFT([discharge],1))) AS DISCH,
          Iif(
                    Len([qrytbl011_level1_8_cdate].[nyports])=1,[qrytbl011_level1_8_cdate].[nyports],
                    Iif(
                              [qrytbl011_level1_8_cdate].[nyports] IN ("reversed",
                                                                       "rv"),"rv",LEFT([qrytbl011_level1_8_cdate].[nyports],1))) AS NYP,
          Iif(
                    Len([quality])=1,[quality],
                    Iif(
                              [quality] IN ("reversed",
                                            "rv"),"rv",LEFT([quality],1))) AS QUAL,
          Iif(
                    Len([documentation])=1,[documentation],
                    Iif(
                              [documentation] IN ("reversed",
                                                  "rv"),"rv",LEFT([documentation],1))) AS DOC,
          Iif(
                    Len([qrytbl011_level1_8_cdate].[alc])=1,[qrytbl011_level1_8_cdate].[alc],
                    Iif(
                              [qrytbl011_level1_8_cdate].[alc] IN ("reversed",
                                                                   "rv"),"rv",LEFT([qrytbl011_level1_8_cdate].[alc],1))) AS ALCRev,
          Iif(
                    Len([transfer])=1,[transfer],
                    Iif(
                              [transfer] IN ("reversed",
                                             "rv"),"rv",LEFT([transfer],1))) AS TRAN,
          Iif(
                    Len([mortality])=1,[mortality],
                    Iif(
                              [mortality] IN ("reversed",
                                              "rv"),"rv",LEFT([mortality],1))) AS MORT,
          Iif(
                    Len([complication])=1,[complication],
                    Iif(
                              [complication] IN ("reversed",
                                                 "rv"),"rv",LEFT([complication],1))) AS [COMP],
          Iif(
                    Len([saereview])=1,[saereview],
                    Iif(
                              [saereview] IN ("reversed",
                                              "rv"),"rv",LEFT([saereview],1))) AS SAE,
          Iif(
                    Len([costoutlier])=1,[costoutlier],
                    Iif(
                              [costoutlier] IN ("reversed",
                                                "rv"),"rv",LEFT([costoutlier],1))) AS COSTO,
          DATE()                                                                   AS UpdateDate,
          Currentuser()                                                            AS [User]
FROM      qrytbl011_level1_8_cdate
LEFT JOIN tblrevresults
ON        (
                    qrytbl011_level1_8_cdate.revdate = tblrevresults.reviewdate)
AND       (
                    qrytbl011_level1_8_cdate.reviewerid = tblrevresults.reviewer)
AND       (
                    qrytbl011_level1_8_cdate.reviewlevel = tblrevresults.reviewlevel)
AND       (
                    qrytbl011_level1_8_cdate.casenbr = tblrevresults.casenbr)
WHERE     (((
                                        qrytbl011_level1_8_cdate.casenbr) IS NOT NULL)
          AND       ((
                                        qrytbl011_level1_8_cdate.reviewlevel) IS NOT NULL)
          AND       ((
                                        qrytbl011_level1_8_cdate.reviewerid) IS NOT NULL)
          AND       ((
                                        qrytbl011_level1_8_cdate.revdate) IS NOT NULL)
          AND       ((
                                        qrytbl011_level1_8_cdate.column)>1)
          AND       ((
                                        tblrevresults.casenbr)=""
                    OR        (
                                        tblrevresults.casenbr) IS NULL)
          AND       ((
                                        tblrevresults.reviewlevel)=""
                    OR        (
                                        tblrevresults.reviewlevel) IS NULL)
          AND       ((
                                        tblrevresults.reviewer)=""
                    OR        (
                                        tblrevresults.reviewer) IS NULL)
          AND       ((
                                        tblrevresults.reviewdate) IS NULL));((tblrevresults.reviewer)="" OR
(
  tblrevresults.reviewer
)
IS NULL) AND
(
  (
    tblrevresults.reviewdate
  )
  IS NULL
)
);

 

[MajP]

This is one of those cases where I would do a parameter insert. That is so complicated, I doubt I could debug that.

Public Function ParamInsert(TableName As String, TheFields As String, ParamArray TheValues() As Variant) As String
  Dim qdf As QueryDef
  Dim i As Integer
  Dim MyParams As New Collection
  Dim strMyParams As String
  Dim strSql As String
  For i = 0 To UBound(TheValues)
    MyParams.Add "Param" & i, "Param" & i
    If strMyParams = "" Then
      strMyParams = "[" & MyParams(i + 1) & "]"
    Else
      strMyParams = strMyParams & ", " & "[" & MyParams(i + 1) & "]"
    End If
  Next i
  strSql = "INSERT INTO " & TableName & " " & TheFields & " VALUES ( " & strMyParams & ")"
  ParamInsert = strSql
  Set qdf = CurrentDb.CreateQueryDef("TempQuery", strSql)
   For i = 0 To UBound(TheValues)
    qdf.Parameters(i) = TheValues(i)
  Next i
  qdf.Execute
  CurrentDb.QueryDefs.Delete ("tempquery")
End Function
Public Sub TestParamInsert()
  Dim TheFields As String
  Dim FirstName As Variant
  Dim LastName As Variant
  FirstName = Null
  LastName = "Smith"
  TheFields = InsertFields("FirstName", "LastName", "OrderID", "OrderDate")
  ParamInsert "MyTable", TheFields, FirstName, LastName, 1, Date
End Sub

This way you can resolve all of those parameters and debug them in code to ensure they are correct format. I think that would be much easier.

Also use this module to help with delimiting

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

 

[Gasman]

@MajP
Can we have the InsertFields() function as well please?

TIA

 

[MajP]

I take back what I said. The query looks complex, but it is really pretty simple and could be easily debugged by building the select statement and seeing if it runs. So please disregard my previous.
I think in order to debug this you can assume the iifs and joins are working so this is where you focus it would be simply below. Except I do see a problem in the join which I left in

INSERT INTO tblrevresults
            (
                        casenbr,
                        reviewlevel,
                        reviewtype,
                        reviewer,
                        reviewdate, 'DATE
                        updatedate, 'DATE
                        [user]
            )
SELECT    qrytbl011_level1_8_cdate.casenbr, 'Text
          qrytbl011_level1_8_cdate.reviewlevel, 'Text
          qrytbl011_level1_8_cdate.reviewtype, 'Text
          qrytbl011_level1_8_cdate.reviewerid,  'Text
          qrytbl011_level1_8_cdate.revdate,      'DATE converted
           DATE()   AS UpdateDate, 'DATE
          Currentuser()  AS [User]
FROM      qrytbl011_level1_8_cdate
LEFT JOIN tblrevresults
tblrevresults.reviewdate) IS NULL));((tblrevresults.reviewer)="" OR
;

I do not know if this is a typo, but I see this line

tblrevresults.reviewdate) IS NULL));((tblrevresults.reviewer)="" OR

I do not know how that would even save. So may be part of your problem, but I assume you built this off of the QDE, so I am guessing maybe that is a typo.

So before going down rabbit holes are you convinced the date fields are the type and not something else can you verify all the source and desitination data types in the code snippet. When I see things like CaseNbr and told it is text I start to have questions.

 

[MajP]

Now I do have an issue with the iifs. Seems overly complext

          Iif(Len([admission])=1,[admission],Iif([admission] IN ("reversed","rv"),"rv",LEFT([admission],1))) AS ADM

Is that not simply

          Iif([admission] = "reversed","rv",LEFT([admission],1) AS ADM

 

[pbuethe]

Now I do have an issue with the iifs. Seems overly complext

          Iif(Len([admission])=1,[admission],Iif([admission] IN ("reversed","rv"),"rv",LEFT([admission],1))) AS ADM

Is that not simply

          Iif([admission] = "reversed","rv",LEFT([admission],1) AS ADM

Good suggestion, although your revision is not quite right . It should be:

Iif([Admission] IN ("Reversed","RV"), "RV", Left([Admission],1) AS ADM

I will revise the query accordingly, then continue working on the problem.

 

[MajP]

Did you look at the semicolon in the middle of your code. Is that real or just a typo?

AND ((tblRevResults.ReviewDate) Is Null)); ((tblRevResults.Reviewer)="" Or

 

[pbuethe]

Did you look at the semicolon in the middle of your code. Is that real or just a typo?

AND ((tblRevResults.ReviewDate) Is Null)); ((tblRevResults.Reviewer)="" Or

Just a typo, there is nothing after the semicolon. Thanks.

 

[pbuethe]

I discussed it with my supervisor and the creator of the source table. The result is that that person is going to make the date field into date/time type, which should help me a lot. We also discovered some other revisions that are needed, so it was very productive. I will keep you posted on what happens.

 

[MajP]

Are you confident the date fields dare the issue. There are a other things that look suspicious just by their names. If you are sure it is the date then OK, but do not get too focused without looking at the bigger picture.
ReviewID is this really text, I normally would have a numeric?
Is caseNbr, level, type really text?

 

[pbuethe]

No, I am not completely sure the date is the issue, but I thought it was the most likely. The ReviewerID and CaseNbr look like numbers, but the data type is Text.

 

[MajP]

Can you do a small test with just these fields and a minimum join. Then remove fields 1 by 1. Start with the date fields

INSERT INTO tblrevresults
            (
                        casenbr,
                        reviewlevel,
                        reviewtype,
                        reviewer,
                        reviewdate, 'DATE
                        updatedate, 'DATE
                        [user]
            )
SELECT    qrytbl011_level1_8_cdate.casenbr, 'Text
          qrytbl011_level1_8_cdate.reviewlevel, 'Text
          qrytbl011_level1_8_cdate.reviewtype, 'Text
          qrytbl011_level1_8_cdate.reviewerid,  'Text
          qrytbl011_level1_8_cdate.revdate,      'DATE converted
           DATE()   AS UpdateDate, 'DATE
          Currentuser()  AS [User]
FROM      qrytbl011_level1_8_cdate
LEFT JOIN tblrevresults

Another thing that could be happening is that your source is all text fields. It is possible (I do not know the source) that the "empty" fields are not null but in fact empty strings. That will fail inserted into a numeric.
However that would not likely give a mismatch but a failure to insert x records message.

 

[pbuethe]

Once the ReviewDate in the source table was changed to a Date/Time data type, I was able to easily join on that field; I added Time fields to the query which were also changed from Text to Date/Time; I didn't have to go through contortions and the query worked perfectly as follows:

INSERT INTO tblRevResults ( CaseNbr, ReviewLevel, ReviewType, Reviewer, ReviewDate, StartTime1, StopTime1, StartTime2, StopTime2, ADM, LOS, DRG, DISCH, NYPORTS, QUAL, DOC, ALC, TRAN, MORT, [COMP], SAE, COSTO, UpdateDate, [User] )
SELECT tbl011_Level1_8.CaseNbr, tbl011_Level1_8.ReviewLevel, tbl011_Level1_8.ReviewType, tbl011_Level1_8.ReviewerID, tbl011_Level1_8.ReviewDate, tbl011_Level1_8.StartTime1, tbl011_Level1_8.StopTime1, tbl011_Level1_8.StartTime2, tbl011_Level1_8.StopTime2, IIf([Admission] In ("Reversed","RV"),"RV",Left([Admission],1)) AS ADM, IIf([LengStay] In ("Reversed","RV"),"RV",Left([LengStay],1)) AS LOS, IIf([DRGRev] In ("Reversed","RV"),"RV",Left([DRGRev],1)) AS DRG, IIf([Discharge] In ("Reversed","RV"),"RV",Left([Discharge],1)) AS DISCH, IIf([tbl011_Level1_8].[NYPORTS] In ("Reversed","RV"),"RV",Left([tbl011_Level1_8].[NYPORTS],1)) AS NYP, IIf([Quality] In ("Reversed","RV"),"RV",Left([Quality],1)) AS QUAL, IIf([Documentation] In ("Reversed","RV"),"RV",Left([Documentation],1)) AS DOC, IIf([tbl011_Level1_8].[ALC] In ("Reversed","RV"),"RV",Left([tbl011_Level1_8].[ALC],1)) AS ALCRev, IIf([Transfer] In ("Reversed","RV"),"RV",Left([Transfer],1)) AS TRAN, IIf([Mortality] In ("Reversed","RV"),"RV",Left([Mortality],1)) AS MORT, IIf([Complication] In ("Reversed","RV"),"RV",Left([Complication],1)) AS [COMP], IIf([SAEReview] In ("Reversed","RV"),"RV",Left([SAEReview],1)) AS SAE, IIf([CostOutlier] In ("Reversed","RV"),"RV",Left([CostOutlier],1)) AS COSTO, Date() AS UpdateDate, CurrentUser() AS [User]
FROM tbl011_Level1_8 LEFT JOIN tblRevResults ON (tbl011_Level1_8.ReviewDate = tblRevResults.ReviewDate) AND (tbl011_Level1_8.ReviewerID = tblRevResults.Reviewer) AND (tbl011_Level1_8.ReviewLevel = tblRevResults.ReviewLevel) AND (tbl011_Level1_8.CaseNbr = tblRevResults.CaseNbr)
WHERE (((tbl011_Level1_8.CaseNbr) Is Not Null) AND ((tbl011_Level1_8.ReviewLevel) Is Not Null) AND ((tbl011_Level1_8.ReviewerID) Is Not Null) AND ((tbl011_Level1_8.ReviewDate) Is Not Null) AND ((tbl011_Level1_8.Column)>1) AND ((tblRevResults.CaseNbr) Is Null) AND ((tblRevResults.ReviewLevel) Is Null) AND ((tblRevResults.Reviewer) Is Null) AND ((tblRevResults.ReviewDate) Is Null));

 

[Gasman]

So a CDate() on that RevDate would have done the same task?