Query that asks for dates x number of days away from today's date / comparing two fields in a query

  • Thread starter Thread starter Mike H
  • Start date Start date
M

Mike H

Hey everyone -

Thanks for the quick responses to my first post. My current issue is
that when I enter in Date()+40 as my criteria for my query, it doesn't
come back with any records despite the fact that I do have dates that
are more than 40 days away from today's date. Is there something I'm
doing wrong or an example of this I could see somewhere to help me
out?

Secondly, how do you go about creating a field in a query that
compares the difference between two dates? I was told to create a new
field and put in the criteria: [Date 1] - [Date 2] but that doesn't
work for me either. I'll also need the abs value for this.

Basically, I'd like to retrieve items that have a ship date that are
40 days past (or before) today's date along with a difference of at
least 10 days between the ship date and the promise date.

Thanks in advance for your help!
Mike
 
You may not have anything 40 days out. Try using <= or >= as appropriate
instead of =.

To compare the difference between two date, you'll need a calculated field.
In the Field row of the query design grid put in something like

CheckDifference: [Date1]-[Date2]
or
CheckDifference: [Date2]-[Date1]
or
CheckDifference: Abs([Date1]-[Date2])

Then in the criteria put in a number for the number of days you want. If you
want records with a difference of 2 or more days then put in >=2 in the
criteria.
 
Thank you Wayne! That works perfectly.

However, now I have a new dilemma....which I'll create a new post for.

Thanks,
Mike
 
Back
Top