smtazulislam
Member
- Local time
- Today, 17:46
- Joined
- Mar 27, 2020
- Messages
- 808
Hello all,
this is my subform sql and its work perfectly.
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...
I try "d" and "*" but not debug.
I dont know why its not working. Need your help. Advance thanks..
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 dont know why its not working. Need your help. Advance thanks..