DateDiff and Count/Sum
in [Reports]
|
From: KARL DEWEY on 15 Apr 2010 12:31 You did not miss anything, I just do not know a lot of things. -- Build a little, test a little. "Duane Hookom" wrote: > Karl, > Aren't these the same: > =Abs(Sum([Date of Awareness]> Date())) > =Abs(Sum(IIF([Date of Awareness]> Date(), 1, 0))) > > This expression will return -1 for true or 0 for false > [Date of Awareness]> Date() > > If you Sum() this expression, it will return a negative count of the number > of records that return true. > > Changing the negative to positive with Abs() should provide the same value > as your suggestion. Or, did I miss something? > > -- > Duane Hookom > Microsoft Access MVP > > > "KARL DEWEY" wrote: > > > Your =IIF(DateDiff("d", [Date of Awareness], Date()>1,1,0)) > > has closing parenthesis misplaced like this -- > > =IIF(DateDiff("d", [Date of Awareness], Date())>1,1,0) > > > > Your =Abs(Sum([Date of Awareness]> Date())) > > is missing an IIF like this -- > > =Abs(Sum(IIF([Date of Awareness]> Date(), 1, 0))) > > and it does not need the Abs function. > > > > -- > > Build a little, test a little. > > > > > > "caro" wrote: > > > > > I am trying to count records where the scheduled date is greater than the > > > current date. All Dates (past visits and scheduled visits) are listed in the > > > Date of Awareness field. The expressions I have tried include: > > > =IIF(DateDiff("d", [Date of Awareness], Date()>1,1,0)) > > > =Abs(Sum([Date of Awareness]> Date())) > > > Please let me know if I am even on the right track. > > > -Caro
From: Duane Hookom on 15 Apr 2010 13:53
Karl, I have learned a lot from your past posts so I was a bit concerned I had messed up the syntax/expression. -- Duane Hookom Microsoft Access MVP "KARL DEWEY" wrote: > You did not miss anything, I just do not know a lot of things. > -- > Build a little, test a little. > > > "Duane Hookom" wrote: > > > Karl, > > Aren't these the same: > > =Abs(Sum([Date of Awareness]> Date())) > > =Abs(Sum(IIF([Date of Awareness]> Date(), 1, 0))) > > > > This expression will return -1 for true or 0 for false > > [Date of Awareness]> Date() > > > > If you Sum() this expression, it will return a negative count of the number > > of records that return true. > > > > Changing the negative to positive with Abs() should provide the same value > > as your suggestion. Or, did I miss something? > > > > -- > > Duane Hookom > > Microsoft Access MVP > > > > > > "KARL DEWEY" wrote: > > > > > Your =IIF(DateDiff("d", [Date of Awareness], Date()>1,1,0)) > > > has closing parenthesis misplaced like this -- > > > =IIF(DateDiff("d", [Date of Awareness], Date())>1,1,0) > > > > > > Your =Abs(Sum([Date of Awareness]> Date())) > > > is missing an IIF like this -- > > > =Abs(Sum(IIF([Date of Awareness]> Date(), 1, 0))) > > > and it does not need the Abs function. > > > > > > -- > > > Build a little, test a little. > > > > > > > > > "caro" wrote: > > > > > > > I am trying to count records where the scheduled date is greater than the > > > > current date. All Dates (past visits and scheduled visits) are listed in the > > > > Date of Awareness field. The expressions I have tried include: > > > > =IIF(DateDiff("d", [Date of Awareness], Date()>1,1,0)) > > > > =Abs(Sum([Date of Awareness]> Date())) > > > > Please let me know if I am even on the right track. > > > > -Caro |