If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
monthly expiry
Hi,
First of everything i would like t thank you all for your continuos support. i made me know better about access and how to start using it . I have an sql that give me todays end of probation reminder, what i need is to get it on monthly basis i tried the month() but it didnt work SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF PROBATION" AS DocumentType FROM [ASSESSEMENT query] WHERE [End of Probation Period] =Date() Thank you for your support |
#2
|
|||
|
|||
monthly expiry
On Wed, 11 Nov 2009 08:20:10 -0800 (PST), Tia
wrote: Hi, First of everything i would like t thank you all for your continuos support. i made me know better about access and how to start using it . I have an sql that give me todays end of probation reminder, what i need is to get it on monthly basis i tried the month() but it didnt work SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF PROBATION" AS DocumentType FROM [ASSESSEMENT query] WHERE [End of Probation Period] =Date() Thank you for your support Please explain by "get it on a monthly basis". If you were to run the query today, which dates in the [End Of Probation Period] field would you expect to see? Those for this month, those for last month, those for the coming month? -- John W. Vinson [MVP] |
#3
|
|||
|
|||
monthly expiry
Tia -
If you want all records where the End of Probation Period is in the current month, try this: SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF PROBATION" AS DocumentType FROM [ASSESSEMENT query] WHERE Month([End of Probation Period]) =Month(Date()) AND Year([End of Probation Period]) =Year(Date()) -- Daryl S "Tia" wrote: Hi, First of everything i would like t thank you all for your continuos support. i made me know better about access and how to start using it . I have an sql that give me todays end of probation reminder, what i need is to get it on monthly basis i tried the month() but it didnt work SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF PROBATION" AS DocumentType FROM [ASSESSEMENT query] WHERE [End of Probation Period] =Date() Thank you for your support . |
#4
|
|||
|
|||
monthly expiry
Tia,
The Month() function needs an argument, being a field or expression representing a date, like Month(Date()) or Month([SomeDateField) Not knowing your app, I am guessing you might want: SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF PROBATION" AS DocumentType FROM [ASSESSEMENT query] WHERE [End of Probation Period] =Month(Date()) Which would select the [End of Probation Period] for the month of the current day. God Bless, Mark A. Sam "Tia" wrote in message ... Hi, First of everything i would like t thank you all for your continuos support. i made me know better about access and how to start using it . I have an sql that give me todays end of probation reminder, what i need is to get it on monthly basis i tried the month() but it didnt work SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF PROBATION" AS DocumentType FROM [ASSESSEMENT query] WHERE [End of Probation Period] =Date() Thank you for your support |
#5
|
|||
|
|||
monthly expiry
On Wed, 11 Nov 2009 13:02:06 -0500, "Mark A. Sam"
wrote: Tia, The Month() function needs an argument, being a field or expression representing a date, like Month(Date()) or Month([SomeDateField) Not knowing your app, I am guessing you might want: SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF PROBATION" AS DocumentType FROM [ASSESSEMENT query] WHERE [End of Probation Period] =Month(Date()) Which would select the [End of Probation Period] for the month of the current day. But it does assume that [End of probation period] is an Integer field with a value between 1 (January) and 12 (December). Tia will need to explain her data structure to be sure, but if [End of probation period] is a Date/Time field she won't get the desired result! A criterion [End of Probation Period] = DateSerial(Year(Date()), Month(Date()) + 1, 1) AND [End of Probation Period] DateSerial(Year(Date()), Month(Date()) + 2, 1) will get all record during the upcoming month (December 2009 if you run the query today, January 2010 if it's run during this next December). -- John W. Vinson [MVP] |
#6
|
|||
|
|||
monthly expiry
John,
I agree. I didn't want to read into it more than was given. By its application, it seems as though it is. God Bless, Mark "John W. Vinson" wrote in message ... On Wed, 11 Nov 2009 13:02:06 -0500, "Mark A. Sam" wrote: Tia, The Month() function needs an argument, being a field or expression representing a date, like Month(Date()) or Month([SomeDateField) Not knowing your app, I am guessing you might want: SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF PROBATION" AS DocumentType FROM [ASSESSEMENT query] WHERE [End of Probation Period] =Month(Date()) Which would select the [End of Probation Period] for the month of the current day. But it does assume that [End of probation period] is an Integer field with a value between 1 (January) and 12 (December). Tia will need to explain her data structure to be sure, but if [End of probation period] is a Date/Time field she won't get the desired result! A criterion [End of Probation Period] = DateSerial(Year(Date()), Month(Date()) + 1, 1) AND [End of Probation Period] DateSerial(Year(Date()), Month(Date()) + 2, 1) will get all record during the upcoming month (December 2009 if you run the query today, January 2010 if it's run during this next December). -- John W. Vinson [MVP] |
#7
|
|||
|
|||
monthly expiry
On Nov 11, 1:35*pm, John W. Vinson
wrote: On Wed, 11 Nov 2009 13:02:06 -0500, "Mark A. Sam" wrote: Tia, The Month() function needs an argument, being a field or expression representing a date, like Month(Date()) *or Month([SomeDateField) Not knowing your app, I am guessing you might want: SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF PROBATION" AS DocumentType FROM [ASSESSEMENT query] WHERE [End of Probation Period] =Month(Date()) Which would select the [End of Probation Period] for the month of the current day. But it does assume that [End of probation period] is an Integer field with a value between 1 (January) and 12 (December). Tia will need to explain her data structure to be sure, but if [End of probation period] is a Date/Time field she won't get the desired result! A criterion [End of Probation Period] = DateSerial(Year(Date()), Month(Date()) + 1, 1) AND [End of Probation Period] DateSerial(Year(Date()), Month(Date()) + 2, 1) will get all record during the upcoming month (December 2009 if you run the query today, January 2010 if it's run during this next December). -- * * * * * * *John W. Vinson [MVP] hello, when i am using this one it will not be in sql but as a query and no data is shown it is emty but once i put it back the way it was remider per day not month or =day i am getting data SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF PROBATION" AS DocumentType FROM [ASSESSEMENT query] WHERE Month([End of Probation Period]) =Month(Date()) AND Year([End of Probation Period]) =Year(Date()) and i tried without the year as well, same story, no data is displayed i am getting data only when i am using this SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF PROBATION" AS DocumentType FROM [ASSESSEMENT query] WHERE [End of Probation Period] =Date() Please help |
#8
|
|||
|
|||
monthly expiry
On Wed, 11 Nov 2009 14:19:26 -0800 (PST), Tia
wrote: [End of Probation Period] = DateSerial(Year(Date()), Month(Date()) + 1, 1) AND [End of Probation Period] DateSerial(Year(Date()), Month(Date()) + 2, 1) will get all record during the upcoming month (December 2009 if you run the query today, January 2010 if it's run during this next December). -- * * * * * * *John W. Vinson [MVP] hello, when i am using this one it will not be in sql but as a query and no data is shown it is emty but once i put it back the way it was remider per day not month or =day i am getting datas Queries ARE SQL. SQL is the language in which queries are written and stored in your database. The query grid is not a query; it's a tool designed to build a SQL string. I have no idea what the sentence above means. "put it back the way it was" - you may know how it was, I don't. SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF PROBATION" AS DocumentType FROM [ASSESSEMENT query] WHERE Month([End of Probation Period]) =Month(Date()) AND Year([End of Probation Period]) =Year(Date()) Of [End of probation period] is a non-NULL date/time field this should show all records with a date during November 2009. and i tried without the year as well, same story, no data is displayed i am getting data only when i am using this SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF PROBATION" AS DocumentType FROM [ASSESSEMENT query] WHERE [End of Probation Period] =Date() This should retrieve only those records with #11/11/2009# in the [End of probation period] field. Might you perhaps have a field named Date (or Month or Year) in your table or query, or a control with that name on your Form? If so, Access may be getting confused about whether you mean your field or the builtin function. This is the main reason to NEVER use these reserved words to name fields or controls! -- John W. Vinson [MVP] |
#9
|
|||
|
|||
monthly expiry
On Nov 11, 3:11*pm, John W. Vinson
wrote: On Wed, 11 Nov 2009 14:19:26 -0800 (PST), Tia wrote: [End of Probation Period] = DateSerial(Year(Date()), Month(Date()) + 1, 1) AND [End of Probation Period] DateSerial(Year(Date()), Month(Date()) + 2, 1) will get all record during the upcoming month (December 2009 if you run the query today, January 2010 if it's run during this next December). -- * * * * * * *John W. Vinson [MVP] hello, when i am using this one it will not be in sql but as a query and no data is shown it is emty but once i put it back the way it was remider per day not month or =day i am getting datas Queries ARE SQL. SQL is the language in which queries are written and stored in your database. The query grid is not a query; it's a tool designed to build a SQL string.. I have no idea what the sentence above means. "put it back the way it was" - you may know how it was, I don't. SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF PROBATION" AS DocumentType FROM [ASSESSEMENT query] WHERE Month([End of Probation Period]) =Month(Date()) AND Year([End of Probation Period]) =Year(Date()) Of [End of probation period] is a non-NULL date/time field this should show all records with a date during November 2009. and i tried without the year as well, same story, no data is displayed i am getting data only when i am using this SELECT Employee, [End of Probation Period] AS ExpiryDate, "END OF PROBATION" AS DocumentType FROM [ASSESSEMENT query] WHERE [End of Probation Period] =Date() This should retrieve only those records with #11/11/2009# in the [End of probation period] field. Might you perhaps have a field named Date (or Month or Year) in your table or query, or a control with that name on your Form? If so, Access may be getting confused about whether you mean your field or the builtin function. This is the main reason to NEVER use these reserved words to name fields or controls! -- * * * * * * *John W. Vinson [MVP can u provide me with your email adress so i can attach the file plz its not working and i dont know what i am doing wrong Please help Tia |
#10
|
|||
|
|||
monthly expiry
On Wed, 11 Nov 2009 15:33:11 -0800 (PST), Tia
wrote: can u provide me with your email adress so i can attach the file plz its not working and i dont know what i am doing wrong Please help Ordinarily no - I'm a self employed consultant, donating (entirely too much) time to this free forum; private email support is normally reserved to paying clients. But just this once... right click the .mdb file, and choose Send To Compressed (Zip) Folder, and email it to jvinson at wysard of info dot com. Remove the blanks and make the obvious edits. There is some problem that we're just not communicating! -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|