Number of days between dates if greater than 14

[CarlaInJax]

In either a query or a report, I need to show the difference between two
fields: [Date Call Received] and [Date Resolved] IF the number of days is
greater than 14. I have been going insane trying to figure out this simple
problem, and also trying to determine if it should be done in the query or on
the report.
Thanks so much for your help.

 

[Marshall Barton]

In either a query or a report, I need to show the difference between two
fields: [Date Call Received] and [Date Resolved] IF the number of days is
greater than 14. I have been going insane trying to figure out this simple
problem, and also trying to determine if it should be done in the query or on
the report.

Either way, I think an expression like this should do it:

IIf(DateDiff("d", [Date Resolved], [Date Call Received]) >
14, DateDiff("d", [Date Resolved], [Date Call Received]),
Null)

 

[CarlaInJax]

This returns nothing for me when I run it in the query. I know that I have
data that meets that criteria, so I must be doing something wrong. Any ideas?

In either a query or a report, I need to show the difference between two
fields: [Date Call Received] and [Date Resolved] IF the number of days is
greater than 14. I have been going insane trying to figure out this simple
problem, and also trying to determine if it should be done in the query or on
the report.

Either way, I think an expression like this should do it:

IIf(DateDiff("d", [Date Resolved], [Date Call Received]) >
14, DateDiff("d", [Date Resolved], [Date Call Received]),
Null)

 

[Marshall Barton]

I probably had the fields in the wrong order? Try changing
both DateDiff to:
DateDiff("d", [Date Call Received], [Date Resolved])
--
Marsh
MVP [MS Access]

This returns nothing for me when I run it in the query. I know that I have
data that meets that criteria, so I must be doing something wrong. Any ideas?

Either way, I think an expression like this should do it:

IIf(DateDiff("d", [Date Resolved], [Date Call Received]) >
14, DateDiff("d", [Date Resolved], [Date Call Received]),
Null)

In either a query or a report, I need to show the difference between two
fields: [Date Call Received] and [Date Resolved] IF the number of days is
greater than 14. I have been going insane trying to figure out this simple
problem, and also trying to determine if it should be done in the query or on
the report.

 

[CarlaInJax]

I'm sorry...It's still not returning any data. Any other suggestions?

I probably had the fields in the wrong order? Try changing
both DateDiff to:
DateDiff("d", [Date Call Received], [Date Resolved])
--
Marsh
MVP [MS Access]

This returns nothing for me when I run it in the query. I know that I have
data that meets that criteria, so I must be doing something wrong. Any ideas?

Either way, I think an expression like this should do it:

IIf(DateDiff("d", [Date Resolved], [Date Call Received]) >
14, DateDiff("d", [Date Resolved], [Date Call Received]),
Null)

CarlaInJax wrote:
In either a query or a report, I need to show the difference between two
fields: [Date Call Received] and [Date Resolved] IF the number of days is
greater than 14. I have been going insane trying to figure out this simple
problem, and also trying to determine if it should be done in the query or on
the report.

 

[Marshall Barton]

Are you sure that [Date Call Received] and [Date Resolved]
are the field names in their table? Are they really
Date/Time fields. not Text fields?

If you'll post a Copy/Paste of your query's SQL view , I'll
look it over it.
--
Marsh
MVP [MS Access]

I'm sorry...It's still not returning any data. Any other suggestions?

I probably had the fields in the wrong order? Try changing
both DateDiff to:
DateDiff("d", [Date Call Received], [Date Resolved])

This returns nothing for me when I run it in the query. I know that I have
data that meets that criteria, so I must be doing something wrong. Any ideas?

:
Either way, I think an expression like this should do it:

IIf(DateDiff("d", [Date Resolved], [Date Call Received]) >
14, DateDiff("d", [Date Resolved], [Date Call Received]),
Null)

CarlaInJax wrote:
In either a query or a report, I need to show the difference between two
fields: [Date Call Received] and [Date Resolved] IF the number of days is
greater than 14. I have been going insane trying to figure out this simple
problem, and also trying to determine if it should be done in the query or on
the report.

 

[CarlaInJax]

I got it to count the number of days between the [Date Call Received] and
[Date Resolved], so the field names are correct. I just cannot get it to
show only those that are greater than 14 days. I don't understand why it
won't work.

I just exported my info to an Excel spreadsheet and completed it that way.

Thanks so much for your help with this.

Are you sure that [Date Call Received] and [Date Resolved]
are the field names in their table? Are they really
Date/Time fields. not Text fields?

If you'll post a Copy/Paste of your query's SQL view , I'll
look it over it.
--
Marsh
MVP [MS Access]

I'm sorry...It's still not returning any data. Any other suggestions?

I probably had the fields in the wrong order? Try changing
both DateDiff to:
DateDiff("d", [Date Call Received], [Date Resolved])


CarlaInJax wrote:
This returns nothing for me when I run it in the query. I know that I have
data that meets that criteria, so I must be doing something wrong. Any ideas?

:
Either way, I think an expression like this should do it:

IIf(DateDiff("d", [Date Resolved], [Date Call Received]) >
14, DateDiff("d", [Date Resolved], [Date Call Received]),
Null)

CarlaInJax wrote:
In either a query or a report, I need to show the difference between two
fields: [Date Call Received] and [Date Resolved] IF the number of days is
greater than 14. I have been going insane trying to figure out this simple
problem, and also trying to determine if it should be done in the query or on
the report.

 

[Marshall Barton]

Using Excel for this is like using a sledge hammer to crack
a peanut ;-)

I'm sure this problem will yield to a simple solution, but I
can only speculate until I see the query and maybe more
details about exactly what you want to query to contain.
--
Marsh
MVP [MS Access]

I got it to count the number of days between the [Date Call Received] and
[Date Resolved], so the field names are correct. I just cannot get it to
show only those that are greater than 14 days. I don't understand why it
won't work.

I just exported my info to an Excel spreadsheet and completed it that way.

Thanks so much for your help with this.

Are you sure that [Date Call Received] and [Date Resolved]
are the field names in their table? Are they really
Date/Time fields. not Text fields?

If you'll post a Copy/Paste of your query's SQL view , I'll
look it over it.