Returning Data for Last Calendar

B

BrianFawcett

Registered User.

Local time

Today, 10:46

Joined

May 3, 2010

Messages

63

• Oct 25, 2011

• #1

I am trying to write a SQL statement that will return only records that have been received in the last 365 days. I am getting an error for too may arguements in my statement. Can someone tell me what my problem is, I have a feeling it is quite simple.

"FROM [Data Table] WHERE [Data Table].[Date Received] > DATE(year,-1) "

 

boblarson

Smeghead

Local time

Today, 10:46

Joined

Jan 12, 2001

Messages

32,040

• Oct 25, 2011

• #2

Use this instead:

Code:

"FROM [[URL="http://www.access-programmers.co.uk/forums/showthread.php?t=217418#"][COLOR=darkgreen]Data[/COLOR][/URL] Table] WHERE [Data Table].[Date Received] > DateAdd("yyyy", -1, Date())"

 

boblarson

Smeghead

Local time

Today, 10:46

Joined

Jan 12, 2001

Messages

32,040

• Oct 25, 2011

• #3

Oh, and using the DateAdd with YEAR ("yyyy") instead will also account for leap years where using 365 days will not.

 

B

BrianFawcett

Registered User.

Local time

Today, 10:46

Joined

May 3, 2010

Messages

63

• Oct 25, 2011

• #4

Bob, VBA is telling me that it expects and End of String at the "yyyy". ??

 

boblarson

Smeghead

Local time

Today, 10:46

Joined

Jan 12, 2001

Messages

32,040

• Oct 25, 2011

• #5

If you are writing this in code, you can use:

"FROM [Data Table] WHERE [Data Table].[Date Received] > " & DateAdd("yyyy", -1, Date())