Date serial comparison error

[mahmed1]

Hi All

Hope you all are well

I have a column that stores leavingdate but it’s not in a date format

ie 20210115 (15/01/2021) some rows will be blank and others will have leaving dates in there

what I want to do is get a count of how many of the leave dates are future dates (ie > today’s date but I keep getting an error or type error
I also need to take off the hours where the same condition is met...

my attempt

Count Greater than today

Count(DateSerial(Left(tblData.LeavingDate,4),Mid(tblData.LeavingDate,2),Right(tblData.LeavingDate,2))>Date())

Sum Hours Greater than today

Sum(IIf(DateSerial(Left(tblData.LeavingDate,4),Mid(tblData.LeavingDate,5,2),Right(tblData.LeavingDate,2))>Date(), tblData.Hours, 0))

where am I going wrong

 

[xenou]

string dates in yyyymmdd can be compared as text. So you can play around with something like:

PSEUDOCODE

if (LeavingDate > format(date(), "yyyymmdd")) then
    '//Is Future
else
    //Is not future

In regards to what you were doing wrong, probably the dateserial function requires integer or numeric arguments. You are passing strings (that look like numbers). That's my guess anyway.

 

[mahmed1]

Thank you

i will change to this syntax - is this correct?


Count(if (tblData.LeavingDate > format(date(), "yyyymmdd")) then 1,0))

and

Sum(if (tblData.LeavingDate > format(date(), "yyyymmdd")) then tblData.Hours, 0))

if there is no dates previously mine gave error message but i will give the code above a try

thank you so much

 

[xenou]

I think the syntax in MSAccess would be more like:
Count(IIf(tblData.LeavingDate > format(date(), "yyyymmdd"), 1, 0))
and:
Sum(IIf(tblData.LeavingDate > format(date(), "yyyymmdd"))tblData.Hours, 0))

I really leave it up to you to get the syntax right though. I'm only guessing at this from memory.

 

[mahmed1]

That works super - 1 last thing with the final query

How can i add a filter to not bring back any 0s from the alias column named TodayFTE?

Im basically tryin to get the same result below but add this to where clause which it aint letting me

WHERE LEN(WFM_AgentData2_Today.muID) <> "" AND LEN(WFM_AgentData2_Today.TeamManager) <>"" AND TodayFTE > 0

SELECT WFM_AgentData2_Today.muID, WFM_AgentData2_Today.TeamManager, WFM_AgentData2_Today.agentName, Round((SUM(WFM_AgentData2_Today.Hours) - SUM(IIf(WFM_AgentData2_Today.LeavingDate < Format(date(),"yyyymmdd"),WFM_AgentData2_Today.Hours,0)))/37,2) As TodayFTE,
Count(WFM_AgentData2_Today.ID) - SUM(IIf(WFM_AgentData2_Today.LeavingDate < Format(date(),"yyyymmdd"),1,0)) AS TodayHeadcount, Date() AS UpdateDate, Month(Date()) AS [Month]
FROM WFM_AgentData2_Today
WHERE LEN(WFM_AgentData2_Today.muID) <> "" AND LEN(WFM_AgentData2_Today.TeamManager) <>""
GROUP BY WFM_AgentData2_Today.muID, WFM_AgentData2_Today.TeamManager,WFM_AgentData2_Today.agentName

 

[xenou]

you would want to use the unaliased expression in the where clause:

WHERE LEN(WFM_AgentData2_Today.muID) <> "" AND LEN(WFM_AgentData2_Today.TeamManager) <>"" AND Format(date(),"yyyymmdd"),WFM_AgentData2_Today.Hours,0)))/37,2) > 0

 

[mahmed1]

Hi Xenou
Unfortunately That syntax ain’t working / maybe I’ve got to many brackets

 

[xenou]

Yes the expression is not valid.

What is this supposed to be:
Format(date(),"yyyymmdd"),WFM_AgentData2_Today.Hours,0)))/37,2)

 

[mahmed1]

Hiya

this is meant to just take the total hours and take off any leavedate before todays date/37 to get Full Time Equiavalent

the latter syntax i used based on what u said to compare dates

Round((SUM(WFM_AgentData2_Today.Hours) - SUM(IIf(WFM_AgentData2_Today.LeavingDate < Format(date(),"yyyymmdd"),WFM_AgentData2_Today.Hours,0)))/37,2) ad TodaysFTE

 

[xenou]

hmm, well that last expression is a valid one ( I think ). What are you trying to put in the where clause?