DateSerial is all I use. It's simple and allows you to do so much with very little coding.
see this site for the whole gambit. http://www.pacificdb.com.au/Support/code_date_monthfunctions.htm Brian Bennett Affinity Health Systems Systems Analyst PBS\Clinic Billing (920)628-9055 [email protected] ________________________________ From: [email protected] [mailto:[email protected]] Sent: Wednesday, February 24, 2010 7:57 AM To: [email protected] Subject: [talkbws] RE: Script Code Greetings, I can't take credit for this, I've never used DateSerial - but it does make for some streamlined code here! The 0 in large font below indicates at you want the last day of last month. Change that to -1 and it would return the last day of 2 months ago, etc. change to 1 and it would be the last day of the current month dtDate = Date Debug.Print Format(DateAdd("m", 0, DateSerial(Year(dtDate), Month(dtDate), 1)) - 1, "mmddyyyy") Getting the 1st is considerably easier: Debug.print format(dateadd("m",-1,date),"mm01yy") And I can take credit for that one :) Regards, Thom Thom C. Blackwell Product Manager Boston Software Systems (866) 653-5105 ex 807 www.bossoft.com<http://www.bossoft.com/> Visit us at HIMSS in booth 7561 Sign up for my weekly webinar!<http://www.bostonworkstation.com/customer_center/special_events.aspx> LEGAL NOTICE Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this E-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents of this E-mail or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately, then delete this message and empty from your trash. From: [email protected] [mailto:[email protected]] Sent: Wednesday, February 24, 2010 8:41 AM To: Talk Subject: [talkbws] RE: Script Code Thank you Tracey, I'll try it. Joanne Barrett-Haramis Sr. Programmer /Analyst Data Management and User Provisioning Halifax Health - IT 386-254-4116 X 1115 E-Mail: [email protected]<mailto:[email protected]> Learn IT, Use IT, Love IT "A mistake is an event, the full benefit of which has not yet been turned to your advantage; the key is to recognize the opportunity to learn and to change." Edwin Land, Founder of Polaroid P please consider the environment before printing this email Electronic communications originating from or sent to Halifax Health are subject to monitoring and public inspection under § 119.07, Florida Statutes. This message and any attachments are the property of Halifax Health and are intended to be received only by the individuals or entities identified in the message. If you have received this message in error, please take notice: 1) that any use, copying, printing, forwarding or distribution of this message in any form is strictly prohibited, and 2) please notify the Halifax Health Compliance Department at (386) 254-4278 and/or forward the message to [email protected], and please delete or destroy all copies of the message and any attachments. ________________________________ From: [email protected] [mailto:[email protected]] Sent: Wednesday, February 24, 2010 8:40 AM To: [email protected] Subject: [talkbws] RE: Script Code Hope this helps Function FirstOfLastMonth() MyMonth = Format(Now, "MM") MyMonth2 = Format(DateAdd("m", -1, Now), "MM") Myday = "01" If MyMonth2 = 12 Then MyYear = Format(DateAdd("yyyy", -1, Now), "YY") Else MyYear = Format(Now, "YY") End If 'If MyMonth < 10 Then 'MyMonth = "0" & Trim(Str(MyMonth)) 'Else 'MyMonth = Trim(Str(MyMonth)) 'End If FirstOfLastMonth = MyMonth2 & " " & MyYear Function EndOfLastMonth2() MyMonth = Format(Now, "MM") MyMonth2 = Format(DateAdd("m", -1, Now), "MM") Myday = "01" If MyMonth2 = 12 Then MyYear = Format(DateAdd("yyyy", -1, Now), "YY") Else MyYear = Format(Now, "YY") End If datetemp = Format(Myday & "/" & MyMonth & "/" & MyYear, "DD/MM/YY") datetemp2 = DateAdd("d", -1, datetemp) EndOfLastMonth2 = Format(datetemp2, "DD") & Format(datetemp2, "MM") & Format(datetemp, "YY") Tracey Locke Applications Security Analyst FacilicorpNB Information Services/Service informatique Zone 1 The Moncton Hospital Tel/tel. :(506)857-5641 Email/Courielle: [email protected]<mailto:[email protected]> Please consider the environment before printing this email. /Pensez a l'environmenment, n'imprimer que si necessaire. ________________________________ From: [email protected] [mailto:[email protected]] Sent: February 24, 2010 9:36 AM To: [email protected] Cc: Barrett, Joanne Subject: [talkbws] Script Code Hi Everyone, I'm trying to write a script; all I need help with is that I want to run the report for the 1st to the last day of the previous month, how do I write that code, thanks. Joanne Barrett-Haramis Sr. Programmer /Analyst Data Management and User Provisioning Halifax Health - IT 386-254-4116 X 1115 E-Mail: [email protected]<mailto:[email protected]> Learn IT, Use IT, Love IT "A mistake is an event, the full benefit of which has not yet been turned to your advantage; the key is to recognize the opportunity to learn and to change." Edwin Land, Founder of Polaroid P please consider the environment before printing this email Electronic communications originating from or sent to Halifax Health are subject to monitoring and public inspection under § 119.07, Florida Statutes. This message and any attachments are the property of Halifax Health and are intended to be received only by the individuals or entities identified in the message. If you have received this message in error, please take notice: 1) that any use, copying, printing, forwarding or distribution of this message in any form is strictly prohibited, and 2) please notify the Halifax Health Compliance Department at (386) 254-4278 and/or forward the message to [email protected], and please delete or destroy all copies of the message and any attachments. --- To post a message to this list, send mail to: [email protected] You are currently subscribed as: [email protected] Unsubscribe in the customer center on our website: http://www.bostonworkstation.com/customer_center/virtual_user_group_talk.aspx ------- Avis de confidentialité FacilicorpNB Disclaimer ------- This e-mail communication (including any or all attachments) is intended only for the use of the person or entity to which it is addressed and may contain confidential and/or privileged material. If you are not the intended recipient of this e-mail, any use, review, retransmission, distribution, dissemination, copying, printing, or other use of, or taking of any action in reliance upon this e-mail, is strictly prohibited. If you have received this e-mail in error, please contact the sender and delete the original and any copy of this e-mail and any printout thereof, immediately. Your co-operation is appreciated. Le présent courriel (y compris toute pièce jointe) s'adresse uniquement à son destinataire, qu'il soit une personne ou un organisme, et pourrait comporter des renseignements privilégiés ou confidentiels. Si vous n'êtes pas le destinataire du courriel, il est interdit d'utiliser, de revoir, de retransmettre, de distribuer, de disséminer, de copier ou d'imprimer ce courriel, d'agir en vous y fiant ou de vous en servir de toute autre façon. Si vous avez reçu le présent courriel par erreur, prière de communiquer avec l'expéditeur et d'éliminer l'original du courriel, ainsi que toute copie électronique ou imprimée de celui-ci, immédiatement. Nous sommes reconnaissants de votre collaboration. --- To post a message to this list, send mail to: [email protected] You are currently subscribed as: [email protected] Unsubscribe in the customer center on our website: http://www.bostonworkstation.com/customer_center/virtual_user_group_talk.aspx --- To post a message to this list, send mail to: [email protected] You are currently subscribed as: [email protected] Unsubscribe in the customer center on our website: http://www.bostonworkstation.com/customer_center/virtual_user_group_talk.aspx --- To post a message to this list, send mail to: [email protected] You are currently subscribed as: [email protected] Unsubscribe in the customer center on our website: http://www.bostonworkstation.com/customer_center/virtual_user_group_talk.aspx ________________________________ CONFIDENTIALITY NOTICE: This e-mail communication and any attachments may contain confidential and privileged information for the use of the designated recipient(s) named above. If you are not the intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify the sender at the electronic mail address noted above and destroy all copies of this communication and any attachments. Thank you for your cooperation. --- To post a message to this list, send mail to: [email protected] You are currently subscribed as: [email protected] Unsubscribe in the customer center on our website: http://www.bostonworkstation.com/customer_center/virtual_user_group_talk.aspx
