Date that is 7 days greater or less than another date.

[RuNu1]

Hi all,
this is my first thread so thanks in advance to anyone who can help out.


I am trying to code a field in MS Access 2003 query that shows a 1 when a date is either 7 days [or more] before or after the progress date [progress date is a field with the db]. Can anyone please show me the required syntax/function as have tried DateAdd, DateDiff etc and am having no luck.
Thanks again.

[Bob Fitz]

Hi

what is the name of your date field?

[xpertoffice]

Enter below in query field box:

MyCalcField: IIF(DateAdd('d', 7, [ProgressDate]) > Now(),0,1)

returns 1 or 0

Should set the Format property of MyCalcField to GeneralNumber but will work anyway.


xpertOffice...

[Bob Fitz]

Hi

I could be wrong, but (there's always a but) I think the solution posted by xpertofice will only return the required value of 1 when the "ProgressDate" is more than 7 days from now. My understanding is that the value of 1 should also be returned if the "ProgressDate" is more than 7 days before now.

If that is correct you may like to try this instead:

 
MyCalcField: IIF(DateAdd('d', 7, [ProgressDate]) > Now(),0,1) + 
IIF(DateAdd('d', 7, [ProgressDate]) < Now(),0,1)

[RuNu1]

Thanks Gents for the replies. What I need is to highlight where the date in my progress_date field is either 7 or more days before or after the progress date. For example progress date is the 10/05/2011 and the date in the created_date field is on or brefore 03/05/2011 then 1 and also if created_date on or after 17/05/2011 then 1. In other words there is a window of acceptance of 14 days from the progress_date that the created_date can be in.

Thanks again

[xpertoffice]

Yes, I think Bob read it better than me.

But I think you need to change the > to => and < to =< to catch the 7 day state, as was requested (now I've read it forensically!)


xpertOffice....

[xpertoffice]

Where's this CreatedDate suddenly appeared from; your original post just said the 'Date', we assumed that meant the date you run the query; ie Now().

So taking what has been said so far, change the Now() for [created_
date].

I'm off to get my big toe nail sorted..

xpertOffice...

[pervezjan]

I need help in vba code.

I am facing problem to validate the date the syntax I am writing in date field is as under (field name is date)

If date > +7 or date< -7 then
Msgbox "wrong"
end if
I mean If date is greater than 1 week or less thab one week then I need a message

[Bob Fitz]

"Date" is a reserved word in Access (see: http://allenbrowne.com/AppIssueBadWord.html) so change the name of the text box to "MyDate" and try:

If Me.[MyDate] >= Me.[MyDate] +7 OR Me.[MyDate] <= Me.[MyDate] -7 then
  Msgbox "wrong"
End if