Full Version: Select information for the past year.
UtterAccess Forums > Microsoft® Access > Access Date + Time
dietmarhannam
I have a list of sorted dates in chronological order in the format "mm/yyyy". When i try to get the information for the last 12 months i don't get any values back.
I have tried "Between Date()-365 And Date()" but i don't think it likes it because of the format i'm using.
Any other ways round the problem? I'm sure i've solved the problem before but just can't seem to do it!
I have looked at the DateAdd() function, is this the way i should be doing it?
Cheers for your help
JayNoelOlimpo
Hi;
Try ...
Create another column in the Query ...
strYear:Format([DateFieldHere],"yyyy")
and under the criteria >=Format(Date(),"yyyy") - 1
HTH.
dietmarhannam
Thanks for the help but it doesn't seem to solve my problem quite as i would like. What i'me really after is the last 12 months.
The statement you gave me gives the last year, but if todays date was January 2005 (for example) then the only field that would show is the 01/2005.
It looks like it works at the moment cause it's December and i have all the 12 months of 2005 but i don't think this would be the case in the early months next year.
tried altering your information to:
strYear:Format([DateFieldHere],"mm")
and under the criteria >=Format(Date(),"mm") - 12
but i receive every date within the table!
Any other ideas?
Thanks
JayNoelOlimpo
You could also use ...
>DateAdd("m",-12,Date())
HTW.
dietmarhannam
it doesn't even like that basic DateAdd function!
Here's the SQL i have got at the moment
SELECT pro.Raised_This_Month, pro.Released_This_Month, pro.Total, pro.Date, (SELECT SUM(pro1.Total)
FROM [CS_Monthly_Total] pro1
WHERE pro1.[Month_ID] <= pro.[Month_ID]) AS RuningTotal
FROM CS_Monthly_Total AS pro
Order even like that basic DateAdd function!
Here's the SQL i have got at the moment
SELECT pro.Raised_This_Month, pro.Released_This_Month, pro.Total, pro.Date, (SELECT SUM(pro1.Total)
FROM [CS_Monthly_Total] pro1
WHERE pro1.[Month_ID] <= pro.[Month_ID]) AS RuningTotal
FROM CS_Monthly_Total AS pro
ORDER BY Format(pro.Date,"yyyy/mm") DESC;
Hope this gives a better view of what i'm doing at the moment. This SQL just gives me all the dates and info associated with these dates. In theory it seems easy, all i need are the dates and information BETWEEN Date() and Date() - 12months. Just seems that everything i try doesn't want to work!
The month_ID is stored in order (1 being the oldest date, 83 being the newest date (Dec 2005)). Perhaps i could use this to get the last 12 numbers. E.g get the last Month_ID (83) and select all the dates between the last ID and ID - 12. Again this seems possible but i don't know where to start. Seems like the DateAdd function is a lot simpler!
FYI i entered this snippet of SQL into my query and it returned the data for the date 12months ago.
WHERE Format(YourDate, "yyyy/mm") = Format(DateAdd("m", -1, Date()), "yyyy/mm")
Odon't know if that is any use to you?
Hope you can help (again!)
JayNoelOlimpo
No need for formatting Yourdate ...
THERE YourDate >DateAdd("m", -12, Date())
Hmmm... Your structure seems not right ...
jsitraining
Hi
One thing that springs to mind (as a knee-jerk reaction) Are you sure that the date column is actually a date column or is it a text column (check the table design) Apologies if I'm teaching you to suck eggs frown.gif Also 'Date' is a reserved word in access and should never be used as an object name. Change the column called date to something else.
im
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.