Date dif between 7 days of current date

what is the correct syntax for plus or minus 7 days from current date?

I tried:

Here is the value of the field in the query - I needed to reformat the date into the correct format.

 IIf(IsNull([EFFECT_HIRE_DATE]),"",DateSerial(CInt(Left([EFFECT_HIRE_DATE],4)),CInt(Mid([EFFECT_HIRE_DATE],5,2)),CInt(Right([EFFECT_HIRE_DATE],2))))

I need to limit the query to the between -7 and +7 of current date.

Here what I have so far for the critieria:
Between DateAdd("d",-7,Date()) And DateAdd("d",7,Date())

current Results -
STATUS_DATE
8/15/2012 7:40:17 PM
9/1/2011 6:40:05 PM
4/25/2011 7:08:32 PM
8/15/2012 7:40:17 PM

As you can see it is not returning the correct results if current date = 9/4/2012
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
Or the other way around if you have an index on EFFECT_HIRE_DATE:

EFFECT_HIRE_DATE Between Format(DateAdd("d", -7, Date()), "yyyymmdd") And Format(DateAdd("d", 7, Date()), "yyyymmdd")

or, if string:

EFFECT_HIRE_DATE Between Format(DateAdd("d", -7, Date()), "\'yyyymmdd\'") And Format(DateAdd("d", 7, Date()), "\'yyyymmdd\'")

/gustav
0
 
pteranodon72Commented:
What is the field type of [EFFECT_HIRE_DATE] in its underlying table? Is it Text or Date/Time?

pT72
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Text, I think - it is coming from an oracle table and the format is as follows

yyyymmdd - hence the need to convert the format to mm/dd/yyyy.

Note; I also tried in the criteria using NOW() instead of Date().  The field properties of the query is set for short date

K
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TempDBACommented:
And if it has time associated with it, it won't help you out

CInt(Right([EFFECT_HIRE_DATE],2))))
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Values from the oracle table for

EFFECT_HIRE_DATE
20111110
20050627
20110420
20110606
20060816
20120413
20101022
20120824
0
 
NorieVBA ExpertCommented:
Is the conversion being done correctly?
0
 
hnasrCommented:
Try:
CDate(format(EFFECT_HIRE_DATE,"0000/00/00")) Between (Date()-7)  AND (Date()+7)
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
thanks for the input
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.