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

[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

 

[Wayne Morgan]

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.

 

[Mike H]

Thank you Wayne! That works perfectly.

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

Thanks,
Mike