UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | 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

UtterAccess Member
Posts: 32



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: 8,758
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

UtterAccess Member
Posts: 32



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: 8,758
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

UtterAccess Member
Posts: 32



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: 16,775
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: 16,775
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

UtterAccess Member
Posts: 32



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

UtterAccess Ruler
Posts: 3,822
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
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 21st November 2014 - 12:37 PM