UtterAccess.com
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,652
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?


--------------------
Regards

Jeff Boyce
Microsoft Access MVP

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
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,652
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.


--------------------
Regards

Jeff Boyce
Microsoft Access MVP

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
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,705
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"))


--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 
+
datAdrenaline
post Sep 14 2011, 04:56 PM
Post #7

UtterAccess Editor
Posts: 16,705
From: Northern Virginia, USA



>> So you are saying that there no way I can make the query automatically run when the new month starts <<

Automatically RUNNING a query is different than creating an expression in your criteria that returns a dynamic value.


--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 
+
aliahad
post Sep 14 2011, 05:01 PM
Post #8

UtterAccess Member
Posts: 32



Thanks !!!!!!!!!!

datAdrenaline, the WHERE statement worked.

Thanks again for your help.
Go to the top of the page
 
+
datAdrenaline
post Sep 14 2011, 11:38 PM
Post #9

UtterAccess Editor
Posts: 16,705
From: Northern Virginia, USA



yw.gif


--------------------
Brent Spaulding | datAdrenaline | Access MVP
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 
+
dmhzx
post Sep 15 2011, 04:37 AM
Post #10

UtterAccess Ruler
Posts: 3,277
From: England



Purely for information, where datAdrealine used Now(),

In 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: 28th July 2014 - 03:17 AM