DateAdd Calculation?

[Liam.M]

Hey guys, how are you? I would very much appreciate any assistance
anyone could please provide me....I have a query which Ken and many
other people have helped me work towards, however, in my latest attempt
I am trying to exclude all the records that are not relevent, and only
wish to display records that fall within two months prior to "Due
Date", so for example, if the Due Date field was= 16/06/2006, I would
want to display any records in my Database that have a "Due Date" that
falls between 16/04/2006 and 16/06/2006....currently I have this
calculation: WHERE ShipsInformation.[Date of Issue]
<=DateAdd("m",-2,Date())
Any suggestions...
thankyou for your response in advance

 

[John Vinson]

I would
want to display any records in my Database that have a "Due Date" that
falls between 16/04/2006 and 16/06/2006....currently I have this
calculation: WHERE ShipsInformation.[Date of Issue]
<=DateAdd("m",-2,Date())

Instead, use

WHERE ShipsInformation.[DateOfIssue] >= DateAdd("m", -2, Date()) AND
[DateOfIssue] <= Date()

You did say *between* and your criterion only specifies *after*.

John W. Vinson[MVP]

 

[Liam.M]

Hey John...thanxs again for this..........you actually helped me write
this SQL Statement in the first place:

SELECT ShipsInformation.[SBMA Number], ShipsInformation.[Vessel Name],
ShipsInformation.[IMO Number], ShipsInformation.[Date of Issue],
ShipsInformation.RecordID, ShipsInformation.[Date of Attendance],
DateAdd("yyyy",1,ShipsInformation.[Date of Issue]) AS [Due Date]
FROM ShipsInformation
WHERE ShipsInformation.DateOfIssue>=DateAdd("m",-2,Date()) And
[DateOfIssue]<=Date();

now just trying to fine tune it.........I have tried what u recommended
But it still doesnt seem to work...it is asking me to enter
"ShipsInformation.DateAOfIssue" Parameter Value? Any idea?

I would
want to display any records in my Database that have a "Due Date" that
falls between 16/04/2006 and 16/06/2006....currently I have this
calculation: WHERE ShipsInformation.[Date of Issue]
<=DateAdd("m",-2,Date())

Instead, use

WHERE ShipsInformation.[DateOfIssue] >= DateAdd("m", -2, Date()) AND
[DateOfIssue] <= Date()

You did say *between* and your criterion only specifies *after*.

John W. Vinson[MVP]

 

[Guest]

Try

WHERE ShipsInformation.[Date of Issue] Between DateAdd("m",-2,Date()) And
Date()

That will list all the records that falls between today's date up to two
month's back.

 

[John Vinson]

Hey John...thanxs again for this..........you actually helped me write
this SQL Statement in the first place:

SELECT ShipsInformation.[SBMA Number], ShipsInformation.[Vessel Name],
ShipsInformation.[IMO Number], ShipsInformation.[Date of Issue],
ShipsInformation.RecordID, ShipsInformation.[Date of Attendance],
DateAdd("yyyy",1,ShipsInformation.[Date of Issue]) AS [Due Date]
FROM ShipsInformation
WHERE ShipsInformation.DateOfIssue>=DateAdd("m",-2,Date()) And
[DateOfIssue]<=Date();

now just trying to fine tune it.........I have tried what u recommended
But it still doesnt seem to work...it is asking me to enter
"ShipsInformation.DateAOfIssue" Parameter Value? Any idea?

Ah. Ok, you're taking me literally. I don't use blanks in fieldnames,
habitually, so I left them out of my expression. Should be

WHERE ShipsInformation.[Date Of Issue]>=DateAdd("m",-2,Date()) And
[Date Of Issue]<=Date();

or if you want to filter on the due date, incorporate the due date
calculation in the dateadd expressions:

WHERE ShipsInformation.[Date Of Issue]>=DateAdd("m",10,Date()) And
[Date Of Issue]<=DateAdd("m",12,Date());

John W. Vinson[MVP]

 

[Liam.M]

Hey John,
Thankyou for your reply, I wasnt taking you literally in the sense that
the expressions were blank....I realised this and immediately
incorporated that into my attempt to solve the problem, for some reason
however, I still cannot get this to work.

I'll run through the problem, once more quickly....I want it to query
my "Due Date" field (not my Date of Issue field) to see if this "Due
Date" field is coming up within the next two months!
I have a feeling that the potentially problem may be because I am using
a Calculation to work the "Due Date" out ..from a value the user
selects in "Calander3" that is then shown in the "Date Of Issue" (which
is DateAdd("yyyy",1,ShipsInformation.[Date of Issue]) AS [Due Date] ),
and then passed to "Due Date".... it means that the user never actually
manually enters a X Value into the "Due Date" field...it is done
automatically for them....could this potentially be why I am having
difficulty working out whether or not the "Due Date" is within the next
two months?

Regards,

Liam