Number of days between dates if greater than 14

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.
 
M

Marshall Barton

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

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?

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...
 
M

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?

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...
 
C

CarlaInJax

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...
 
M

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?

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...
 
C

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.

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...
 
M

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.

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Difference between two dates if greater than 14 1
Conditional format and comparing dates 1
Number of Days? 4
calculations in report controls 4
Show if null or greater than 13
grouping and sorting - dates and balances 5
Conditional formatting of date supplied by a form 1
Using Dates with the IF function 2

Top