asked on

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

pteranodon72

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

pT72

Karen Schaefer

ASKER

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