Problem with DateSerial

  • Thread starter Thread starter Leslie W.
  • Start date Start date
L

Leslie W.

I'm having trouble with a SQL Script I'm working with.

SELECT tblDetails.Fund, tblDetails.Budget, tblDetails.RstrCat,
tblDetails.RstrCatName, tblDetails.ObjectCode, tblDetails.CobjDesc,
tblDetails.FiscalYear, tblDetails.Amount, tblDetails.ReportMonth,
DateSerial(IIf([ReportMonth]>6,Left([FiscalYear],4),Right([FiscalYear],4)),[ReportMonth]+1,0)
AS FYDate, tblDetails.RstrCatGroup, tblDetails.CostCenter,
tblDetails.DescDTL, tblDetails.IndcFnA, tblDetails.RecType
FROM tblDetails
WHERE
(((DateSerial(IIf([ReportMonth]>6,Left([FiscalYear],4),Right([FiscalYear],4)),[ReportMonth]+1,0))>=#7/1/2008#)
AND ((tblDetails.IndcFnA)<>"I") AND ((tblDetails.RecType)="IACT"));


I receive the error "Data type mismatch in criteria expression," so I think
there's a problem somewhere with the DateSerial script because when I remove
the criteria ">=#7/1/2007#", the query runs fine, and I can't figure out what
happened.

In the raw table this data comes from, the [ReportMonth] field is formatted
as a number.

Any advice would be appreciated. Thanks in advance!

Leslie
 
Leslie

"Data type mismatch" implies, well, a wrong data type.

What data types are [ReportMonth] and [FiscalYear]?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
ReportMonth is a Number, FiscalYear is Text.

Thanks!
Leslie

Jeff Boyce said:
Leslie

"Data type mismatch" implies, well, a wrong data type.

What data types are [ReportMonth] and [FiscalYear]?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Leslie W. said:
I'm having trouble with a SQL Script I'm working with.

SELECT tblDetails.Fund, tblDetails.Budget, tblDetails.RstrCat,
tblDetails.RstrCatName, tblDetails.ObjectCode, tblDetails.CobjDesc,
tblDetails.FiscalYear, tblDetails.Amount, tblDetails.ReportMonth,
DateSerial(IIf([ReportMonth]>6,Left([FiscalYear],4),Right([FiscalYear],4)),[ReportMonth]+1,0)
AS FYDate, tblDetails.RstrCatGroup, tblDetails.CostCenter,
tblDetails.DescDTL, tblDetails.IndcFnA, tblDetails.RecType
FROM tblDetails
WHERE
(((DateSerial(IIf([ReportMonth]>6,Left([FiscalYear],4),Right([FiscalYear],4)),[ReportMonth]+1,0))>=#7/1/2008#)
AND ((tblDetails.IndcFnA)<>"I") AND ((tblDetails.RecType)="IACT"));


I receive the error "Data type mismatch in criteria expression," so I
think
there's a problem somewhere with the DateSerial script because when I
remove
the criteria ">=#7/1/2007#", the query runs fine, and I can't figure out
what
happened.

In the raw table this data comes from, the [ReportMonth] field is
formatted
as a number.

Any advice would be appreciated. Thanks in advance!

Leslie
Click to expand...
Click to expand...
 
you may need to cast fiscalyear as a int because date serial seems to
need ints not strings for itsself to work

try

DateSerial(IIf([ReportMonth]>6,cint(Left([FiscalYear],
4)),cint(Right([FiscalYear],4­))),[ReportMonth]+1,0))>=#7/1/2008#)

for your date serial function

though this is just a guess

Regards
Kelvan
 
If you have any nulls in the field you are using to build the date, it
will generate an error. And when you attempt to apply criteria to a
calculated field that returns an error, you will run into problems with
the query - an error message - and then failure.


You might try the following and see if it works for you.

IIF(IsNumeric(ReportMonth) AND isNumeric(Left(FiscalYear,4)) AND
IsNumeric(Right(FiscalYear,4)), DateSerial(IIf([ReportMonth]>6,
Left([FiscalYear],4),Right([FiscalYear],4)), [ReportMonth]+1,0),Null)

That might still have problems calculating correctly in every case, but
it should not cause a DataType Mismatch error.

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