Date calculation on 2 fields

[Business analyst williams]

I have a query that calculates captures a date filed 20 days after the date
which is entered. It then gathers all the dates after the 20 days has been
calculated. I need to code another date field that doesnt grab info 45 days
past the date.

Example: works now
user - puts in date 1/28/2010
query - subracts 20 days from that date = 1/08/10
query - then gathers all the dates after 1/8/10.

Need - To look at another date field and make sure no data is gathered 45
days from the date in field.

 

[Jeff Boyce]

Are you talking about two separate queries, or a single query with two
criteria?

(if the latter, your selection criterion might be something like - untested:

Between 20 And 45

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jeff Boyce]

Note that the selection criteria I offered would be applied to the
difference between dates, not to the date itself.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jerry Whittle]

Posting the SQL statement of the current query and the name of the other date
field would really help us to help you.
I'll take a guess and suggest this in the criteria for the other date field.

<Date() + 45

 

[Business analyst williams]

Here is the current query:

SELECT [Customer Communication].[Provisional Credit Date], [Customer
Communication].[Reference ID], [Customer Communication].[Card Number],
[Customer Communication].[DDA/Sav Acct Number], [Customer
Communication].[Customer contact date]
FROM [Customer Communication]
WHERE ((([Customer Communication].[Provisional Credit
Date])<=DateAdd("d",-20,[Date])));

The date field that I do not want exceed 45 days is the Customer contact date

 

[Jerry Whittle]

SELECT CC.[Provisional Credit Date],
CC.[Reference ID],
CC.[Card Number],
CC.[DDA/Sav Acct Number],
CC.[Customer contact date]
FROM [Customer Communication] as CC
WHERE CC.[Provisional Credit Date] <= DateAdd("d",-20,Date())
AND CC.[Customer contact date] < Date() + 45;

Noticed that I clean up things by using an alias for the table name.

One thing confused me is the square brackets [ ] around the Date in your
SQL statement. If you have a field named Date, it would be OK, but if you
want to use the Date function, it should look like above.

DateAdd is very handy when doing thing like adding months, weeks, years,
etc. However you can just put a number like I did above if adding up days.
You may what to change to one or the other for consistancy.

Be careful to test this especially if your date fields would happen to hold
date and times. You could miss part of a day especially for future dates.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here is the current query:

SELECT [Customer Communication].[Provisional Credit Date], [Customer
Communication].[Reference ID], [Customer Communication].[Card Number],
[Customer Communication].[DDA/Sav Acct Number], [Customer
Communication].[Customer contact date]
FROM [Customer Communication]
WHERE ((([Customer Communication].[Provisional Credit
Date])<=DateAdd("d",-20,[Date])));

The date field that I do not want exceed 45 days is the Customer contact date

Posting the SQL statement of the current query and the name of the other date
field would really help us to help you.
I'll take a guess and suggest this in the criteria for the other date field.

<Date() + 45