• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

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
0
Karen Schaefer
Asked:
Karen Schaefer
1 Solution
 
pteranodon72Commented:
What is the field type of [EFFECT_HIRE_DATE] in its underlying table? Is it Text or Date/Time?

pT72
0
 
Karen SchaeferAuthor 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
 
TempDBACommented:
And if it has time associated with it, it won't help you out

CInt(Right([EFFECT_HIRE_DATE],2))))
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Karen SchaeferAuthor Commented:
Values from the oracle table for

EFFECT_HIRE_DATE
20111110
20050627
20110420
20110606
20060816
20120413
20101022
20120824
0
 
NorieCommented:
Is the conversion being done correctly?
0
 
hnasrCommented:
Try:
CDate(format(EFFECT_HIRE_DATE,"0000/00/00")) Between (Date()-7)  AND (Date()+7)
0
 
Gustav BrockCIOCommented:
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
 
Karen SchaeferAuthor Commented:
thanks for the input
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now