data type mismatch

[scubadiver]

I have a linked table from an SQL warehouse.

first query

SELECT dbo_Account.OriginalFeeTable, Format([TakeOnDate],"mmm") & "-" &
Format([takeondate],"yy") AS TODate, Val([InitialDebt]/100) AS debt
FROM dbo_Account
WHERE (((dbo_Account.OriginalFeeTable)<>"00") AND
((Val([InitialDebt]/100))<>"") AND ((dbo_Account.TakeOnDate) Between
DateAdd("m",-12,Date()) And Date()));

second query doesn't work:

SELECT Qry_Account.OriginalFeeTable, Qry_Account.TODate,
Count(Qry_Account.TODate) AS CountOfTODate
FROM Qry_Account
GROUP BY Qry_Account.OriginalFeeTable, Qry_Account.TODate;


What is the problem and solution?

thanks

 

[Bob Barrows [MVP]]

I have a linked table from an SQL warehouse.

first query

SELECT dbo_Account.OriginalFeeTable, Format([TakeOnDate],"mmm") & "-"
& Format([takeondate],"yy") AS TODate, Val([InitialDebt]/100) AS debt
FROM dbo_Account
WHERE (((dbo_Account.OriginalFeeTable)<>"00") AND
((Val([InitialDebt]/100))<>"") AND ((dbo_Account.TakeOnDate) Between
DateAdd("m",-12,Date()) And Date()));

second query doesn't work:

SELECT Qry_Account.OriginalFeeTable, Qry_Account.TODate,
Count(Qry_Account.TODate) AS CountOfTODate
FROM Qry_Account
GROUP BY Qry_Account.OriginalFeeTable, Qry_Account.TODate;


What is the problem and solution?

I can't answer without knowing what the symptoms are. Please describe your
symptoms without using generic phrases like "doesn't work".

 

[John Spencer]

First query.

Val([InitialDebt]/100))<>"")

I assume that you are trying to test for a case with InitialDebt is
Null. So a better test would be InitialDebt is Null. Also I would
change the expression slightly to Val(InitialDebt)/100 as Debt.

Now that said I don't see why you even have the first query when you
could just use something like the following which would probably be more
efficient

SELECT OriginalFeeTable
, Format(TakeOnDate,"mmm\-yy") as ToDate
, Count(TakeOnDate) as CountOfToDate
FROM dbo_Account
WHERE OriginalFeeTable <>"00"
AND InitialDebt is not Null
AND TakeOnDate Between DateAdd(m,-12,Date()) and Date()
GROUP BY OriginalFeeTable
, Format(TakeOnDate,"mmm\-yy") as ToDate



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================