ACCESS 97



First Query



SELECT A.ACCOUNT,

B.DOCTYPE,

B.DOCNUM,

B.YEARCODE,

B.PERIOD,

B.USERFIELD1,

B.USERFIELD2,

CDate([USERFIELD1]) AS StartDate,

CDate([USERFIELD2]) AS EndDate,

CDate([USERFIELD2])-getDeferredCutoff() AS DaysDeferred,

CDate([USERFIELD2])-CDate([USERFIELD1]) AS PeriodDays,

0-IIf((CDate([USERFIELD2])-CDate([USERFIELD1]))*(CDate([USERFIELD2])-getDeferredCutoff())>0,([VALUE])/(CDate([USERFIELD2])-CDate(
[USERFIELD1]))*(CDate([USERFIELD2])-getDeferredCutoff()),0)

AS DeferredIncomeVal

FROM SQSDBA_M_NAMEACCOUNT AS A INNER JOIN SQSDBA_D_DETAILS AS B ON A.ACCOUNT

= B.ACCOUNT

WHERE IsDate([USERFIELD1])<>False AND

IsDate([USERFIELD2])<>False AND

CDate([USERFIELD1])<=getDeferredCutoff() AND

CDate([USERFIELD2])>getDeferredCutoff()

ORDER BY A.ACCOUNT;



Second Query



SELECT SQSDBA_D_DETAILS.ACCOUNT,

SQSDBA_M_NAMEACCOUNT.ADDRESSNAME,

SQSDBA_D_DETAILS.YEARCODE,

SQSDBA_D_DETAILS.PERIOD,

SQSDBA_D_DETAILS.DOCTYPE,

SQSDBA_D_DETAILS.DESCRIPTION, 0-[VALUE] AS PVALUE,

IIf(IsNull([qryDeferredIncome].[ACCOUNT]),0,[DeferredIncomeVal]) AS

DeferredIncome

FROM (SQSDBA_M_NAMEACCOUNT INNER JOIN SQSDBA_D_DETAILS ON

SQSDBA_M_NAMEACCOUNT.ACCOUNT = SQSDBA_D_DETAILS.ACCOUNT) LEFT JOIN

qryDeferredIncome ON (SQSDBA_D_DETAILS.PERIOD = qryDeferredIncome.PERIOD)

AND

(SQSDBA_D_DETAILS.YEARCODE = qryDeferredIncome.YEARCODE) AND

(SQSDBA_D_DETAILS.DOCNUM = qryDeferredIncome.DOCNUM) AND

(SQSDBA_D_DETAILS.DOCTYPE = qryDeferredIncome.DOCTYPE) AND

(SQSDBA_D_DETAILS.ACCOUNT = qryDeferredIncome.ACCOUNT)

WHERE (((SQSDBA_D_DETAILS.ACCOUNT) Like getCompanyNumber()+"*") AND

((SQSDBA_D_DETAILS.YEARCODE)=getYearForReport()) AND

((SQSDBA_D_DETAILS.NOMINAL) Like getNominalReportCode()))

ORDER BY SQSDBA_D_DETAILS.ACCOUNT;



Parameters passed to query from FORM



This works fine however due to the data I don't get the desired results.

I would get the right results if I also select NOMINAL in the first query

and the an OUTER JOIN on NOMINAL



however I then get Run-time error 3464



data type mismatch in criteria expresion.



On going into debug



Function getDeferredCutoff() As Date  (Date returns data as short date)

    Dim myTable As Recordset

    Dim mystring As Date



    Set myTable = CurrentDb.OpenRecordset("tblReportData")

    mystring = myTable.Fields("DeferredCutOff").Value



    getDeferredCutoff = mystring (mystring returns data as time 00:00:00)



    myTable.Close

End Function



Any suggestions

Gary Fullalove

Hello Gary !

Have you checked the data type in the database? At least I lately had this 

problem having all the info of  date and time with microseconds in 

database and only date in ASP.

Hope you find the way,

Sanna