Need help defining parameters for an access query

[Mike H]

Hello everyone,

I'm trying to develop some queries in Access and I can't seem to
figure out a couple things:

How do I run a query on a range of dates in a db that only wants dates
40 days after or more (or 40 days before or less) a date given for
each item? Example: It would give me an item dated 1-Oct-03 because
it's more than 40 days from today.

In this query, I would also need a way that takes two fields and
compares them...for example, if I had two seperate date fields, I
would need a query that returns items that have a difference of at
least 5 days between the two date fields.

So the query, in the end, could return an item that has a "promise"
date that is at least 40 days more (or less) than today's date AND has
a difference of at least 5 days between the "promise" date and the
"delivered" date.

I hope this makes sense....and thank you all in advance!!

-Mike

 

[Duane Hookom]

I can't understand your 40 day thing however...In the criteria under your
first date field, use an expression like:
<DateAdd("d",-40,Date())

You can create a column in your query
DateSpread: DateDiff("d", [DateA], [DateB])
then set the criteria to

 

[Dale]

Hello everyone,

I'm trying to develop some queries in Access and I can't seem to
figure out a couple things:

How do I run a query on a range of dates in a db that only wants dates
40 days after or more (or 40 days before or less) a date given for
each item? Example: It would give me an item dated 1-Oct-03 because
it's more than 40 days from today.

In this query, I would also need a way that takes two fields and
compares them...for example, if I had two seperate date fields, I
would need a query that returns items that have a difference of at
least 5 days between the two date fields.

So the query, in the end, could return an item that has a "promise"
date that is at least 40 days more (or less) than today's date AND has
a difference of at least 5 days between the "promise" date and the
"delivered" date.

I hope this makes sense....and thank you all in advance!!

-Mike

To find all dates greater than 40 days from today's date, enter in the
criteria section of query under field "promise" date:

Date()+40

If you want records that meet this criteria plus your other
criteria...try this:

Create a field in your query that calculates the difference between
the promise date and the delivered date:

[PromiseDate]-[DeliveredDate] Not sure which one should comes first.
You might have to try it both ways.

Then in the criteria section under this field and on the same line as
your other criteria enter:

4

Hope this helps.

 

[Mike H]

Thanks everyone for replying...

I've tried the Date()+40 function but it doesn't work for me. Date()
works but not +40. Any ideas?

Also, I'm not quite sure how to go about creating that comparison of
dates in the query...and it also needs to return an abs value.

I sincerely appreciate all of your help!

-Mike

 

[Danny J. Lesandrini]

I never liked that syntax, though I've used it and it works.
Seems like voodo science. Use the DateAdd() funciton

dteMyDate = DateAdd("d", Date(), 40)