Data Type Mismatch Question 2

[iuianj07]

Hello guys,

I have a query below:

SELECT TotalScope_MaxDate.ReportingPeriod, TotalScope_MaxDate.LoanNumber, TotalScope_MaxDate.DateAssigned, TotalScope_MaxDate.RushReqDate, TotalScope_MaxDate.ResolvedIssueDate, TotalScope_MaxDate.MaxDate, IIf([TATRush]=0,[TATDays],[TATRush]) AS TotalTATTime, dateAddNoWeekends(Maximum([TotalScope_MaxDate].[DateAssigned],[TotalScope_MaxDate].[RushReqDate],[TotalScope_MaxDate].[ResolvedIssueDate]),[TotalTATTime]) AS DueDate
FROM (TotalScope_MaxDate LEFT JOIN TATDays ON (TotalScope_MaxDate.MAXDateMonth = TATDays.Month) AND (TotalScope_MaxDate.MAXDateYear = TATDays.Year)) LEFT JOIN TOtalScope_TATRush ON (TotalScope_MaxDate.LoanNumber = TOtalScope_TATRush.LoanNumber) AND (TotalScope_MaxDate.ReportingPeriod = TOtalScope_TATRush.ReportingPeriod)
WHERE (((TotalScope_MaxDate.Withdraw)=0))
GROUP BY TotalScope_MaxDate.ReportingPeriod, TotalScope_MaxDate.LoanNumber, TotalScope_MaxDate.DateAssigned, TotalScope_MaxDate.RushReqDate, TotalScope_MaxDate.ResolvedIssueDate, TotalScope_MaxDate.MaxDate, IIf([TATRush]=0,[TATDays],[TATRush]), TOtalScope_TATRush.TATRush, TATDays.TATDays;

Where DueDate field came from the module below:

Public Function dateAddNoWeekends(dtmDate As Variant, intDaysToAdd As Integer) As Date
  Dim direction As Integer
  Dim intCount As Integer
If IsNumeric(intDaysToAdd) And IsDate(dtmDate) Then
    dateAddNoWeekends = dtmDate
    If intDaysToAdd < 0 Then
     direction = -1
    ElseIf intDaysToAdd > 0 Then
     direction = 1
    Else
     Exit Function
    End If
    Do
        dateAddNoWeekends = dateAddNoWeekends + 1 * (direction)
        If Not (Weekday(dateAddNoWeekends) = vbSaturday Or Weekday(dateAddNoWeekends) = vbSunday) Then
        intCount = intCount + 1
        End If
    Loop Until intCount = Abs(intDaysToAdd)
End If
End Function

The query above is working fine... but when I created another query:

SELECT Switch([job_Tracking].[Signed Off] Is Not Null,"Signed Off",[job_Tracking].[On hold for Issue] Is Not Null,"On Hold for Issue",[job_Tracking].[QCCompleteDate] Is Not Null,"QC Complete",[job_Tracking].[AnalystCompleteDate] Is Not Null,"Ready for Review",[job_Tracking].[DateASsigned] Is Not Null,"Assigned",[job_Tracking].[DateAssigned] Is Null,"Not Assigned") AS Status, Job_Tracking.LoanNumber, Job_Tracking.ReportingPeriod, TotalSCOPE_TAT.DateAssigned, TotalSCOPE_TAT.RushReqDate, TotalSCOPE_TAT.ResolvedIssueDate, TotalSCOPE_TAT.MaxDate, TotalSCOPE_TAT.TotalTATTime, TotalSCOPE_TAT.DueDate
FROM Job_Tracking INNER JOIN TotalSCOPE_TAT ON (Job_Tracking.ReportingPeriod = TotalSCOPE_TAT.ReportingPeriod) AND (Job_Tracking.LoanNumber = TotalSCOPE_TAT.LoanNumber) AND (Job_Tracking.DateAssigned = TotalSCOPE_TAT.DateAssigned)
WHERE (((Job_Tracking.Withdraw)=0))
GROUP BY Switch([job_Tracking].[Signed Off] Is Not Null,"Signed Off",[job_Tracking].[On hold for Issue] Is Not Null,"On Hold for Issue",[job_Tracking].[QCCompleteDate] Is Not Null,"QC Complete",[job_Tracking].[AnalystCompleteDate] Is Not Null,"Ready for Review",[job_Tracking].[DateASsigned] Is Not Null,"Assigned",[job_Tracking].[DateAssigned] Is Null,"Not Assigned"), Job_Tracking.LoanNumber, Job_Tracking.ReportingPeriod, TotalSCOPE_TAT.DateAssigned, TotalSCOPE_TAT.RushReqDate, TotalSCOPE_TAT.ResolvedIssueDate, TotalSCOPE_TAT.MaxDate, TotalSCOPE_TAT.TotalTATTime, TotalSCOPE_TAT.DueDate
HAVING (((Job_Tracking.ReportingPeriod)<"2020*"));

It gives an error message:

Data type mismatch in criteria expression

I am certain though that the link fields have the same data type (Reporting Period, Loan Number nad Date Assigned)

I tried though to remove the DueDate field from the query, and when I run it... the query runs fine... But when I put in the DueDate field again, it gives the Data Type mismatch error again...

Is there any way you could help me out debug this problem?

Any help is greatly appreciated.

Thank you,

 

[iuianj07]

Hello again,

Okay, after trying to debug, I tried to remove all Group By on the query:

GROUP BY Switch([job_Tracking].[Signed Off] Is Not Null,"Signed Off",[job_Tracking].[On hold for Issue] Is Not Null,"On Hold for Issue",[job_Tracking].[QCCompleteDate] Is Not Null,"QC Complete",[job_Tracking].[AnalystCompleteDate] Is Not Null,"Ready for Review",[job_Tracking].[DateASsigned] Is Not Null,"Assigned",[job_Tracking].[DateAssigned] Is Null,"Not Assigned"), Job_Tracking.LoanNumber, Job_Tracking.ReportingPeriod, TotalSCOPE_TAT.DateAssigned, TotalSCOPE_TAT.RushReqDate, TotalSCOPE_TAT.ResolvedIssueDate, TotalSCOPE_TAT.MaxDate, TotalSCOPE_TAT.TotalTATTime, TotalSCOPE_TAT.DueDate
HAVING (((Job_Tracking.ReportingPeriod)<"2020*"));

and the query is now running without an error message.

However, I have a CrossTab Query that is based on the query above... the SQL is:

TRANSFORM Count([VW - Current Loan Status].LoanNumber) AS CountOfLoanNumber
SELECT [VW - Current Loan Status].DueDate
FROM [VW - Current Loan Status]
WHERE ((([VW - Current Loan Status].DueDate)>="#11/17/2010#") AND (([VW - Current Loan Status].PropertyNumber)="SUM") AND (([VW - Current Loan Status].ConsolidatedStatement)=-1)) OR ((([VW - Current Loan Status].DueDate)>="#11/17/2010#") AND (([VW - Current Loan Status].PropertyNumber)<>"SUM") AND (([VW - Current Loan Status].ConsolidatedStatement)=0))
GROUP BY [VW - Current Loan Status].DueDate
PIVOT [VW - Current Loan Status].Status;

and now when I try to run this Cross Tab, it is still giving me the Data Type mismatch error message...

Any help is greatly appreciated...

 

[dhookom]

Is ReportingPeriod actually text? I would think the field type should be numeric.

Your DueDate field should be either delimited with # or ", not both.

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

Hello dhookom,

ReportingPeriod is text... what we have in ReportingPeriod fields are (2009YE, 2010Q1, 2010Q2, 2010Q3 etc...)

As of the delimiter in the DueDate field, I took off either # or " but it still gives the data type mismatch error... I even removed the whole date criteria on the DueDate field and it still gives the error...

Any help is greatly appreciated.

Thank you

 

[PHV]

What about this ?

TRANSFORM Count(LoanNumber) AS CountOfLoanNumber
SELECT DueDate
FROM [VW - Current Loan Status]
WHERE DueDate>=#2010-11-17# AND ((PropertyNumber='SUM' AND ConsolidatedStatement=-1) OR (PropertyNumber<>'SUM' AND ConsolidatedStatement=0))
GROUP BY DueDate
PIVOT Status;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iuianj07]

Hello PHV,

I tried the query you posted, but it still gives the data type mismatch error...

Thanks

 

[PHV]

What are the data type of DueDate,PropertyNumber and ConsolidatedStatement in [VW - Current Loan Status] ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iuianj07]

Hello,

PropertyNumber = TEXT (reason why it's text because we have a SUM record for the PropertyNumber field)

DueDate = dateAddNoWeekends(Maximum([TotalSCOPE_TAT].[DateAssigned],[TotalSCOPE_TAT].[RushReqDate],[TotalSCOPE_TAT].[ResolvedIssueDate]),[TotalTATTime])

ConsolidatedStatement = Yes/No field...

Here is the SQL for VW - Current Loan Status

SELECT Switch([job_Tracking].[Signed Off] Is Not Null,"Signed Off",[job_Tracking].[On hold for Issue] Is Not Null,"On Hold for Issue",[job_Tracking].[QCCompleteDate] Is Not Null,"QC Complete",[job_Tracking].[AnalystCompleteDate] Is Not Null,"Ready for Review",[job_Tracking].[DateASsigned] Is Not Null,"Assigned",[job_Tracking].[DateAssigned] Is Null,"Not Assigned") AS Status, Job_Tracking.LoanNumber, Job_Tracking.ReportingPeriod, Job_Tracking.PropertyNumber, TotalSCOPE_TAT.DateAssigned, TotalSCOPE_TAT.RushReqDate, TotalSCOPE_TAT.ResolvedIssueDate, TotalSCOPE_TAT.MaxDate, TotalSCOPE_TAT.TotalTATTime, dateAddNoWeekends(Maximum([TotalSCOPE_TAT].[DateAssigned],[TotalSCOPE_TAT].[RushReqDate],[TotalSCOPE_TAT].[ResolvedIssueDate]),[TotalTATTime]) AS DueDate, Job_Tracking.ConsolidatedStatement
FROM Job_Tracking INNER JOIN TotalSCOPE_TAT ON (Job_Tracking.DateAssigned = TotalSCOPE_TAT.DateAssigned) AND (Job_Tracking.LoanNumber = TotalSCOPE_TAT.LoanNumber) AND (Job_Tracking.ReportingPeriod = TotalSCOPE_TAT.ReportingPeriod)
WHERE (((Job_Tracking.ReportingPeriod)<"2020*") AND ((Job_Tracking.Withdraw)=0));

Here is the SQL for TotalSCOPE_TAT

SELECT TotalScope_MaxDate.ReportingPeriod, TotalScope_MaxDate.LoanNumber, TotalScope_MaxDate.DateAssigned, TotalScope_MaxDate.RushReqDate, TotalScope_MaxDate.ResolvedIssueDate, TotalScope_MaxDate.MaxDate, IIf([TATRush]=0,[TATDays],[TATRush]) AS TotalTATTime, dateAddNoWeekends(Maximum([TotalScope_MaxDate].[DateAssigned],[TotalScope_MaxDate].[RushReqDate],[TotalScope_MaxDate].[ResolvedIssueDate]),[TotalTATTime]) AS DueDate
FROM (TotalScope_MaxDate LEFT JOIN TATDays ON (TotalScope_MaxDate.MAXDateMonth = TATDays.Month) AND (TotalScope_MaxDate.MAXDateYear = TATDays.Year)) LEFT JOIN TOtalScope_TATRush ON (TotalScope_MaxDate.LoanNumber = TOtalScope_TATRush.LoanNumber) AND (TotalScope_MaxDate.ReportingPeriod = TOtalScope_TATRush.ReportingPeriod)
WHERE (((TotalScope_MaxDate.Withdraw)=0))
GROUP BY TotalScope_MaxDate.ReportingPeriod, TotalScope_MaxDate.LoanNumber, TotalScope_MaxDate.DateAssigned, TotalScope_MaxDate.RushReqDate, TotalScope_MaxDate.ResolvedIssueDate, TotalScope_MaxDate.MaxDate, IIf([TATRush]=0,[TATDays],[TATRush]), TOtalScope_TATRush.TATRush, TATDays.TATDays;

Here is the SQL for TotalScope_MaxDate:

SELECT Job_Tracking.ReportingPeriod, Job_Tracking.LoanNumber, Job_Tracking.ConsolidatedStatement, Job_Tracking.PropertyNumber, Job_Tracking.DateAssigned, JobTracking_PropertyCount.CountOfLoanNumber, Job_Tracking.Analyst, Job_Tracking.QC, Job_Tracking.RushReqDate, Job_Tracking.ResolvedIssueDate, Maximum([DateAssigned],[RushReqDate],[ResolvedIssueDate]) AS MaxDate, Format(Maximum([DateAssigned],[RushReqDate],[ResolvedIssueDate]),"mmm") AS MAXDateMonth, Format(Maximum([DateAssigned],[RushReqDate],[ResolvedIssueDate]),"yyyy") AS MAXDateYear, Job_Tracking.Withdraw
FROM Job_Tracking LEFT JOIN JobTracking_PropertyCount ON (Job_Tracking.ReportingPeriod = JobTracking_PropertyCount.ReportingPeriod) AND (Job_Tracking.LoanNumber = JobTracking_PropertyCount.LoanNumber)
WHERE (((Job_Tracking.Withdraw)=0))
GROUP BY Job_Tracking.ReportingPeriod, Job_Tracking.LoanNumber, Job_Tracking.ConsolidatedStatement, Job_Tracking.PropertyNumber, Job_Tracking.DateAssigned, JobTracking_PropertyCount.CountOfLoanNumber, Job_Tracking.Analyst, Job_Tracking.QC, Job_Tracking.RushReqDate, Job_Tracking.ResolvedIssueDate, Maximum([DateAssigned],[RushReqDate],[ResolvedIssueDate]), Format(Maximum([DateAssigned],[RushReqDate],[ResolvedIssueDate]),"mmm"), Format(Maximum([DateAssigned],[RushReqDate],[ResolvedIssueDate]),"yyyy"), Job_Tracking.Withdraw
HAVING (((Job_Tracking.ReportingPeriod)<"2020*" Or (Job_Tracking.ReportingPeriod)<"2020*"))
ORDER BY Job_Tracking.Analyst, Job_Tracking.QC;

Here is the SQL for TotalScope_TATRush

SELECT Job_Tracking.ReportingPeriod, Job_Tracking.LoanNumber, Job_Tracking.DateAssigned, Job_Tracking.[24HourRush], Job_Tracking.[1-3DayRush], IIf([1-3DayRush]=-1,3,IIf([24HourRush]=-1,1,0)) AS TATRush, Format([DateAssigned],"mmm") AS MonthASsigned, Format([DateAssigned],"ww") AS WeekAssigned
FROM Job_Tracking
GROUP BY Job_Tracking.ReportingPeriod, Job_Tracking.LoanNumber, Job_Tracking.DateAssigned, Job_Tracking.[24HourRush], Job_Tracking.[1-3DayRush], IIf([1-3DayRush]=-1,3,IIf([24HourRush]=-1,1,0)), Format([DateAssigned],"mmm"), Format([DateAssigned],"ww");

TATDays is a table...

Thanks for your help

Any help is greatly appreciated...

 

[dhookom]

What is the data type of TATDays.Year.

You need to look at each query in datasheet view and write down each field that might be referenced in criteria or joins to record if they are left or right aligned in datasheet view. Then compare the joined fields to make sure the joined fields are consistent alignment (data types).

Duane
Hook'D on Access
MS Access MVP