Applying Criteria to DateDiff Expression (1 Viewer)

WesleyK · Mar 20, 2019

Hey all! So glad I found this active forum.

[Background: There is an ugly database I've inherited, which contains a generic text field holding a piece of information I want. Thankfully the data entry is consistent and I was able to use the Left command to isolate the data that I want to use as a date. Then I used DateDiff command and successfully calculated the difference between Today and the piece of information I extracted by using Left command.]

So now I have this expression which I've identified as [TimeLeft] which displays the DateDiff results successfully.

For this query however, I only want to display records which [TimeLeft] is less than 8 (including negative numbers).

I've tried adding criteria of "<8" and that definitely doesn't work. My current code is:

TimeLeft: DateDiff("d",Now(),[ProjectDeadline])

Is it possible to add criteria to a column which is calculating DateDiff at the same time? How can I apply Criteria to limit the results?

Thanks for your help everyone!:banghead:

-Wes-

plog · Mar 20, 2019

You cannot add criteria to the DateDiff function, however you can rearrange the equation to accomplish the comparison in a different manner. Instead of a DateDiff you would put [ProjectDeadline] in your query, then under it use criteria like this:

>(Date() + 8)

--That might need to be a minus sign.

However, I'm not all that confident [ProjectDeadline] is a date (or at least the date you assume it is). My advice is to set up a verification query. Create a new query, bring down [ProjectDeadline], then create your DateDiff calculated field in another column but apply no criteria. That way you can verify the DateDiff is working correctly and further its being supplied with the [ProjectDeadline] value you assume it has. Guarantee this isn't DateDiffs fault, but your assumptions.

June7 · Mar 20, 2019

"Doesn't work" means what - error message, wrong result, nothing happens?

Should be able to have criteria under the calculated column. Maybe should use Date() instead of Now().

However, if ProjectDeadline is itself a calculated field, the WHERE clause would have to repeat this calc within the DateDiff - if you want to apply filter in same query that does these calcs.

But plog's suggested alternative should work.

WesleyK · Mar 21, 2019

SOLVED!

Thanks so much everyone. My original code was:

TimeLeft: DateDiff("d",Now(),[ProjectDeadline],10))

...and I wasn't able to apply a criteria.

I switched "Now()" to "Date()" with no luck, and I was still getting a prompt to input project deadline when I ran my query. Once I replaced [ProjectDeadline] with the calculation for Project Deadline it worked.... this is my revised code:

TimeLeft: DateDiff("d",Date(),Left([Comments],10))

With that code I can apply criteria to that column and it works!

Thanks so much everyone! This poor database has so many single fields carrying multiple types of data. *sigh*

KathyJean · Apr 4, 2019

I have the same situation but only want the days that are past due. I've applied the following: =DateDiff("d",Now(),[VMR-Due],0)

If I try: =DateDiff("d",Now(),[VMR-Due],<0)
I get an error message "expression you entered contains invalid syntax."

I only want the number of days past due.

While I've been working in access for a couple of years, I'm self taught, so I'm not familiar with coding.
Thanks for the help.
:banghead:

Mark_ · Apr 4, 2019

Incorrect syntax.
Proper syntax is DateDiff ( interval, date1, date2 [, firstdayofweek] [, firstweekofyear] ), so you are telling it the FirstDayOfWeek is <0.

You would want DateDiff("d",Date(),[VMR-Due]) <0

NOTE: I am under the impression [VMR-Due] is an existing field. If it is [VMR] - [Due] that could be a problem also.

KathyJean · Apr 4, 2019

Thanks Mark.
You are correct that the field is [VMR-Due].
I put in the expression and the answers are either -1 or 0 instead of the number of days past due.

Mark_ · Apr 4, 2019

Can you post the SQL or a screen shot of your query?

theDBguy · Apr 4, 2019

KathyJean said:
Thanks Mark.
You are correct that the field is [VMR-Due].
I put in the expression and the answers are either -1 or 0 instead of the number of days past due.

Hi. Pardon me for jumping in but is [VMR-Due] the date it’s due, and you want those that are passed this date? If so, you could try [VMR-Due]<=Date().

KathyJean · Apr 5, 2019

I finally got it to work. I put Expr1: DateDiff("d",Now(),[Contract-Due Date-Preparation])
then put <=0 in the criteria.

Thank you all so much for your help. It's been a long week.

Applying Criteria to DateDiff Expression (1 Viewer)

WesleyK

New member

plog

Banishment Pending

June7

AWF VIP

WesleyK

New member

KathyJean

New member

Mark_

Longboard on the internet

KathyJean

New member

Mark_

Longboard on the internet

theDBguy

I’m here to help

KathyJean

New member

Similar threads

Users who are viewing this thread