UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

 
   Reply to this topicStart new topic
> How To Get Current Date And Month In A Access Query, Office 2010    
 
   
aliahad
post Sep 14 2011, 04:28 PM
Post#1



Posts: 33
Joined: 14-September 11



I have this query which I am trying to automate it. Currently the filed Statutory_Payment_Year_Month is hard coded and I want to automate that to be current year and month. September 2011 should be '201109'. I have used DatePart and Date functions but not getting any results. In the table Statutory_Payment_Year_Month is defined as Long. All help will be appreciated.
SELECT Sum(tbl_Calendar.Nbr_Of_Returns) AS SumOfNbr_Of_Returns
FROM tbl_Calendar
WHERE EXISTS
(
SELECT *
FROM tbl_Return
WHERE tbl_Calendar.Cactus_ID = tbl_Return.Cactus_ID
AND (((tbl_Return.Statutory_Payment_Year_Month)=201010)));
Thanks,
Ali.
Go to the top of the page
 
Jeff B.
post Sep 14 2011, 04:31 PM
Post#2


UtterAccess VIP
Posts: 9,022
Joined: 30-April 10
From: Pacific NorthWet


welcome2UA.gif
You provided the SQL statement.
Can you confirm that what you think is in that field is what is actually in that field?
What is the data type of the field in the table definition?
Go to the top of the page
 
aliahad
post Sep 14 2011, 04:35 PM
Post#3



Posts: 33
Joined: 14-September 11



The data type is 'Long'. I want the query to update itself for that specific month and the year. So for next month the filed should automatically pick up the Statutory_Payment_Year_Month = 201110
Go to the top of the page
 
Jeff B.
post Sep 14 2011, 04:38 PM
Post#4


UtterAccess VIP
Posts: 9,022
Joined: 30-April 10
From: Pacific NorthWet


There's really nothing 'automatic' about Access (except the Autonumber field type), so if you want something to be stored in a field, you'll need to tell Access what and how.
And in case you weren't aware of this, Access stores data in tables in no particular order. So there is no "next month" or "next record" in Access unless you tell it what/how.
Go to the top of the page
 
aliahad
post Sep 14 2011, 04:54 PM
Post#5



Posts: 33
Joined: 14-September 11



So you are saying that there no way I can make the query automatically run when the new month starts. Not even with the date function
Go to the top of the page
 
datAdrenaline
post Sep 14 2011, 04:55 PM
Post#6


UtterAccess Editor
Posts: 17,081
Joined: 4-December 03
From: Northern Virginia, USA


>> I want the query to update itself for that specific month and the year <<
From the information I gleaned from a brief read of the trhread, your criteria portion of the SQL View of the Query Object should look something like this ...
... WHERE tbl_Return.Statutory_Payment_Year_Month = CLng(Format(Now(),"yyyymm"))
Go to the top of the page
 
datAdrenaline
post Sep 14 2011, 04:56 PM
Post#7


UtterAccess Editor
Posts: 17,081
Joined: 4-December 03
From: Northern Virginia, USA


>> So you are saying that there no way I can make the query automatically run when the new month starts <<
utomatically RUNNING a query is different than creating an expression in your criteria that returns a dynamic value.
Go to the top of the page
 
aliahad
post Sep 14 2011, 05:01 PM
Post#8



Posts: 33
Joined: 14-September 11



Thanks !!!!!!!!!!
atAdrenaline, the WHERE statement worked.
Thanks again for your help.
Go to the top of the page
 
dmhzx
post Sep 15 2011, 04:37 AM
Post#9



Posts: 5,303
Joined: 22-December 10
From: England


Purely for information, where datAdrealine used Now(),
On this case you could use Date() instead.
It's purely personal, but I like to use Date() when I'm working to a granularity of whole days, and Now() if I really want time.
Go to the top of the page
 


RSSSearch   Top   Lo-Fi    6th July 2015 - 05:08 PM