Solved DateDiff function not work in the SQL (1 Viewer)

smtazulislam

Member
Local time
Today, 07:43
Joined
Mar 27, 2020
Messages
806
Hello all,
this is my subform sql and its work perfectly.
Code:
SELECT AbDaySN([Forms]![frmPayrollHistory]![txtEmployeeID],[AbsetdayID]) AS SN, tblAbsentDates.AbsetdayID, tblAbsentDates.EmployeeID, tblAbsentDates.PayHdrID, tblAbsentDates.AbsentdayType, LtblAbsentdayTypes.Deduct, tblAbsentDates.StartDate, tblAbsentDates.EndDate, DateDiff("d",[StartDate],[EndDate])+1 AS [Days Absent], tblAbsentDates.Total, IIf([Deduct]=-1,[Total],0) AS [AT], tblAbsentDates.Notes
FROM LtblAbsentdayTypes INNER JOIN tblAbsentDates ON LtblAbsentdayTypes.AbsentdayType = tblAbsentDates.AbsentdayType;

But when I put in the vba sql then not work. there have error DateDiff formula.
DateDiff("d",[StartDate],[EndDate])+1 AS [Days Absent]
I try like...

Code:
SQL = "SELECT AbDaySN([Forms]![frmPayrollMainForm]![txtEmployeeID],[AbsetdayID]) AS SN, tblAbsentDates.AbsetdayID, tblAbsentDates.EmployeeID, tblAbsentDates.StartDate, tblAbsentDates.EndDate, tblAbsentDates.AbsentdayType, LtblAbsentdayTypes.Deduct, DateDiff("d",[StartDate],[EndDate])+1 AS [Days Absent], tblAbsentDates.Total, IIf([Deduct]=-1,[Total],0) AS [AT], tblAbsentDates.Notes"
    SQL = SQL & " FROM LtblAbsentdayTypes INNER JOIN tblAbsentDates ON LtblAbsentdayTypes.AbsentdayType = tblAbsentDates.AbsentdayType"
    SQL = SQL & " FROM tblEmployee INNER JOIN tblAbsentDates ON tblEmployee.EmployeeID = tblAbsentDates.EmployeeID"
    SQL = SQL & " WHERE (year(StartDate) & format(month(StartDate), '00')<='" & refDate & "')"
    SQL = SQL & " AND (year(StartDate) & format(month(StartDate), '00')>='" & refDate & "')"
I try "d" and "*" but not debug.

I dont know why its not working. Need your help. Advance thanks..
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:43
Joined
May 7, 2009
Messages
19,169
use single Quote:

DateDiff('d',[StartDate],[EndDate])+1 AS [Days Absent]
 

Users who are viewing this thread

Top Bottom