Number of days between dates if greater than 14

  • Thread starter Thread starter CarlaInJax
  • Start date Start date
C

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.
 
CarlaInJax said:
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.
Click to expand...


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)
 
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?

Marshall Barton said:
CarlaInJax said:
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.
Click to expand...


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)
Click to expand...
 
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?

Marshall Barton said:
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)
Click to expand...
CarlaInJax said:
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.
Click to expand...
Click to expand...
Click to expand...
 
I'm sorry...It's still not returning any data. Any other suggestions?

Marshall Barton said:
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?

Marshall Barton said:
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)
Click to expand...
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.
Click to expand...
Click to expand...
Click to expand...
 
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?

Marshall Barton said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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.

Marshall Barton said:
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?

Marshall Barton said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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.

Marshall Barton said:
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.
Click to expand...
Click to expand...
 
Back
Top