Problem with DateSerial

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
 
J

Jeff Boyce

Leslie

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

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

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
L

Leslie W.

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...
 
L

Lord Kelvan

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
 
J

John Spencer

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
'====================================================
 
Top